On Mon, May 1, 2017 at 12:51 AM, Amit Langote <langote_amit...@lab.ntt.co.jp> wrote: > What we could document now is that partitioned tables don't allow > specifying triggers that reference transition tables. Although, I am > wondering where this note really belongs - the partitioning chapter, the > triggers chapter or the CREATE TRIGGER reference page? Maybe, Kevin and > Thomas have something to say about that. If it turns out that the > partitioning chapter is a good place, here is a documentation patch.
I think that before we document this behavior, we'd better make sure we understand exactly what the behavior is, and we'd better make sure it's correct. Currently, triggers that involve transition tables are altogether prohibited when the root relation is partitioned, but are allowed in inheritance cases. However, the actual behavior appears to be buggy. Here's what happens when I define a parent and a child and update all the rows: rhaas=# CREATE FUNCTION t() RETURNS trigger rhaas-# LANGUAGE plpgsql rhaas-# AS $$declare q record; begin raise notice 'table %', tg_table_name; for q in select * from old loop raise notice 'table % got value %', tg_table_name, q.a; end loop; return null; end;$$; CREATE FUNCTION rhaas=# CREATE TABLE p (a int, b text); CREATE TABLE rhaas=# CREATE TABLE p1 () INHERITS (p); CREATE TABLE rhaas=# CREATE TRIGGER x AFTER UPDATE ON p REFERENCING OLD TABLE AS old NEW TABLE AS new FOR EACH STATEMENT EXECUTE PROCEDURE t(); CREATE TRIGGER rhaas=# INSERT INTO p VALUES (0, 'zero'); INSERT 0 1 rhaas=# INSERT INTO p1 VALUES (1, 'one'); INSERT 0 1 rhaas=# INSERT INTO p1 VALUES (2, 'two'); INSERT 0 1 rhaas=# UPDATE p SET b = 'whatever'; NOTICE: table p NOTICE: table p got value 0 UPDATE 3 Only the rows in the parent show up in the transition table. But now look what happens if I add an unrelated trigger that also uses transition tables to the children: rhaas=# CREATE FUNCTION u() RETURNS trigger LANGUAGE plpgsql AS $$begin null; end$$; CREATE FUNCTION rhaas=# CREATE TRIGGER x1 AFTER UPDATE ON p1 REFERENCING OLD TABLE AS old NEW TABLE AS new FOR EACH STATEMENT EXECUTE PROCEDURE u(); CREATE TRIGGER rhaas=# UPDATE p SET b = 'whatever'; NOTICE: table p NOTICE: table p got value 0 NOTICE: table p got value 1 NOTICE: table p got value 2 UPDATE 3 It seems pretty clear to me that this is busted. The existence of trigger x1 on p1 shouldn't affect whether trigger x on p sees changes to p1's rows in its transition tables. Either all changes to any descendants of p should be captured by the transition tables, or only changes to the root table should be captured. If we do the former, the restriction against using transition tables in triggers on partitioned tables should be removed, I would think. If we do the latter, then what we should document is not that partitioned tables have a restriction that doesn't apply to inheritance but rather that the restriction on the partitioned case flows from the fact that only the parent's changes are captured, and the parent is always empty in the partitioning case. In deciding between these two cases, we should consider the case where the inheritance children have extra columns and/or different column orderings. Adding this as an open item. Kevin? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
