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

[Apologies to all for my recent absence from community lists, and
special thanks to Thomas and Robert for picking up the slack.]

On Tue, May 9, 2017 at 4:51 PM, Thomas Munro
<thomas(dot)munro(at)enterprisedb(dot)com> wrote:
> On Tue, May 9, 2017 at 10:29 PM, Thomas Munro <thomas(dot)munro(at)enterprisedb(dot)com> 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.

No, they see all rows from the statement, each time.

test=# create table t (c int not null);
CREATE TABLE
test=# create function t_func()
test-# returns trigger
test-# language plpgsql
test-# as $$
test$# begin
test$# raise notice '% / % = %',
test$# new.c,
test$# (select sum(c) from n),
test$# (select new.c::float / sum(n.c) from n);
test$# return null;
test$# end;
test$# $$;
CREATE FUNCTION
test=# create trigger t_trig
test-# after insert or update on t
test-# referencing new table as n
test-# for each row
test-# execute procedure t_func();
CREATE TRIGGER
test=# insert into t select generate_series(1,5);
NOTICE: 1 / 15 = 0.0666666666666667
NOTICE: 2 / 15 = 0.133333333333333
NOTICE: 3 / 15 = 0.2
NOTICE: 4 / 15 = 0.266666666666667
NOTICE: 5 / 15 = 0.333333333333333
INSERT 0 5

This behavior is required for this feature by the SQL standard.

> Now our policy of firing the statement level triggers only for
> the named relation but firing row-level triggers for all modified
> relations leads to a tricky problem for the inheritance case: what
> type of transition tuples should the child table's row-level triggers
> see?

The record format for the object on which the trigger was declared, IMO.

> Suppose you have an inheritance hierarchy like this:
>
> animal
> -> mammal
> -> cat
>
> You define a statement-level trigger on "animal" and another
> statement-level trigger on "mammal". You define a row-level trigger
> on "cat". When you update either "animal" or "mammal", the row
> triggers on "cat" might run. Row-level triggers on "cat" see OLD and
> NEW as "cat" tuples, of course, but if they are configured to see
> transition tables, should they see "cat", "mammal" or "animal" tuples
> in the transition tables? With my patch as it is, that depends on
> which level of the hierarchy you explicitly updated!

I think that the ideal behavior would be that if you define a
trigger on "cat", you see rows in the "cat" format; if you define a
trigger on rows for "mammal", you see rows in the "mammal" format;
if you define a trigger on rows for "animal", you see rows in the
"animal" format. Also, the ideal would be that we support an ONLY
option for trigger declaration. If your statement is ONLY on the a
given level in the hierarchy, the row triggers for only that level
would fire. If you don't use ONLY, a row trigger at that level
would fire for operations at that level or any child level, but with
a record format matching the level of the trigger.

Now, that may be too ambitious for this release. If so, I suggest
we not implement anything that would be broken by the above, and
throw a "not implemented" error when necessary.

--
Kevin Grittner
VMware vCenter Server
https://www.vmware.com/