There is no guarantee that transaction_timestamp() is

Guard against transaction_timestamp() moving backwards about akka-persistence-r2dbc HOT 2 CLOSED

akka commented on August 17, 2024

Guard against transaction_timestamp() moving backwards

from akka-persistence-r2dbc.

Comments (2)

patriknw commented on August 17, 2024

This seems to happen under rather normal conditions. See 16 here:

postgres=# select persistence_id, sequence_number, db_timestamp, write_timestamp from event_journal where persistence_id = 'TestEntity-1|p71' order by persistence_id, db_timestamp, sequence_number;

  persistence_id  | sequence_number |         db_timestamp          | write_timestamp
------------------+-----------------+-------------------------------+-----------------
 TestEntity-1|p71 |               1 | 2021-10-21 11:13:56.134083+00 |   1634814836096
 TestEntity-1|p71 |               2 | 2021-10-21 11:13:56.836606+00 |   1634814836553
 TestEntity-1|p71 |               3 | 2021-10-21 11:13:57.293981+00 |   1634814837199
 TestEntity-1|p71 |               4 | 2021-10-21 11:13:58.609032+00 |   1634814838602
 TestEntity-1|p71 |               5 | 2021-10-21 11:14:02.078275+00 |   1634814841899
 TestEntity-1|p71 |               6 | 2021-10-21 11:14:08.056475+00 |   1634814848040
 TestEntity-1|p71 |               7 | 2021-10-21 11:14:08.574025+00 |   1634814848557
 TestEntity-1|p71 |               8 | 2021-10-21 11:14:14.25899+00  |   1634814853569
 TestEntity-1|p71 |               9 | 2021-10-21 11:14:14.310217+00 |   1634814854286
 TestEntity-1|p71 |              10 | 2021-10-21 11:14:19.647697+00 |   1634814859571
 TestEntity-1|p71 |              11 | 2021-10-21 11:14:27.876247+00 |   1634814866869
 TestEntity-1|p71 |              12 | 2021-10-21 11:14:27.900744+00 |   1634814867860
 TestEntity-1|p71 |              16 | 2021-10-21 11:14:28.009415+00 |   1634814868008
 TestEntity-1|p71 |              13 | 2021-10-21 11:14:28.014187+00 |   1634814867931
 TestEntity-1|p71 |              14 | 2021-10-21 11:14:28.022303+00 |   1634814867985
 TestEntity-1|p71 |              15 | 2021-10-21 11:14:28.040212+00 |   1634814868004

from akka-persistence-r2dbc.

patriknw commented on August 17, 2024

One solution suggestion is to look at timestamp from previous sequence_number:

insert into event_journal_test (persistence_id, sequence_number, db_timestamp)
  values ('pid1', 8, greatest(transaction_timestamp(), 
    (select db_timestamp + '1 microsecond'::interval from event_journal_test where persistence_id = 'pid1' and sequence_number = 7)));