Обсуждение: Deferred constraint trigger semantics

david.g.johnston@gmail.com wrote:

bryn@yugabyte.com wrote:

INFO:  trg fired. new.v = 80, n = 8

It shows the expected "new" value each time it fires. And yet the query reflects the table content on statement completion. This seems to be very strange.

From the documentation:

"Row-level BEFORE triggers fire immediately before a particular row is operated on, while row-level AFTER triggers fire at the end of the statement (but before any statement-level AFTER triggers)."

https://www.postgresql.org/docs/current/trigger-definition.html

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

david.g.johnston@gmail.com wrote:

bryn@yugabyte.com wrote:

...makes no mention of what you might expect to see in an AFTER EACH ROW trigger.

...the absence of a comment declaring a guarantee of order means that, like the comment for the row-level BEFORE trigger, the row-level AFTER row ordering is not guaranteed (even if one cannot produce a counter-example in today's codebase).

...unless anybody contradicts me.

Caveat emptor...? I wouldn't be surprised that doing so is technically possible in all cases - as to whether a particular algorithm is sound, to some extent, isn't something we try to predict. We do try to describe all the known interactions though - and let the user work within what those mean for them.

...implies that there's no such unpredictability in the AFTER EACH ROW cases.

I would not read it that way. In general, absence of mention of predictability like this means there is none - that some other sentence goes into more detail doesn't change that.

But there has to be a subtle caveat here for the deferred constraint trigger when the txn changes two or more tables, all of which participate in the query that the trigger function issues… The "raise info" output below illustrates my point (n changes from 5 to 8).

I'm failing to see the deferral aspect of that example. First statement finishes, sees the 5 inserts, next statement finishes, sees 3 more inserts. Not, both statements finish, triggers fire, triggers see all 8 inserts (which I suspect they will if you actually perform deferral).

...Your test case for the deferred constraint, that supposedly allows for the insertion of invalid data per the specification of the constraint trigger, isn't something I've worked through yet; and as written reads like a bug report.

Is there a fundamental reason why a deferred AFTER EACH STATEMENT constraint trigger is not allowed? Nothing in what is explained in the "Overview of Trigger Behavior" and "Visibility of Data Changes" sections lets me see why the present restriction is needed.

I imagine having to keep around a working set of what are the changed records is both memory intensive and also problematic should a future statement make yet more changes to the table… And given that constraints are defined per-row everywhere else there is a pull to not push the envelope of our extension too far.

laurenz.albe@cybertec.at wrote:

bryn@yugabyte.com wrote:

…Internet Search turned up this 2019 post by Laurenz Albe—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?

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.

david.g.johnston@gmail.com wrote:

bryn@yugabyte.com wrote:

Oops. I did a copy-and-paste error on going from my test env. to email and missed out the "deferral" that I'd intended. For completeness, here's the test that I meant:

create constraint trigger trg
after insert on t1
for each row
execute function trg_fn();

create constraint trigger trg
after insert on t2
initially deferred
for each row
execute function trg_fn();

It adds the "initially deferred" decoration to the "create constraint trigger" statement. This is (still) the result:

You only added it to the uninteresting trigger on t2.  It's the t1 trigger where I'd expect the behavior to change.  I'm assuming your test does both (not in a position to test it myself at the moment).

[What I wrote here was rubbish, given that my test code was not what I claimed it was.]

[David's response here is now moot.]
 

With respect to « having to keep around a working set of what are the changed records » I think that the complexity that you envisaged is avoided by the (emergent) rule that an AFTER EACH STATEMENT trigger cannot see "old" and "new" values. In other words, all you can sensibly do in its function is ordinary SQL that sees the current state at the moment it fires.

To my surprise, it *is* legal to write code that accesses "old" and "new" values. But, because many rows can be affected by a single statement, and the trigger fires just once, the meanings of "old" and "new" are undefined. I've seen that, in any test that I do, both are always set to NULL (which seems reasonable).

I was thinking more about transition tables - though I admit it's not a complete thought given their opt-in nature.

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

postgres.rocks@gmail.com wrote:

It adds the "initially deferred" decoration to the "create constraint trigger" statement. This is (still) the result:

INFO:  trg fired. new.v = 10, n = 5
INFO:  trg fired. new.v = 20, n = 5
INFO:  trg fired. new.v = 30, n = 5
INFO:  trg fired. new.v = 40, n = 5
INFO:  trg fired. new.v = 50, n = 5
INFO:  trg fired. new.v = 60, n = 8
INFO:  trg fired. new.v = 70, n = 8

INFO:  trg fired. new.v = 80, n = 8

Because You can do

create constraint trigger trg

after insert on t2

deferrable initially deferred

for each row

execute function trg_fn();

You didn't explicitly defer the trigger trg on t1!. That means after you insert on t1 then the trigger trg on t1 invoked rather than on commit time. 

If you

create constraint trigger trg

after insert on t1

deferrable initially deferred

for each row

execute function trg_fn();

create constraint trigger trg

after insert on t2

deferrable initially deferred

for each row

execute function trg_fn();

then you will get

INFO:  00000: trg fired. new.v = 10, n = 8
INFO:  00000: trg fired. new.v = 20, n = 8
INFO:  00000: trg fired. new.v = 30, n = 8
INFO:  00000: trg fired. new.v = 40, n = 8
INFO:  00000: trg fired. new.v = 50, n = 8
INFO:  00000: trg fired. new.v = 60, n = 8
INFO:  00000: trg fired. new.v = 70, n = 8

INFO:  00000: trg fired. new.v = 80, n = 8 

On Thu, 2022-05-12 at 22:06 -0700, Bryn Llewellyn wrote:
> 
> > 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.
> 
> I can't follow you here, sorry. I tried this:
> 
> create table t(
>   k serial primary key,
>   v int not null,
>   constraint t_v_unq unique(v) initially deferred);
> 
> -- RED
> start transaction isolation level read committed;
> insert into t(v) values (1), (2);
> select k, v from t order by k;
> 
> -- BLUE
> start transaction isolation level read committed;
> insert into t(v) values (1), (3);
> select k, v from t order by k;
> 
> -- RED
> commit;
> select k, v from t order by k;
> 
> -- BLUE
> select k, v from t order by k;
> commit;
> 
> select k, v from t order by k;
> 
> The first "select" from the "BLUE" session at the very end produces this:
> 
>  k | v 
> ---+---
>  1 | 1
>  2 | 2
>  3 | 1
>  4 | 3
> 
> This doesn't surprise me. It's ultimately illegal. But not yet. (Before "RED" committed, "BLUE"
> didn't see the rows with "k = 1" and "k = 2". So it isn't seeing any other sessions uncommitted
> data—but only it's own uncommitted data.)

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

> Then, when "BLUE" commits, it (of course) gets this:
> 
> ERROR:  duplicate key value violates unique constraint "t_v_unq"
> DETAIL:  Key (v)=(1) already exists.
> 
> Then it sees (of course, again) only the rows with "k = 1" and "k = 2"—the same as what "RED" saw.
> 
> It seems to be impossible to do a test in slow motion where "RED" and "BLUE" each issues "commit"
> at the exact same moment. So thinking about this scenario doesn't tell me if:
> 
> (a) Each session runs its constraint check and the rest of what "commit" entails in a genuinely serialized fashion.
> 
> OR
> 
> (b) Each session first runs its constraint check (and some other stuff) non-serializedly—and only
>     then runs the small part of the total "commit" action (the WAL part) serializedly.
>     (This would result in bad data in the database at rest—just as my contrived misuse of
>     "set constraints all immediate" left things in my "one or two admins" scenario.)

I'd say that (b) is a more accurate description.

> The (a) scheme sounds correct. And the (b) scheme sounds wrong. Why would PG prefer to implement (b) rather than
(a)?
> 
> I'm clearly missing something.

Because (a) would result in terrible performance if there are many concurrent transactions.

I don't see why (b) is wrong - as your example shows, the behavior is correct.

Perhaps you have to understand what a PostgreSQL "snapshot" is and that the exact moment at
which a row was created is not important - it is the transaction numbers in "xmin" and "xmax"
that count.

> > 
> Where, in the PG doc, can I read the account of the proper mental model for the application programmer?
> It seems to be impossible to conduct an experiment that would disprove the hypothesis that one,
> or the other, of these mental models is correct.

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.

> > > > 
> Your post's testcase used the condition "at least one guard on duty" [...]
> 
> My testcase used a stricter rule: the table of staff must have exactly one or two rows where
> the job is "Admin". So, here, concurrent sessions can break the rule (when the txn starts
> with one "Admin") by updating different rows to make them "Admin" or by inserting different
> new "Admin" rows. I've convinced myself by experiment that an ordinary trigger can enforce
> this rule when contending sessions use "serializable" isolation. Am I right that you'd say
> that no pessimistic locking scheme can enforce the rule at lower isolation levels except the
> brute-force "lock table"?

If the transaction can insert two rows, I'd agree.
If the transaction only inserts a single row, than it could SELECT ... FOR NO KEY UPDATE the
one existing row, thus serializing the concurrent transactions.

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

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.

laurenz.albe@cybertec.atwrote: