Thread: [HACKERS] COMMIT TRIGGERs, take n, implemented with CONSTRAINT TRIGGERS

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:
- commit triggers run exactly once in any write transaction
- commit triggers run at the _end_ of any write transaction
- multiple commit triggers may be declared, and they run in name  lexical order
- commit triggers do NOT run in read-only transactions
- commit trigger procedures can do anything any any other trigger  procedure can do: DDL, DML, NOTIFY, ...

There is just one undesirable bit of semantics in this implementation:
unprivileged users can break its semantics by executing SET CONSTRAINTS
... IMMEDIATE.  Obviously this is bad, at least for some possible uses
of commit triggers.

Also, this implementation is somewhat inefficient since under the hood
it uses deferred CONSTRAINT TRIGGERs, which have to be FOR EACH ROW
triggers...

To use this:
- download commit_trigger.sql (reviews welcome!)  - run this in psql:
     -- Load commit trigger functionality:     \i commit_trigger.sql
- run this in psql to demo:
     -- CREATE COMMIT TRIGGER egt     -- EXECUTE PROCEDURE commit_trigger.example_proc();     INSERT INTO
commit_trigger.triggers                    (trig_name, proc_schema, proc_name)     SELECT 'egt', 'commit_trigger',
'example_proc';
     CREATE SCHEMA eg;     CREATE TABLE eg.x(a text primary key);     BEGIN;         INSERT INTO eg.x (a)
VALUES('foo');        INSERT INTO eg.x (a) VALUES('bar');     COMMIT;     INSERT INTO eg.x (a) VALUES('foobar');
INSERTINTO eg.x (a) VALUES('baz');     DROP TABLE eg.x CASCADE;
 
  There should be exactly one NOTICE for the first transaction, and  exactly one each for the two INSERTs subsequently
donein auto-commit  mode.
 

I hope this will put to rest all objections to COMMIT TRIGGERS, and that
it will lead to a proper implementation.

Uses of COMMIT TRIGGERs include:
- update/refresh view materializations- consistency checks- NOTIFY- record history (in particular, record transaction
boundaries)-and, no doubt, others
 

https://github.com/twosigma/postgresql-contrib/
https://github.com/twosigma/postgresql-contrib/blob/master/commit_trigger.sql
https://raw.githubusercontent.com/twosigma/postgresql-contrib/master/commit_trigger.sql

Cheers,

Nico
-- 


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

Hi,

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.

Greetings,

Andres Freund


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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

Hi,

On 2017-09-15 14:19:29 -0500, Nico Williams wrote:
> Please see my post and the linked file to see why.

The discussions here are often going to be referred back to in years, so
external links where we aren't sure about the longevity (like e.g. links
to the mailing list archive, where we're fairly sure), aren't liked
much.  If you want to argue for a change, it should happen on-list.

Greetings,

Andres Freund


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

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.

Note that DEFERRED CONSTRAINT TRIGGERs already have these semantics,
except of course that an unprivileged user can force them to run
IMMEDIATEly rather than DEFERRED.

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
procedurethat  can *atomically* examine the totatility of the changes made by the  user.
 

This allows one to perform consistency checks across the entire DB.
One might speed them up by examining deltas recorded by for-each-row
triggers, but logically one could check the entire state of the DB.

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.

These are all good things -- or at least they aren't bad things.

For NOTIFY, it would be nice to have a post-commit trigger, though such
a thing could do nothing more than NOTIFY!

(A "post-commit" trigger could certainly not write to the DB unless it
did so in a new transaction that itself could not invoke post-commit
triggers that could write more...  Such a thing would be very strange!
But I'm not asking for a post-commit trigger feature.)

Nico
-- 


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

On Fri, Sep 15, 2017 at 12:25:08PM -0700, Andres Freund wrote:
> On 2017-09-15 14:19:29 -0500, Nico Williams wrote:
> > Please see my post and the linked file to see why.
> 
> The discussions here are often going to be referred back to in years, so
> external links where we aren't sure about the longevity (like e.g. links
> to the mailing list archive, where we're fairly sure), aren't liked
> much.  If you want to argue for a change, it should happen on-list.

Fair enough.  I thought I had given enough detail, but here is the code.
It's just an event trigger that ensures every table has a DEFERRED
CONSTRAINT TRIGGER that runs a function that debounces invocations so
that the "commit trigger" function runs just once:

/** Copyright (c) 2017 Two Sigma Open Source, LLC.* All Rights Reserved** Permission to use, copy, modify, and
distributethis software and its* documentation for any purpose, without fee, and without a written agreement* is hereby
granted,provided that the above copyright notice and this* paragraph and the following two paragraphs appear in all
copies.**IN NO EVENT SHALL TWO SIGMA OPEN SOURCE, LLC BE LIABLE TO ANY PARTY FOR* DIRECT, INDIRECT, SPECIAL,
INCIDENTAL,OR CONSEQUENTIAL DAMAGES, INCLUDING* LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS
DOCUMENTATION,*EVEN IF TWO SIGMA OPEN SOURCE, LLC HAS BEEN ADVISED OF THE POSSIBILITY OF* SUCH DAMAGE.** TWO SIGMA OPEN
SOURCE,LLC SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING,* BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
MERCHANTABILITYAND FITNESS* FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS IS"* BASIS, AND TWO
SIGMAOPEN SOURCE, LLC HAS NO OBLIGATIONS TO PROVIDE* MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.*/
 

/** This file demonstrates how to create a "COMMIT TRIGGER" for* PostgreSQL using CONSTRAINT TRIGGERs.** There have
beenmany threads on the PG mailing lists about commit* triggers, with much skepticism shown about the possible
semanticsof* such a thing.** Below we demonstrate reasonable, useful, and desirable semantics, how* to obtain them with
PGtoday.** There are three shortcomings of this implementation:** a) It is possible defeat this implementation by
using**     SET CONSTRAINTS ... IMMEDIATE;**    or otherwise disabling the triggers created under the hood herein.**
Theability to make these triggers run early can be *dangerous*,*    depending on the application.  It is especially
dangerousgiven*    that no privilege is needed in order to do this, and there's no*    way for a CONSTRAINT TRIGGER to
detectwhen it is called _last_,*    only when it is called _first_, in any transaction.** b) This implementation
serializeswrite transactions implicitly by*    having a single row encode commit trigger state.**    (This is easily
fixedthough.)** c) This implementation is inefficient because CONSTRAINT TRIGGERs*    have to be FOR EACH ROW triggers.
Thus a transaction that does*    1,000 inserts will cause 999 unnecessary trigger procedure calls*    under the hood.
Also,because CONSTRAINT TRIGGERs have to be FOR*    EACH ROW triggers, PG has to track OLD/NEW row values for all*
affectedrows, even though commit triggers obviously don't need*    this.** (Also, for simplicity we use SECURITY
DEFINERfunctions here,* otherwise we'd have to have additional code to grant to* public the ability to call our
functions. We would need additional* code by which to ensure that users do not toggle internal state to* prevent commit
triggerexecution.)** For example, to create a commit trigger that invokes* commit_trigger.example_proc() at the end of
any_write_ transaction,* run the following in psql:**      -- Load commit trigger functionality:*      \i
commit_trigger.sql**     -- CREATE COMMIT TRIGGER egt*      -- EXECUTE PROCEDURE commit_trigger.example_proc();*
INSERTINTO commit_trigger.triggers*                      (trig_name, proc_schema, proc_name)*      SELECT 'egt',
'commit_trigger','example_proc';** Demo:**  db=# \i commit_trigger.sql*  <noise>*  db=# INSERT INTO
commit_trigger.triggers* db-#                 (trig_name, proc_schema, proc_name)*  db-# SELECT 'egt',
'commit_trigger','example_proc';*  db=#*  db=# CREATE SCHEMA eg;*  CREATE SCHEMA*  db=# CREATE TABLE eg.x(a text
primarykey);*  CREATE TABLE*  db=# BEGIN;*  BEGIN*  db=#     INSERT INTO eg.x (a) VALUES('foo');*  INSERT 0 1*  db=#
INSERT INTO eg.x (a) VALUES('bar');*  INSERT 0 1*  db=# COMMIT;*  NOTICE:  example_proc() here!  Should be just one for
thisTX (txid 208036)*  CONTEXT:  PL/pgSQL function example_proc() line 3 at*  RAISE*  COMMIT*  db=# INSERT INTO eg.x
(a)VALUES('foobar');*  NOTICE:  example_proc() here!  Should be just one for this TX (txid 208037)*  CONTEXT:  PL/pgSQL
functionexample_proc() line 3 at*  db=# INSERT INTO eg.x (a) VALUES('baz');*  NOTICE:  example_proc() here!  Should be
justone for this TX (txid 208038)*  CONTEXT:  PL/pgSQL function example_proc() line 3 at*  db=#** Semantics:**  -
committrigger procedures called exactly once per-transaction that*    had any writes (even if they changed nothing in
theend)**    (*Unless* someone first runs SET CONSTRAINTS ALL IMMEDIATE!)**  - commit trigger procedures called in
orderof commit trigger name*    (ascending)**  - commit trigger procedures may perform additional write operations,*
andif so that will NOT cause additional invocations of commit*    trigger procedures**  - commit trigger procedures may
RAISEEXCEPTION, triggering a*    rollback of the transaction** The above semantics are exactly what would be desired of
aproperly-* integrated COMMIT TRIGGER feature, except that it SHOULD NEVER be* possible to cause commit triggers to
fireearly by executing* SET CONSTRAINTS ALL IMMEDIATE.*/
 

\set ON_ERROR_STOP on

CREATE SCHEMA IF NOT EXISTS commit_trigger;

CREATE TABLE IF NOT EXISTS commit_trigger.triggers (   trig_name       TEXT PRIMARY KEY,   proc_schema     TEXT NOT
NULL,  proc_name       TEXT NOT NULL
 
);

/* State needed to prevent more than one commit trigger call per-commit */
CREATE TABLE IF NOT EXISTS commit_trigger.commit_trigger_called (   _id BIGINT PRIMARY KEY CHECK(_id = 0) DEFAULT(0),
_txidBIGINT CHECK(_txid = txid_current()) DEFAULT(txid_current()))
 
;

INSERT INTO commit_trigger.commit_trigger_called
SELECT
ON CONFLICT DO NOTHING;

/* Example commit trigger procesdure */
CREATE OR REPLACE FUNCTION commit_trigger.example_proc()
RETURNS VOID AS $$
BEGIN   RAISE NOTICE       'example_proc() here!  Should be just one for this TX (txid %)',       txid_current();
END $$ LANGUAGE PLPGSQL SECURITY INVOKER SET search_path = commit_trigger;

CREATE OR REPLACE VIEW commit_trigger.synthetic_triggers AS
SELECT rn.nspname   AS tbl_schema,      r.relname    AS tbl_name,      coalesce(t.tgname,
'zzz_commit_trigger_'|| rn.nspname || '_' || r.relname) AS tg_name,      t.tgenabled  AS tg_enabled
 
FROM pg_catalog.pg_class r
JOIN pg_catalog.pg_namespace rn ON rn.oid = r.relnamespace
LEFT JOIN pg_trigger t ON t.tgrelid = r.oid
WHERE r.relkind = 'r' AND     (t.tgname IS NULL OR t.tgname LIKE 'zzz\_commit\_trigger\_%') AND     rn.nspname NOT IN
('commit_trigger','pg_catalog');
 

CREATE OR REPLACE FUNCTION commit_trigger.invoke_commit_triggers()
RETURNS VOID AS $$
DECLARE   t record;
BEGIN   FOR t IN (       SELECT ct.proc_schema AS proc_schema, proc_name AS proc_name       FROM
commit_trigger.triggersct       JOIN pg_catalog.pg_proc p ON ct.proc_name = p.proname       JOIN
pg_catalog.pg_namespacepn ON p.pronamespace = pn.oid AND                                          pn.nspname =
ct.proc_schema      ORDER BY trig_name ASC)   LOOP       EXECUTE format($q$               SELECT %1$I.%2$I();
$q$,t.proc_schema, t.proc_name);   END LOOP;
 
END $$ LANGUAGE PLPGSQL SECURITY INVOKER SET search_path = commit_trigger;

CREATE OR REPLACE FUNCTION commit_trigger.trig_proc()
RETURNS TRIGGER AS $$
BEGIN   IF NOT EXISTS(           SELECT *           FROM commit_trigger.commit_trigger_called           WHERE _txid =
txid_current())THEN       /*RAISE NOTICE 'Calling commit triggers (txid = %)', txid_current();*/       PERFORM
commit_trigger.invoke_commit_triggers();      UPDATE commit_trigger.commit_trigger_called       SET _txid =
txid_current();  END IF;   RETURN  CASE TG_OP               WHEN 'INSERT' THEN NEW               WHEN 'UPDATE' THEN NEW
             ELSE OLD           END;
 
END $$ LANGUAGE PLPGSQL SECURITY INVOKER SET search_path = commit_trigger;

CREATE OR REPLACE FUNCTION commit_trigger.make_triggers()
RETURNS void AS $$
DECLARE   t record;
BEGIN   FOR t IN (       SELECT st.tg_name       AS tg_name,              st.tbl_schema    AS tbl_schema,
st.tbl_name     AS tbl_name       FROM commit_trigger.synthetic_triggers st       WHERE st.tg_enabled IS NULL)   LOOP
   EXECUTE format($q$               CREATE CONSTRAINT TRIGGER %1$I               AFTER INSERT OR UPDATE OR DELETE
       ON %2$I.%3$I               INITIALLY DEFERRED FOR EACH ROW               EXECUTE PROCEDURE
commit_trigger.trig_proc();          $q$, t.tg_name, t.tbl_schema, t.tbl_name);   END LOOP;   DELETE FROM
commit_trigger.triggersct   WHERE NOT EXISTS (       SELECT p.*       FROM pg_catalog.pg_proc p       JOIN
pg_catalog.pg_namespacepn ON p.pronamespace = pn.oid       WHERE pn.nspname = ct.proc_schema AND p.proname =
ct.proc_name  );
 
END $$ LANGUAGE PLPGSQL SECURITY DEFINER SET search_path = commit_trigger;

CREATE OR REPLACE FUNCTION commit_trigger.event_make_triggers()
RETURNS event_trigger AS $$
BEGIN   PERFORM commit_trigger.make_triggers();
END $$ LANGUAGE PLPGSQL SECURITY DEFINER SET search_path = commit_trigger;

/** Make sure we define our internal triggers for all future new TABLEs,* and that we cleanup when commit trigger
proceduresare DROPped.*/
 
DROP EVENT TRIGGER IF EXISTS commit_trigger_make_triggers;
CREATE EVENT TRIGGER commit_trigger_make_triggers ON ddl_command_end
WHEN tag IN ('CREATE TABLE', 'DROP FUNCTION')
EXECUTE PROCEDURE commit_trigger.event_make_triggers();

/* Create our internal triggers for all existing tables now */
SELECT commit_trigger.make_triggers();


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

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
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
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
for such a definition.  Triggers are generally expected to be objects
you can create any number of.

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;
and they're pretty useless in the face of DDL too.  It's going to be hard
to produce a very credible use-case without a lot of work to expose that
information somehow.

(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.)

> 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.
        regards, tom lane


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

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

I guess this got lost over the weekend and subsequent week (I was on
vacation).

On Fri, Sep 15, 2017 at 04:03:35PM -0500, Nico Williams wrote:
> 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!

Not to belabor the point, though I'm being redundant: the important
thing is that we have something we can run after the last statement from
the _client_.  Trigger order is already what it is, and that's fine
because the dev/dba controls that, but does not control what the
_client_ sends.

I'm using PostgreSQL for an all-SQL application with:
- [WISH] access via the PostgreSQL protocol (unconstrained)
- [CURRENT] access via HTTP via PostgREST (fairly constrained)

This means we don't have full control over the client.  Even if we did,
the client authenticates as users, and we can't ensure that the users
aren't connecting separately and sending arbitrary commands.

Incidentally, there's also a need for a connect-time or BEGIN-time
trigger to simplify other trigger functions.  I often write trigger
functions that do CREATE TEMP TABLE IF NOT EXISTS -- a handy technique.
But it'd be nice if we could have temp tables (and indexes, and views,
and...) automatically created for each session or transaction.  If
nothing else, it would allow statement/row triggers to do less work,
thus run marginally faster.

Nico
-- 


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

The attached file demonstrates how to create COMMIT, BEGIN, and even
session CONNECT TRIGGERs for PostgreSQL using PlPgSQL only, via normal
and CONSTRAINT TRIGGERs.

There have been many threads on the PG mailing lists about commit
triggers, with much skepticism shown about the possible semantics of
such a thing.  Below we list use cases, and demonstrate reasonable,
useful, and desirable semantics.

The previous version of this could be defeated by using SET CONSTRAINTS
..  IMMEDIATE.  This version detects this when it would cause commit
triggers to run too soon, and causes an exception to be raised.  The
technique used to detect this could be used by anyone whose business
logic breaks when SET CONSTRAINTS ..  IMMEDIATE is used.

There is one shortcoming of this implementation: it is inefficient
because it has to use FOR EACH ROW triggers under the hood, so if you
do 1,000 inserts, then 999 of the resulting internal trigger
procedure invocations will be unnecessary.  These are FOR EACH ROW
triggers because that is the only level permitted for CONSTRAINT
triggers, which are used under the hood to trigger running at commit
time.

(It would be nice if CONSTRAINT triggers could be FOR EACH STATEMENT
too...)

Use-cases:

 - create TEMP schema before it's needed by regular triggers

   This can be useful if CREATE TEMP TABLE IF EXISTS and such in regular
   triggers could slow them down.

 - cleanup internal, temporary state left by triggers from earlier
   transactions

 - perform global consistency checks (assertions, if you like)

   Note that these can be made to scale by checking only the changes
   made by the current transaction.  Transition tables, temporal
   tables, audit tables -- these can all help for the purpose of
   checking only deltas as opposed to the entire database.

   Related: there was a thread about a patch to add assertions:


https://www.postgresql.org/message-id/flat/20131218113911.GC5224%40alap2.anarazel.de#20131218113911.GC5224@alap2.anarazel.de

 - update materializations of views when all the relevant deltas can
   be considered together

   I use an alternatively view materialization system that allows direct
   updates of the materialization table, and records updates in a
   related history table.  Being about to update such materializations
   via triggers is very convenient; being able to defer such updates as
   long as possible is a nice optimization.

 - call C functions that have external side-effects when you know the
   transaction will succeed (or previous ones that have succeeded but
   not had those functions called)

Semantics:

 - connect/begin/commit trigger procedures called exactly once per-
   transaction that had any writes (even if they changed nothing
   in the end), with one exception:

    - exceptions thrown by triggers may be caught, and the triggering
      statement retried, in which case triggers will run again

 - connect/begin trigger procedures called in order of trigger
   name (ascending) before any rows are inserted/updated/deleted by
   any DML statements on non-TEMP tables in the current transaction

 - commit trigger procedures called in order of commit trigger name
   (ascending) at commit time, after the last statement sent by the
   client/user for the current transaction

 - begin and commit trigger procedures may perform additional write
   operations, and if so that will NOT cause additional invocations
   of begin/commit trigger procedures

 - commit trigger procedures may RAISE EXCEPTION, triggering a
   rollback of the transaction

Nico
--

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