0

We are experiencing a strange problem with triggers and a sequence generator in Postgres. We have a trigger on table that inserts audit log informations (some business Data before update, some business Data after update, ...).

The problem we have is that the order of the sequence generated doesn't match the timestamp and the data at the time of the capture.

enter image description here

We are sure that functionnaly the row with the id (7 228 916) happened after the row with the id (7 229 471), as the timestamps confirm it.

But the sequences are not in the correct order.

At the end of the of the trigger, here is the insert statement:

INSERT INTO AUDIT_TABLE (ID, ROW_ID, CREATED_AT, ...)
VALUES (nextval('AUDIT_TABLE_SEQ'), NEW.PK_, current_timestamp, ...);

Does someone please have an explanation for this problem?

Thank you for your help

We are expecting to have the sequences to be ordered with timestamps of the capture of events.

youness.bout
  • 343
  • 3
  • 9
  • Many things could interfere: Some proces could change the setting for the sequence, current_timestamp is set at the start of the transaction and is NOT the same as the clock timestamp, someone could have changed the setting for the clock, etc. etc. But most important, a sequence just generates a number and that's it. It doesn't rollback and it has no other meaning than just being a number. – Frank Heikens Feb 14 '23 at 12:12
  • Why do you care? If you want the timestamp, and you have the timestamp, then use the timestamp. The job of a sequence, despite the name, is provide unique value, not strictly ordered values. – jjanes Feb 14 '23 at 14:02
  • @jjanes We do care about the order in which the events happened to know who did what and what was the situation before. The timestamp only is not sufficient since some events can happen at the same time. And we do not want to rely on sequence tables and locking mechanisms. Thanks for your replies – youness.bout Feb 14 '23 at 16:20
  • There is absolutly no guaranty of any kind in the order of a sequenced object SEQUENCE or IDENTITY... These values are strictly asemantics son you must never rely on it for anykind of logical retrireving of informations – SQLpro Feb 14 '23 at 18:12
  • I understand your point of view and it's valid. But what I can't understand is the why at a timestamp "13:49:27s" we get a sequence smaller than the timestamp "13:49:12s". – youness.bout Feb 15 '23 at 12:07

0 Answers0