Re: Deferred constraint trigger semantics - Mailing list pgsql-general

On Wed, 2022-05-11 at 15:54 -0700, Bryn Llewellyn wrote:
> I re-read the penultimate paragraph in Laurenz's post:
> 
> «
> By making the trigger INITIALLY DEFERRED, we tell PostgreSQL to check the condition at COMMIT time.
> »
> 
> I have always understood that (in Postgres and any respectable RDBMS) commits in a multi-session
> environment are always strictly serialized—irrespective of the transaction's isolation level.
> Am I correct to assume this is the case for Postgres? I took "at COMMIT time" to mean "as part
> of the strictly serialized operations that implement a session's COMMIT".

I am not sure what you mean by serialized commits.  Transactions are concurrent, and so are
commits.  COMMIT takes some time, during which several things happen, among them executing
deferred constraints, writing a WAL record and flushing the WAL.  The only thing that is
necessarily serialized is writing the WAL record.

> But I see (now) that you argue that this is not the case, thus:
> 
> «
> This will reduce the window for the race condition a little, but the problem is still there.
> If concurrent transactions run the trigger function at the same time, they won’t see each other’s modifications.
> »
> 
> I take what you say in your post to mean that each session executes its deferred constraint
> check (by extension, not just for constraint triggers but for all deferred constraint cases)
> momentarily *before* COMMIT so that the effect is only to reduce the duration of the race condition
> window rather than to eliminate it.

In the case of constraint triggers, yes.
But there is no race condition for primary key, unique and foreign key constraints, because
they also "see" uncommitted data.

> So it all depends on a lawyerly reading of the wording "at COMMIT time". The current CREATE TABLE doc says this:
> 
> «
> If the constraint is INITIALLY DEFERRED, it is checked only at the end of the transaction.
> »
> 
> The wording "at the end of the transaction" is not precise enough to adjudicate—and so the key
> question remains: Is a deferred constraint checked:
> 
> (a) as part of the strictly serialized operations that implement a session's COMMIT?
> 
> or
> 
> (b) momentarily *before* COMMIT and not within the serialized COMMIT execution?
> 
> So… (asking the wider audience) is the answer (a) or (b)? An if it's (b), why? After all, (b) brings
> the race condition risk. Is (a) simply not feasible?

COMMITs are not serialized.  You seem to think that as soon as one transaction's COMMIT starts
processing, no other transaction may COMMIT at the same time.  That is not the case.

> 
> > > 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?
> > 
> > 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.
> 
> This doesn't seem to be what you wrote. These two headings [...]

Then I must have been unclear.  Or you only looked at the headings.

> As I reason it, if you use the SERIALIZABLE approach, then an ordinary immediate AFTER EACH
> STATEMENT trigger will work fine—precisely because of how that isolation level is defined.
> So here, a deferred constraint trigger isn't needed and brings no value.

Now that is absolutely true.  If you use the big hammer of SERIALIZABLE, there can be no
anomaly, and it is unnecessary to keep the window for a race condition small.
Deferred triggers and constraints still have a value, because they see the state
of the database at the end of the whole transaction.

> This implies that if a deferred constraint trigger is to have any utility, it must be safe
> to use it (as I tested it) at the READ COMMITTED level. I do see that, though I appear to
> be testing this, I cannot do a reliable test because I cannot, in application code, open up,
> and exploit, a race condition window after COMMIT has been issued. (I *am* able to do this
> to expose the fact that "set constraints all immediate" is unsafe.)

This sentence lacks the definition of what you mean by "safe", on which all hinges.

If "safe" means that you can use them to make sure that a certain condition is always
satisfied (like in a constraint), they are not safe.  But that is not the only use for
a trigger.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com