[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.mu...@enterprisedb.com> wrote: > On Tue, May 9, 2017 at 10:29 PM, Thomas Munro <thomas.mu...@enterprisedb.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/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
