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

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