On Fri, Sep 15, 2017 at 11:26:08AM -0700, Andres Freund wrote:
> On 2017-09-14 14:41:12 -0500, Nico Williams wrote:
> > I've read through several old threads on COMMIT TRIGGERs. Rather than
> > write a lengthy post addressing past debates, here's an implementation
> > and demonstration of [an approximation of] COMMIT TRIGGERs with natural
> > and _desirable_ semantics:
>
> I think you should also explain why that's a desirable set of
> semantics. E.g. explain the use case you have and potential other
> use-cases. I think it should also be explained that these are
> *pre*-commit triggers - IIRC some people have asked for *post*-commit
> triggers in the past.
Responding out of order:
- Yes, this is a pre-commit thing.
It's the *same* as DEFERRED CONSTRAINT TRIGGERs. After all, that's how I'm implementing this now :)
Critically, the client/user can no longer execute additional statements at this point, since they've executed
COMMIT. Therefore these trigger procedures will see *all* of the changes made by the user (and all of the changes
madeby commit triggers that run before them, so, as usual, trigger invocation order matters).
- As to use-cases, I listed a few in my post:
- update/refresh view materializations - consistency checks - NOTIFY - record history (in particular, record
transactionboundaries) - and, no doubt, others
Of course all of this can [almost!] be done with CONSTRAINT TRIGGERs,
since that's what I'm demonstrating.
HOWEVER, there's a *very serious* problem with CONSTRAINT TRIGGERs:
unprivileged users can make them run IMMEDIATEly rather than deferred.
Also, using CONSTRAINT TRIGGERs for this is inefficient. Please see my
post and the linked file to see why.
Nico
--
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers