Re: Deferred constraint trigger semantics

> david(dot)g(dot)johnston(at)gmail(dot)com wrote:
>
>> bryn(at)yugabyte(dot)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

Thanks, David. Those sentences were too deeply buried, in a page with no subsections, for me to spot by ordinary searching in the page. The term "Row-level BEFORE" trigger subverted my search for "BEFORE EACH ROW" trigger—which is the term that I'm used to. This is another lesson for me to read every word in what looks like relevant doc, from start to finish, like a book. I'm chastened.

The next section, "39.2. Visibility of Data Changes"
https://www.postgresql.org/docs/current/trigger-datachanges.html

says this:

«
* Statement-level triggers follow simple visibility rules: none of the changes made by a statement are visible to statement-level BEFORE triggers, whereas all modifications are visible to statement-level AFTER triggers.

* The data change (insertion, update, or deletion) causing the trigger to fire is naturally not visible to SQL commands executed in a row-level BEFORE trigger, because it hasn't happened yet.

* 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. It's a bit of a stretch to put the sentences from the previous section that you quoted together with these three bullets to conclude this: querying the trigger's base-table's content *is* allowed from the trigger's function for all of the five timing points: BEFORE and AFTER EACH ROW (not deferred), BEFORE and AFTER EACH STATEMENT (not deferred), and AFTER EACH ROW (deferred to commit time) is indeed supported. I'll take this to be the case unless anybody contradicts me.

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. 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). But this seems to be sound inference from the rules that were stated. I'll take this, too, to be the case unless anybody contradicts me.

I assume, though, that considering this output that I showed in my original mail:

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

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?

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.