On Tue, 2022-05-10 at 17:46 -0700, Bryn Llewellyn wrote: > I looked at the sections "CREATE TRIGGER" and "Chapter 39. Triggers" in the > Current PG doc. > But I failed to find any information about the semantics of the deferred > constraint trigger > or about the use cases that motivated this feature. Nor could I find any code > examples. > Internet Search turned up this 2019 post by Laurenz Albe's—but nothing else > at all. > > https://www.cybertec-postgresql.com/en/triggers-to-enforce-constraints/ > > (This is why I CC'd you, Laurenz.)
So I guess I should answer. About the starting paragraph of your mail: Constraint triggers are a syntactic leftover from the way that triggers are implemented in PostgreSQL. There is different syntax now, but it was decided to leave constraint triggers, since they may have some use. > [Lots of ruminations and wandering throughts] Sorry, that was too much for me to comment on - that would require a mid-sized article. > Is my entire concept (and Laurenz's too) fundamentally flawed? Specifically, > is > querying a trigger's base table in a "for each row" trigger fundamentally > unsound > and not supported? (In Oracle Database, it causes the notorious "mutating > table" > runtime error.) My post claims that constraint triggers alone are *not* a sufficient solution to validate constraints - you need additional locking or SERIALIZABLE isolation to make that work reliably. That does not mean that using constraint triggers is unsound or unsupported, and the fact that Oracle's implementation of transaction isolation is somewhat shoddy has little impact on that. Yours, Laurenz Albe
