Date: Fri, 16 Jul 2021 20:02:59 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, 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: <20210717010259.GU20208@telsasoft.com>
References: <20210716193319.GS20208@telsasoft.com>
 <202107162201.g243c7mnnxuy@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <202107162201.g243c7mnnxuy@alvherre.pgsql>
User-Agent: Mutt/1.9.4 (2018-02-28)
Archived-At: 
 <https://www.postgresql.org/message-id/20210717010259.GU20208%40telsasoft.com>
Precedence: bulk

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.com

.. 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