Horton

Re: CREATE TABLE .. PARTITION OF fails to preserve tgenabled for inherited row triggers

Justin Pryzby <pryzby@telsasoft.com>

View thread View on postgr.es
From: Justin Pryzby <pryzby@telsasoft.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>, pgsql-hackers@postgresql.org, Amit Langote <amitlangote09@gmail.com>
Date: 2021-07-16T19:33:19Z
Lists: pgsql-hackers 
On Fri, Jul 16, 2021 at 02:15:26PM -0400, Tom Lane wrote:
> Justin Pryzby <pryzby@telsasoft.com> writes:
> > I think there's still an issue that comments on child triggers aren't
> > preserved, right ?
> 
> Do we care?  That seems like messing with a system-internal object.
> In general we won't promise to preserve the results of doing so.

It's fine if that's the conclusion.

Back in October, that seemed like one too many things misbehaving, which led me
to walk away from the patch and re-orient.

I was going re-check the behavior, but instead hit another bug:

CREATE TABLE p(i int) PARTITION BY RANGE(i);
CREATE TABLE p1 PARTITION OF p FOR VALUES FROM (1)TO(2);
CREATE FUNCTION foo() returns trigger LANGUAGE plpgsql AS $$begin end$$;
CREATE TRIGGER x AFTER DELETE ON p1 EXECUTE FUNCTION foo();
CREATE TRIGGER x AFTER DELETE ON p EXECUTE FUNCTION foo();
\d p1
2021-07-16 14:30:12.371 CDT client backend[6252] psql ERROR:  more than one row returned by a subquery used as an expression
2021-07-16 14:30:12.371 CDT client backend[6252] psql STATEMENT:  SELECT t.tgname, pg_catalog.pg_get_triggerdef(t.oid, true), t.tgenabled, t.tgisinternal, (SELECT (NULLIF(a.relid, t.tgrelid))::pg_catalog.regclass FROM pg_catalog.pg_trigger AS u,       pg_catalog.pg_partition_ancestors(t.tgrelid) AS a WHERE u.tgname = t.tgname AND u.tgrelid = a.relid       AND u.tgparentid = 0) AS parent
        FROM pg_catalog.pg_trigger t
        WHERE t.tgrelid = '37718' AND (NOT t.tgisinternal OR (t.tgisinternal AND t.tgenabled = 'D') 
            OR EXISTS (SELECT 1 FROM pg_catalog.pg_depend WHERE objid = t.oid 
                AND refclassid = 'pg_catalog.pg_trigger'::pg_catalog.regclass))
        ORDER BY 1;
ERROR:  more than one row returned by a subquery used as an expression

The tgenabled issue was a contributing factor which led us to stop using
inherited triggers, so I'm not very motivated to dig into it.

-- 
Justin

Commits

  1. Fix pg_dump for disabled triggers on partitioned tables

  2. Preserve firing-on state when cloning row triggers to partitions

  3. Fix ALTER TABLE .. ENABLE/DISABLE TRIGGER recursion

  4. psql \d: Display table where trigger is defined, if inherited

  5. Fix cloning of row triggers to sub-partitions

  6. Make pg_dump emit ATTACH PARTITION instead of PARTITION OF (reprise)