Re: Deferred constraint trigger semantics - Mailing list pgsql-general
| From | David G. Johnston |
|---|---|
| Subject | Re: Deferred constraint trigger semantics |
| Date | |
| Msg-id | CAKFQuwZFYONkpmnCgQ_cuD7iQYrbyrW58zhyvx3qpVBeVn6Whw@mail.gmail.com Whole thread Raw |
| In response to | Re: Deferred constraint trigger semantics (Bryn Llewellyn <bryn@yugabyte.com>) |
| Responses |
Re: Deferred constraint trigger semantics
|
| List | pgsql-general |
On Tue, May 10, 2022 at 7:52 PM Bryn Llewellyn <bryn@yugabyte.com> wrote:
david.g.johnston@gmail.com wrote:* However, SQL commands executed in a row-level BEFORE trigger will see the effects of data changes for rows previously processed in the same outer command. This requires caution, since the ordering of these change events is not in general predictable; an SQL command that affects multiple rows can visit the rows in any order.»Strangely, the wording here, explicit as it is, makes no mention of what you might expect to see in an AFTER EACH ROW trigger.
Because the previous, quoted, paragraph did that. Because of 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).
I'll take this to be the case 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.
The mention of unpredictable results in the third bullet in the BEFORE case 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. I'll assume that you (all) know what I mean. 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).
the actual order in which I see the "raise info" output is unpredictable while the values shown in each *are* predictable. Can I rely on this rule?
Absent a deferred constraint I would say yes. 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. This is also an area that the SQL Standard does make rules in. And given that constraints are defined per-row everywhere else there is a pull to not push the envelope of our extension too far.
David J.
pgsql-general by date: