Re: [HACKERS] transition table behavior with inheritance appearsbroken (was: Declarative partitioning - another take) - Mailing list pgsql-hackers

On Mon, May 1, 2017 at 12:51 AM, Amit Langote
<Langote_Amit_f8@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