How to address moddatetime apparently bypassing precision of column?

Question

We have a database where every timestamp is defined as timestamp(3) with time zone. We noticed that in some cases we were getting back timestamps with 6 fractional digits, not 3. I tracked this down to only happening in out update_date columns, and only if it was updated via moddatetime.

Does anybody have experience with gracefully fixing this?

I tested manual inserts, updates, from literal, from now(), etc and none of those cause the issue.

I'm aware of being able to fix this by casting to ::timestamptz(3) wherever we use this field, but this will add a lot of mess, plus I am concerned about the performance if we need to do ... WHERE date_trunc('milliseconds', update_date) > other_tz3_value.

The best I've come up with would be changing the index we have on update_date to a functional index on date_trunc('milliseconds', update_date), but again, this seems clunky.

The tables in question have a trigger like:

CREATE TRIGGER mytrigger
    BEFORE UPDATE ON mytable
    FOR EACH ROW EXECUTE PROCEDURE moddatetime(update_date);

My expectation would be that no matter how a value is inserted into a timestamp(3) field, it always comes back with no more than 3 fractional digits.

moddatetime is a function provided by the extension with the same name. From https://www.postgresql.org/docs/10/contrib-spi.html : F.37.5. moddatetime — Functions for Tracking Last Modification Time moddatetime() is a trigger that stores the current time into a timestamp field. This can be useful for tracking the last modification time of a particular row within a table. To use, create a BEFORE UPDATE trigger using this function. Specify a single trigger argument: the name of the column to be modified. The column must be of type timestamp or timestamp with time zone. — Alex, Mar 25 '19 at 13:09

score 0 · Answer 1 · answered Jul 11 '20 at 11:45

I don't think it's possible with the moddatetime function.

This answer shows an example of using a custom trigger. We can modify it to define the precision of the generated timestamp like so:

CREATE OR REPLACE FUNCTION update_modified_column()   
RETURNS TRIGGER AS $$
BEGIN
    NEW.modified = current_timestamp(3);
    RETURN NEW;   
END;
$$ language 'plpgsql';

How to address moddatetime apparently bypassing precision of column?

1 Answers1