Re: Deferred constraint trigger semantics

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