PG Bug reporting form <noreply@postgresql.org> writes:
> One field being updated is a timestamp provided as UTC text representation
> (e.g. '2020-07-29T22:30:00.124248Z') but stored as timestamp with time
> zone. The timestamp sub-second component is not consistently written -
> sometimes it is stored correctly, sometime it is stored incorrectly. Always
> the sub second part of the time (including more significant digits) and
> never the date/time from seconds upwards.
Given the described query:
> UPDATE "tnt_res_b195217c_cfc8_11ea_8c1b_00155dce25bc".job
> SET
> locked_tz = CASE WHEN locked_tz IS NULL THEN '2020-07-29T22:30:00.124248Z'
> ELSE locked_tz END,
> locked_by_operative_uid = CASE WHEN locked_by_operative_uid IS NULL THEN
> 'b32ffd2c-cfc8-11ea-987d-00155dce25bc' ELSE locked_by_operative_uid END,
> version = CASE WHEN locked_tz IS NULL THEN version + 1 ELSE version END,
> description='2020-07-29T22:30:00.124248Z' -- added for debugging
> WHERE uid = '09dbe5d6-d1eb-11ea-9185-00155dce25bc'
> RETURNING locked_tz, locked_by_operative_uid;
what seems far more likely than random data corruption is that some other
transaction updated this same row slightly earlier, setting the locked_tz
value that you are reading back. The CASE in this query would then have
preserved that value, but the description field would get updated anyway.
In the specific example you show, if I'm not confused, the reported
locked_tz value is a bit older than the description value, so that
this sequence of events seems very plausible. But even if the order
were reversed, that wouldn't immediately destroy this theory, because
you haven't said where the timestamps are coming from. The transaction
that got to the row first could possibly try to store a "newer" timestamp
than the one that got there second, unless there's some guarantee about
how those timestamps are computed that you've not described.
In short, I think you need to take a hard look at whatever logic you
think is preventing concurrent selection of the same job row by multiple
transactions, because what it looks like from here is that that's not
working reliably.
I won't completely deny that there could be a Postgres bug here, but
there are enough moving parts that are missing from this bug report
that it can't be investigated usefully.
regards, tom lane
