I need to run a script on every database change. The script works on diffs, so I need to know if I handled the previous state (the handler script could be down for some time for example).

One way I could implement it is to create a trigger on every change which would increment a sequence. Then I just check if current_sequence_value == previous_sequence_value + 1.

I'm not that happy with making every single write require the sequence update, so the question is: can I get such "state version seq" using some kind of built-in metadata?

  • You want to run a script for every DML statement? I hope you have only few data modifications. Maintaining a sequence for that will be your least performance problem. – Laurenz Albe Aug 8 '19 at 15:13
  • @LaurenzAlbe Compared to the number of reads, I have very little writes. Also, the script first checks if a certain condition is met (based on the change in the db), which will be met for a tiny fraction of changes. Also the script execution is asynchronous. So I think I should be fine. – Dart Dega Aug 8 '19 at 18:14
  • 1
    Then use a sequence. They have little overhead. – Laurenz Albe Aug 9 '19 at 5:33
  • be aware that you can't rollback updates to sequences. – Jasen Aug 9 '19 at 21:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.