Postgresql - restore to savepoint across transaction boundaries

Question

Is there a way to roll back to a "committed savepoint"?

Afaik, the actual savepoints supported by postgresql are subtransactions and lose their meaning when the enclosing transaction commits or aborts. Are there "savepoints" across transaction boundaries?

Basically, what I want is execute these three transaction in order:

Transaction ~ A

BEGIN TRANSACTION;
    COMMIT SAVEPOINT 'before_a';
    DO SOMETHING;
COMMIT TRANSACTION;

Transaction ~ B

BEGIN TRANSACTION;
    DO SOMETHING_ELSE;
COMMIT TRANSACTION;

Transaction ~ C

BEGIN TRANSACTION
    ROLLBACK TO COMMITED SAVEPOINT 'before_a'; -- discards work done in A and B
COMMIT TRANSACTION

The reason is that I am writing a (Java) regression test.

Under faulty circumstances, DO SOMETHING_ELSE will trigger a transaction commit exception when trying to commit B (some foreign key constraint violations upon a DELETE, I believe), but ONLY if the work done in transaction A has been committed.

As the issue is now resolved, Transaction B will commit. But in doing so, both A and B will have left some by-products in the database. These need now be purged from the database if the next test is supposed to have any chance at succeeding.

It's exceedingly difficult to track these by-products down manually, so transaction C should remove these.

There is no way you can do what you want. A `commit` makes all changes of the transaction so far permanent. Savepoints are **always** local to the transaction that created them. There is no way to access a savepoint created in a different transaction (let alone roll back to it) — , Apr 09 '20 at 18:07
@a_horse_with_no_name that's why I put it in quotes. I'm looking for any semi-reasonable mechanism that would provide this functionality from within postgresql. — User1291, Apr 09 '20 at 18:13
Run the entire thing in a single transaction. So "Begin Do_Something; Do_Something_Else; Rollback; End;" as long the transaction is running Do_Something_Else can see any DB changes made during Do_Something". Unfortunately, you cannot break into the transaction to validate what has been done. The other option, if your in a test environment, would be backup before and restore afterward. — Belayer, Apr 09 '20 at 21:36

score 0 · Answer 1 · answered Apr 10 '20 at 05:00

As has been mentioned in the comments, that is impossible in PostgreSQL.

Your only hope is to use subtransactions inside an enveloping transaction:

BEGIN;
SAVEPOINT before_a;
/* perform the work for A */
/* perform the work for B */

If B fails, do the following:

ROLLBACK TO SAVEPOINT before_a;

Then, no matter if B failed or not:

/* perform the work for C */
COMMIT;

Postgresql - restore to savepoint across transaction boundaries

1 Answers1