Re: transition table behavior with inheritance appears broken (was: Declarative partitioning - another take)

On Wed, May 10, 2017 at 9:57 AM, Alvaro Herrera
<alvherre(at)2ndquadrant(dot)com> wrote:
> Thomas Munro wrote:
>
>> Recall that transition tables can be specified for statement-level
>> triggers AND row-level triggers. If you specify them for row-level
>> triggers, then they can see all rows changed so far each time they
>> fire.
>
> Uhmm ... why do we do this? It seems like a great way to cause much
> confusion. Shouldn't we see the transition table containing the whole
> set for statement-level triggers only, and give row-level triggers just
> the individual affected row each time?

I assumed that had come from the standard. I don't have a published
standard, but I have just taken a quick look at one of the publicly
available drafts dated 2006. I think its model is that the transition
tables are always conceptually there, and NEW and OLD are just range
variables over those tables. That may explain why transition tables
are mentioned in the context of row-level triggers, and it may be that
the spec's authors never intended row-level triggers to be able to see
the (partial) transition table other than through the range variables
that access exactly one row, but I don't see any wording that
explicitly says so in the spec. Do you? Thoughts, Kevin?

After thinking about this some more, it's not only the conversion to
some arbitrary parent tuple type that would be surprising to a user of
inheritance + triggers + transition tables, it's the fact that a
row-level trigger on a given child table will also see tuples
collected from other tables in the hierarchy. I think I didn't quite
get that right in -v2: it should probably build
TriggerTransitionFilter from union of all child tables' transition
table flags, not just the named table, so that if any child table has
a row trigger we start collecting transition tuples from all others
for it to see... That sounds pretty crazy to me.

So, assuming we want to proceed with this plan of collecting
transition tuples from children, I see approximately 3 choices:

1. Reject transition tables on row-level triggers.
2. Reject transition tables on row-level triggers on tables that
inherit from other tables.
3. Continue to allow transition tables on row-level triggers, but
document that they must be prepared to see transition tuples as they
would when querying arbitrary parent tables, and see tuples from other
tables in the hierarchy (!)

Another possibility which I haven't seriously considered is per-table
transition tables so you'd collect each child's tuples separately.

I take Alvaro's comment as a vote for 1. I vote for 1 too.

--
Thomas Munro
http://www.enterprisedb.com