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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
