Re: triggers on prepare, commit, rollback... ?

Dear Tom,

>>>> trigger on "prepare", "commit", "rollback", "savepoint",
>
> I'm sure I've ranted on this several times before, but a quick archive
> search doesn't find anything. So, here are a few points to chew on:

I agree that not all of them may make sense, but some may do, and I do not
know which of them would definitely be of any use in any case for anyone.
In fact, I just know that I do not know:-)

> * Trigger on rollback: what's that supposed to do? The current
> transaction is already aborted, so the trigger has no hope of making any
> database changes that will ever be visible to anyone.

For some synchronous replication implementation, it may be to forward the
ROLLBACK to the twin transaction in another database?

> * Trigger on commit: what do you do if the transaction fails after
> calling the trigger? The reductio ad absurdum for this is to consider
> having two on-commit triggers, where obviously the second one could
> fail.

Obviously, an "AFTER xxx" cannot make the xxx to fail.

I would use "BEFORE COMMIT" or "BEFORE PREPARE" to check global
consistency constraints that would not make sense to be checked at the
simple row or statement level in a complex transaction.

> The basic problem is that the transaction commit sequence is very

Indeed. The "BEFORE" should be before anything else in the commit
sequence, and may make it fail, but the "AFTER" is after anything else,
and could not change the outcome.

> the "user" transaction commits or aborts. But that hardly seems like
> a usable basis for replication, since you're just plain out of luck
> if the secondary transaction fails.

As for replication, I was naively thinking of using BEFORE PREPARE to
forward a prepare and possibly fail in the master if the slave fails, and
then an AFTER PREPARE or BEFORE COMMIT/ROLLBACK PREPARE would forward the
final COMMIT/ROLLBACK depending on the success of the prepare operation in
the master and slaves. The function could also wait for something to
finish on the slave.

If the something fails in between, then there are several state
combinations to detect and handle, but this is just the point of 2PC.

I'm not claiming this is easy, on the contrary, but ISTM that the 2PC
semantics is all what is fundamentaly needed to achieve synchroneous
replication, and once hooks are available there should be a way to
prototype something at the user level (slony style). The efficiency of the
stuff is another issue, but some applications don't need high throughput
and low latency, but just to know that the data are not lost.

Well, just my usual 0.02 EUR:-)

--
Fabien.