From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | Bryn Llewellyn <bryn(at)yugabyte(dot)com> |
Cc: | pgsql-general list <pgsql-general(at)lists(dot)postgresql(dot)org>, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
Subject: | Re: Deferred constraint trigger semantics |
Date: | 2022-05-11 03:33:13 |
Message-ID: | CAKFQuwZFYONkpmnCgQ_cuD7iQYrbyrW58zhyvx3qpVBeVn6Whw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Tue, May 10, 2022 at 7:52 PM Bryn Llewellyn <bryn(at)yugabyte(dot)com> wrote:
>
> *david(dot)g(dot)johnston(at)gmail(dot)com <avid(dot)g(dot)johnston(at)gmail(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Masahiko Sawada | 2022-05-11 04:45:24 | Re: Support logical replication of DDLs |
Previous Message | Bryn Llewellyn | 2022-05-11 02:52:32 | Re: Deferred constraint trigger semantics |