Re: pre-commit triggers

On 2013-11-19 16:04:12 -0500, Andrew Dunstan wrote:
>
> On 11/19/2013 03:54 PM, Andres Freund wrote:
> >On 2013-11-19 12:45:27 -0800, Josh Berkus wrote:
> >>On 11/19/2013 08:42 AM, Andres Freund wrote:
> >>>Could you explain a bit what the use case of this is and why it's not
> >>>sufficient to allow constraint triggers to work on a statement level?
> >>>"Just" that there would be multiple ones fired?
> >>The main reason is to enforce arbitrary assertions which need
> >>enforcement at the end of a transaction and not before. For example:
> >>[...]
> >>You can't enforce this at the statement level because the
> >>update/insert/deletes can happen in any order on the various tables.
> >That's why I suggested adding statement level constraint triggers
> >(should be a farily small patch), which can be deferred till commit. The
> >problem there is that they can be triggered several times, but that can
> >relatively easily accounted for in user code.
> >
> >I can't really say why, but commit time even triggers make me nervous...

Don't get me wrong, I am not -1'ing the feature, just wondering whether
there might be better alternatives.

> This feature is really extremely close to being a deferred constraint
> trigger that is called once. The code that calls these event triggers runs
> right before the code that runs the deferred triggers. That spot in the code
> was chosen with some care, to try to reduce any risk from the feature.

Well, a) that code is battle tested b) it properly handles new events
being created during the invocation of a trigger c) it allows only
triggering when specific tables have been modified. That'd allow major
efficiency improvements in the usecase cited upthread.

I think the major advantage is that it doesn't depend on the relatively
obscure definition of "an xid has been assigned".

> Putting the onus on the user to detect multiple invocations of the trigger
> would make for MORE fragility, not less.

Yea, that's the major reason against it. Without that I'd say that's the
clear route. But maybe adding a AFTER STATEMENT ONCE (or better using an existing
keyword) is the way to go for that?

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services