Re: [HACKERS] COMMIT TRIGGERs, take n, implemented with CONSTRAINTTRIGGERS

On Fri, Sep 15, 2017 at 04:07:33PM -0400, Tom Lane wrote:
> Nico Williams <nico@cryptonector.com> writes:
> > On Fri, Sep 15, 2017 at 02:19:29PM -0500, Nico Williams wrote:
> >> On Fri, Sep 15, 2017 at 11:26:08AM -0700, Andres Freund wrote:
> >>> I think you should also explain why that's a desirable set of
> >>> semantics.
> 
> > Now, why is this desirable: atomicity.
> 
> >    The client/user can no longer add statements to the transaction,
> >    therefore now (commit-time) is a good time to run a procedure that
> >    can *atomically* examine the totatility of the changes made by the
> >    user.
> 
> I do not really understand this claim.  The argument for a commit trigger
> seems to be exactly "I want to be the last thing that happens in the
> transaction".  But as soon as you have two of them, one of them is not

The user defining the triggers is a very different thing from the user
sending the various statements up to and including the COMMIT.  They
need not be the same.

The DBA/developers/ops own the triggers.  The client user may not have
any trigger creation privilege.

Being in control of the [pre-]commit triggers, I can control the order
in which they run (by-name, lexical order).

The important thing is that all of the [pre-]commit triggers will run
after the last statement in the TX send by the client.

Surely there's nothing strange abolut this -- it's already the case for
[DEFERRED] CONSTRAINT TRIGGERs!

> going to be the last thing.  Maybe you could address that by requiring
> the triggers to be read-only, but then it doesn't seem like a very useful

No, you think something is a problem that isn't.  And I need these
triggers to be able to write.

> feature.  If there can be only one, maybe it's a usable feature or maybe
> not, but I'm inclined to think that CREATE TRIGGER is a pretty poor API

Multiple [pre-]commit triggers make as much (or little) sense as
multiple triggers on any table.

> for such a definition.  Triggers are generally expected to be objects
> you can create any number of.

That's what I expect of commitr triggers too: that I could have as many
as I want.

> Another question is exactly how you're going to "examine the totality of
> the transaction's changes"; a trigger, per se, isn't going to know *any*
> of what the transaction did let alone all of it.  We have some features
> like transition tables, but they're not accessible after end of statement;

Currently I use an audit facility I wrote (based on any number of audit
facilities I've seen) which automatically creates for-each-row triggers
for all tables and which then record (txid,old_row,new_row) for all
updates.  So a pre-copmmit trigger can simply examine all such audit
rows for txid_current() each table it cares about.

> and they're pretty useless in the face of DDL too.  It's going to be hard

I use event triggers to discover schema changes.

(I'm not concerned about superusers -- they can disable my
implementation.  I _am_ concerned about unprivileged users.)

> to produce a very credible use-case without a lot of work to expose that
> information somehow.

One dead-trivial use-case is to
 INSERT INTO tx_log.tx_log("txid","who","when","how_long") SELECT txid_current(), current_user, current_timestamp,
 clock_timestamp() - current_timestamp;
 

And yes, I could just as well use a DEFERRED CONSTRAINT TRIGGER on every
table than does this INSERT with ON CONFLICT ("txid") DO NOTHING.
Except that an unprivileged user could SET CONSTRAINTS ALL IMMEDIATE
(ugh), and that this would be slower (once per-row) than doing it just
once at commit time.

> (Some closely related work is being done for logical decoding, btw.
> I wonder which use-cases for this might be satisfied by having a logical
> decoding plug-in watching the WAL output.)

My use case involves generating a stream of incremental updates to
hand-materialized views (because the materialized views in PG do not
expose deltas).  That *almost* fits the logical WAL decoder concept, but
fails on account of needing to be incremental updates not of _raw_ data,
but of views on that data.

> > Commit triggers also allow one to record transaction boundaries, and
> > NOTIFY listeners less frequently than if one did a NOTIFY in normal
> > for-each-row/statement triggers.
> 
> Um, NOTIFY already collapses duplicate notifications per-transaction.

Oh, that I didn't know.  (But it changes nothing for me.)

Nico
-- 


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers