From: Justin Pryzby <pryzby@telsasoft.com>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
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 20:02:59 -0500
Message-ID: <20210717010259.GU20208@telsasoft.com> (raw)
In-Reply-To: <202107162201.g243c7mnnxuy@alvherre.pgsql>
References: <20210716193319.GS20208@telsasoft.com>
<202107162201.g243c7mnnxuy@alvherre.pgsql>
On Fri, Jul 16, 2021 at 06:01:12PM -0400, Alvaro Herrera wrote:
> On 2021-Jul-16, Justin Pryzby wrote:
> > 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();
>
> Hmm, interesting -- those statement triggers are not cloned, so what is
> going on here is just that the psql query to show them is tripping on
> its shoelaces ... I'll try to find a fix.
>
> I *think* the problem is that the query matches triggers by name and
> parent/child relationship; we're missing to ignore triggers by tgtype.
> It's not great design that tgtype is a bitmask of unrelated flags ...
I see it's the subquery Amit wrote and proposed here:
https://www.postgresql.org/message-id/CA+HiwqEiMe0tCOoPOwjQrdH5fxnZccMR7oeW=f9FmgszJQbgFg@mail.gmail...
.. and I realize that I've accidentally succeeded in breaking what I first
attempted to break 15 months ago:
On Mon, Apr 20, 2020 at 02:57:40PM -0500, Justin Pryzby wrote:
> I'm happy to see that this doesn't require a recursive cte, at least.
> I was trying to think how to break it by returning multiple results or results
> out of order, but I think that can't happen.
If you assume that pg_partition_ancestors returns its results in order, I think
you can fix it by adding LIMIT 1. Otherwise I think you need a recursive CTE,
as I'd feared.
Note also that I'd sent a patch to add newlines, to make psql -E look pretty.
v6-0001-fixups-c33869cc3bfc42bce822251f2fa1a2a346f86cc5.patch
--
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, alvherre@alvh.no-ip.org, tgl@sss.pgh.pa.us, amitlangote09@gmail.com
Subject: Re: CREATE TABLE .. PARTITION OF fails to preserve tgenabled for inherited row triggers
In-Reply-To: <20210717010259.GU20208@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