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 made by 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 transaction boundaries) - 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