Re: [HACKERS] COMMIT TRIGGERs, take n, implemented with CONSTRAINTTRIGGERS - Mailing list pgsql-hackers

From Nico Williams
Subject Re: [HACKERS] COMMIT TRIGGERs, take n, implemented with CONSTRAINTTRIGGERS
Date
Msg-id 20170915191927.GA26093@localhost
Whole thread Raw
In response to Re: [HACKERS] COMMIT TRIGGERs, take n, implemented with CONSTRAINTTRIGGERS  (Andres Freund <andres@anarazel.de>)
Responses Re: [HACKERS] COMMIT TRIGGERs, take n, implemented with CONSTRAINTTRIGGERS
Re: [HACKERS] COMMIT TRIGGERs, take n, implemented with CONSTRAINTTRIGGERS
List pgsql-hackers
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

pgsql-hackers by date:

Previous
From: Robert Haas
Date:
Subject: Re: [HACKERS] path toward faster partition pruning
Next
From: Andres Freund
Date:
Subject: Re: [HACKERS] COMMIT TRIGGERs, take n, implemented with CONSTRAINTTRIGGERS