Re: Transaction callback

On 31. jan 2004, at 18:53, Tom Lane wrote:
> "Thomas Hallgren" <thhal(at)mailblocks(dot)com> writes:
>> Ideally, I'd like a "beforeCompletion" that is executed prior to the
>> start
>> of the commit process and a "afterCompletion" that is called when the
>> transaction is commited. The latter would have a status flag
>> indicating if
>> status is "prepared" (to support 2-phase commits), "commited", or
>> "rolled
>> back".
>
> And what exactly would this callback do?

I imagine this would be to enforce that constraints are kept. FOREIGN
KEYs can be deferred, and simple CHECK constrains can be simulated with
clever foreign keys to dummy tables. Possibly allowing CHECK
constraints to be deferred alleviate the need for this?

> The transaction commit sequence is sufficiently delicate that I'm not
> interested in any proposals to call random user-written code in it.
> The notion of a post-commit callback is even more problematic --- what
> is it going to do at all? It cannot modify the database, and it cannot
> do anything that risks getting an error, which seems to leave mighty
> little scope for useful activity.

Surely this wouldn't effect the commit sequence. Post-commit actions
could be just like cronjobs, but which are run as soon as there is a
known need for them (and not otherwise).

Ideally triggered triggers could install pre-commit actions during the
transaction. The trigger knows:
* after this particular insert/update some database logic that cannot
be codified into a foreign key constraint is in an inconsistent state
and must not be committed unless we are sure that some other action
happened later

I've had several cases of needing sth like this, but always could hack
a solution using several triggers and dummy tables that I could put
into an illegal state (with a deferred foreign key). Later another
trigger took that table out of the illegal state if the right action
was performed.

David Helgason
Over the Edge Entertainments