Date: Fri, 16 Jul 2021 14:33:19 -0500
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>
Subject: Re: CREATE TABLE .. PARTITION OF fails to preserve tgenabled for
 inherited row triggers
Message-ID: <20210716193319.GS20208@telsasoft.com>
References: <20201027174318.GM9241@telsasoft.com>
 <202107141802.5wzzuwqwodsx@alvherre.pgsql>
 <20210716181012.GR20208@telsasoft.com>
 <3746987.1626459326@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <3746987.1626459326@sss.pgh.pa.us>
User-Agent: Mutt/1.9.4 (2018-02-28)
Archived-At: 
 <https://www.postgresql.org/message-id/20210716193319.GS20208%40telsasoft.com>
Precedence: bulk

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