0

Dealing with a DAL-type system, I've an issue with savepoint safety: a savepoint is created at the start of a process and either released on successful completion or rolled back on error.

However while it's not normal it's possible that the entire transaction gets committed (or rolled back) by the process being run. This removes the savepoint we're trying to release / rollback. This issue can be worked around by putting an other savepoint around the release/rollback, but obviously it's not possible to SAVEPOINT a transaction in error.

Is there a way to perform a best-effort handling of this such that the savepoint is released / rollbacked if it exists, and nothing happens otherwise? According to In Postgres, how to get a list of current defined savepoint? there was no way to LBYL at the time, and there seems to be none still.

Masklinn
  • 34,759
  • 3
  • 38
  • 57
  • I don't see the point: if the transaction is ended, setting a savepoint is meaningless. You could just release the savepoint at the end of the function and catch the error. – Laurenz Albe Jan 18 '21 at 17:47
  • @LaurenzAlbe the issue is that the transaction may or may not be ended, the layer I'm working at has no idea about that because it depends how ill-working the process it triggered is. The tx *should not* be ended, but the system should be able to handle that state. & releasing a savepoint which doesn't exist means putting the current transaction in error if it's not already there. – Masklinn Jan 19 '21 at 06:57
  • Then why not create the savepoint right after starting a new transaction? – Laurenz Albe Jan 19 '21 at 07:03
  • Because the goal is to release or rollback the existing savepoint if it still exists. – Masklinn Jan 19 '21 at 07:05
  • If you create it right after `BEGIN`, it will always exist, so you can unconditionally release it. – Laurenz Albe Jan 19 '21 at 07:26
  • It will not exist if the thing being called either rollbacks or commits the transaction, which is the entire issue. – Masklinn Jan 19 '21 at 07:31
  • Like I said: Just issue `RELEASE SAVEPOINT`, and if it errors out, ignore the error. If you ar outside a transaction, it won't matter. – Laurenz Albe Jan 19 '21 at 07:40
  • Like I said: that puts the transaction in error if the savepoint is gone. – Masklinn Jan 19 '21 at 07:47
  • You just said that the transaction has been committed or rolled back. So which transaction would be broken? – Laurenz Albe Jan 19 '21 at 08:00
  • No, I said that the transaction *may* be committed or rolled back. All the unknowns are the point of the question, if everything was always in a known state there would be no reason to ask a question in the first place. – Masklinn Jan 19 '21 at 08:11
  • Sorry, that is completely unclear. Either there is a transaction, or there is no transaction. If there is a transaction, there is a savepoint. If there is no transaction, it doesn't matter. So unconditionally releasing the savepoint should always do the right thing. I'll vote to close the question. Perhaps you can edit it to clarify it. – Laurenz Albe Jan 19 '21 at 08:29

1 Answers1

-1

You can try to use pg_xlogdump, bulk copy the result into a table and then read the table to find those commands...

SQLpro
  • 3,994
  • 1
  • 6
  • 14