From: Justin Pryzby <pryzby@telsasoft.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: pgsql-hackers@postgresql.org, Amit Langote <amitlangote09@gmail.com>
Subject: Re: CREATE TABLE .. PARTITION OF fails to preserve tgenabled for inherited row triggers
Date: Fri, 16 Jul 2021 14:33:19 -0500
Message-ID: <20210716193319.GS20208@telsasoft.com> (raw)
In-Reply-To: <3746987.1626459326@sss.pgh.pa.us>
References: <20201027174318.GM9241@telsasoft.com>
<202107141802.5wzzuwqwodsx@alvherre.pgsql>
<20210716181012.GR20208@telsasoft.com>
<3746987.1626459326@sss.pgh.pa.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: pryzby@telsasoft.com, tgl@sss.pgh.pa.us, alvherre@alvh.no-ip.org, amitlangote09@gmail.com
Subject: Re: CREATE TABLE .. PARTITION OF fails to preserve tgenabled for inherited row triggers
In-Reply-To: <20210716193319.GS20208@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox