From: | Justin Pryzby <pryzby(at)telsasoft(dot)com> |
---|---|
To: | Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-hackers(at)postgresql(dot)org, Amit Langote <amitlangote09(at)gmail(dot)com> |
Subject: | Re: CREATE TABLE .. PARTITION OF fails to preserve tgenabled for inherited row triggers |
Date: | 2021-07-17 01:02:59 |
Message-ID: | 20210717010259.GU20208@telsasoft.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
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
From | Date | Subject | |
---|---|---|---|
Next Message | David Rowley | 2021-07-17 02:36:09 | Re: Add proper planner support for ORDER BY / DISTINCT aggregates |
Previous Message | John Naylor | 2021-07-17 00:02:33 | Re: speed up verifying UTF-8 |