Re: COMMIT TRIGGERs, take n, implemented with CONSTRAINT TRIGGERS

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