How to catch PostgreSQL ROLLBACK for a function with UOW side-effects?

1
votes

I am writing a scalar plpgsql function that contains a C function that has a side-effect outside of the database. When the function is invoked, in some arbitrary SQL (trigger, select, write, etc), I want the side-effect to be committed or rolled back on the PostgreSQL unit of work (UOW) boundary. I can handle the UOW commit, but I don't know how to "catch" the database ROLLBACK and rollback the side-effect. The key point is I am writing the function, but don't have control of how it is called, i.e., I can not "force" the call to be in a block with EXCEPTION handlers. Any ideas?

For the commit, I plan to have the plpsql function INSERT into a database TABLE that has a trigger "... AFTER INSERT ... EXECUTE PROCEDURE commit_my_side_effect()", so when the UOW is committed, the row is committed, the AFTER INSERT trigger fires and presto, the side effect is committed;

The only idea I have so far is to pass out the txid_current() to a background worker process. Then on some heartbeat using SPI, check if the txid is not in flight or committed, then it must have been rolled back. But that seems like heavy lifting.

2
postgresqltransactionsrollback

2 Answers

0
votes

Broadly speaking, a transaction is considered "rolled back" if it's not committed and it's no longer running; in the interests of ACID compliance, an explicit ROLLBACK needs to be functionally identical to yanking the power cord on your server, so in general, there can't be any deliberate action associated with a rollback which you might be able to hook into.

The actual removal of rolled-back data is handled by vacuuming, which works more or less like your proposed background worker: anything written by a transaction which is not running and not committed is a candidate for removal. However, there's a bit more to it than that, as a transaction containing subtransactions (SAVEPOINTs or PL/pgSQL EXCEPTION blocks) can be partially rolled back. In other words, txid_current() alone isn't enough to decide if a change was committed, and I don't know if Postgres exposes enough information about subtransaction states to let you to cater for this.

I think the only sane approach is to move the application of side-effects to an external process, and trigger it after commit, once you know what has actually been committed. Two approaches come to mind:

  • Have your PL/pgSQL function insert into a work queue which is polled by the external process, or
  • Feed changes to the process via NOTIFY (notifications are only delivered on commit, and notifications from rolled-back subtransactions are discarded)

Notifications are more lightweight and lower latency (they're delivered asynchronously, so no polling is necessary), but less robust than a table-based approach, as the notification queue is wiped out in the event of a crash or an unexpected disconnection. Of course, if you want crash safety without the downsides of polling, you can simply do both.

0
votes

I found a feature called ON_ERROR_ROLLBACK, and looking at the implementation,https://github.com/postgres/postgres/blob/master/src/bin/psql/common.c, I think I can "wrap" all the SQL commands using the following pseudo-code to add "fake" savepoint, and "fake" rollback to savepoint and fire off a "rollback_side_effect()":

side_effect_fired = false; // set true if the side_effect udf called
run("SAVEPOINT _savepoint");
run($sqlcommand);
if (txn_status == ERROR && side_effect_fired) {
   run("ROLLBACK TO _savepoint"
   rollback_side_effect()); // rollback the side effect
}

I probably need a stack of _savepoint. I will run with that!