public inbox for [email protected]  

help / color / mirror / Atom feed

From: Justin Pryzby <[email protected]>
To: Alvaro Herrera <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: [email protected]
Cc: Amit Langote <[email protected]>
Subject: \d with triggers: more than one row returned by a subquery used as an expression
Date: Fri, 17 Dec 2021 09:43:56 -0600
Message-ID: <[email protected]> (raw)

I want to mention that the 2nd problem I mentioned here is still broken.
https://www.postgresql.org/message-id/[email protected]

It happens if non-inheritted triggers on child and parent have the same name.

On Fri, Jul 16, 2021 at 08:02:59PM -0500, Justin Pryzby wrote:
> 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/[email protected]...
> 
> .. 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 

view thread (2+ messages)  latest in thread

reply

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: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: \d with triggers: more than one row returned by a subquery used as an expression
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox