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: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-hackers@postgresql.org, Amit Langote <amitlangote09@gmail.com>
Date: 2021-07-17T01:02:59Z
Lists: pgsql-hackers 
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

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)