Re: Deferred constraint trigger semantics

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"

says this:

«

»

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:

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.