Re: pre-commit triggers - Mailing list pgsql-hackers

On 11/19/2013 04:23 PM, Andres Freund wrote:
> 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".

That part or really orthogonal to the issue at hand I think. i.e. I 
could, by removing about 30 characters, remove that restriction and 
leave the rest intact.

>
>> 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?


What if you need to have such a trigger on multiple tables? How many 
times does it fire?

This feature would be nicely and easily defined - it will run each such 
trigger once per transaction (modulo the xid issue).

Now maybe we could use an event trigger WHEN clause instead of always 
applying the "xid must be real" rule. I'm not sure what that would look 
like - i.e. what would be the filter variable or its possible values, 
but it's possibly worth exploring.

cheers

andrew