Re: Deferred constraint trigger semantics

laurenz.albe@cybertec.at wrote:

Be "seeing" I didn't mean "show to the user". I mean that the code that implements PostgreSQL constraints takes uncommitted data into account.

The documentation describes that for the case of uniqueness in some detail:

https://www.postgresql.org/docs/current/index-unique-checks.html

I'd say that the proper mental model is that you don't need to care. The ACID properties are guarantees that the database makes, and these guarantees are usually negative: "no sequence of actions can result in the violation of a unique constraint" or "you don't get to see uncommitted data".

The exact sequence of what happens during COMMIT is interesting, but irrelevant to the programmer.  All that counts is "a deferred constraint is checked between the time that COMMIT starts processing and the time that it returns".

If you want to know more, you have to start reading the code. It is open source and well documented.