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
