public inbox for [email protected]
help / color / mirror / Atom feed\d with triggers: more than one row returned by a subquery used as an expression
2+ messages / 1 participants
[nested] [flat]
* \d with triggers: more than one row returned by a subquery used as an expression
@ 2021-12-17 15:43 Justin Pryzby <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Justin Pryzby @ 2021-12-17 15:43 UTC (permalink / raw)
To: Alvaro Herrera <[email protected]>; +Cc: Tom Lane <[email protected]>; pgsql-hackers; Amit Langote <[email protected]>
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: \d with triggers: more than one row returned by a subquery used as an expression
@ 2021-12-22 21:03 Justin Pryzby <[email protected]>
parent: Justin Pryzby <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Justin Pryzby @ 2021-12-22 21:03 UTC (permalink / raw)
To: Alvaro Herrera <[email protected]>; +Cc: Tom Lane <[email protected]>; pgsql-hackers; Amit Langote <[email protected]>
On Fri, Dec 17, 2021 at 09:43:56AM -0600, Justin Pryzby wrote:
> 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.
This is the fix I was proposing
It depends on pg_partition_ancestors() to return its partitions in order:
this partition => parent => ... => root.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2021-12-22 21:03 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-12-17 15:43 \d with triggers: more than one row returned by a subquery used as an expression Justin Pryzby <[email protected]>
2021-12-22 21:03 ` Justin Pryzby <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox