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/20210717010259.gu20...@telsasoft.com > > 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.
>From 1c7282f5c06197e178bd23c50a226a148cb66bc8 Mon Sep 17 00:00:00 2001 From: Justin Pryzby <pryz...@telsasoft.com> Date: Fri, 16 Jul 2021 19:57:00 -0500 Subject: [PATCH 1/2] psql: fix \d for statement triggers with same name on partition and its parent This depends on pg_partition_ancestors() to return its partitions in order: this partition => parent => ... => root. 20210716193319.gs20...@telsasoft.com --- src/bin/psql/describe.c | 2 +- src/test/regress/expected/triggers.out | 13 +++++++++++++ src/test/regress/sql/triggers.sql | 4 ++++ 3 files changed, 18 insertions(+), 1 deletion(-) diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index 42bad9281ed..7ad10fa1a3f 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -3012,7 +3012,7 @@ describeOneTableDetails(const char *schemaname, " FROM pg_catalog.pg_trigger AS u, " " pg_catalog.pg_partition_ancestors(t.tgrelid) AS a" " WHERE u.tgname = t.tgname AND u.tgrelid = a.relid" - " AND u.tgparentid = 0) AS parent" : + " AND u.tgparentid = 0 LIMIT 1) AS parent" : "NULL AS parent"), oid); if (pset.sversion >= 110000) diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out index 9d529e949f4..44c8cdf206f 100644 --- a/src/test/regress/expected/triggers.out +++ b/src/test/regress/expected/triggers.out @@ -2122,6 +2122,19 @@ Triggers: alter table trigpart attach partition trigpart3 FOR VALUES FROM (2000) to (3000); -- fail ERROR: trigger "trg1" for relation "trigpart3" already exists drop table trigpart3; +create trigger samename after delete on trigpart execute function trigger_nothing(); +create trigger samename after delete on trigpart1 execute function trigger_nothing(); +\d trigpart1 + Table "public.trigpart1" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + a | integer | | | + b | integer | | | +Partition of: trigpart FOR VALUES FROM (0) TO (1000) +Triggers: + samename AFTER DELETE ON trigpart1 FOR EACH STATEMENT EXECUTE FUNCTION trigger_nothing() + trg1 AFTER INSERT ON trigpart1 FOR EACH ROW EXECUTE FUNCTION trigger_nothing(), ON TABLE trigpart + drop table trigpart; drop function trigger_nothing(); -- diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql index eaab642950b..f644a64ca7d 100644 --- a/src/test/regress/sql/triggers.sql +++ b/src/test/regress/sql/triggers.sql @@ -1428,6 +1428,10 @@ create trigger trg1 after insert on trigpart3 for each row execute procedure tri alter table trigpart attach partition trigpart3 FOR VALUES FROM (2000) to (3000); -- fail drop table trigpart3; +create trigger samename after delete on trigpart execute function trigger_nothing(); +create trigger samename after delete on trigpart1 execute function trigger_nothing(); +\d trigpart1 + drop table trigpart; drop function trigger_nothing(); -- 2.17.0
>From 543badde5a9bc536d5072cdafd4947a46bfb3efa Mon Sep 17 00:00:00 2001 From: Justin Pryzby <pryz...@telsasoft.com> Date: Fri, 3 Apr 2020 22:43:26 -0500 Subject: [PATCH 2/2] psql: Add newlines and spacing in trigger query for \d cosmetic change to c33869cc3 to make the output of psql -E look pretty. --- src/bin/psql/describe.c | 12 ++++++------ 1 file changed, 6 insertions(+), 6 deletions(-) diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index 7ad10fa1a3f..a82c945a9a2 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -3007,12 +3007,12 @@ describeOneTableDetails(const char *schemaname, "t.tgenabled, t.tgisinternal, %s\n" "FROM pg_catalog.pg_trigger t\n" "WHERE t.tgrelid = '%s' AND ", - (pset.sversion >= 130000 ? - "(SELECT (NULLIF(a.relid, t.tgrelid))::pg_catalog.regclass" - " FROM pg_catalog.pg_trigger AS u, " - " pg_catalog.pg_partition_ancestors(t.tgrelid) AS a" - " WHERE u.tgname = t.tgname AND u.tgrelid = a.relid" - " AND u.tgparentid = 0 LIMIT 1) AS parent" : + (pset.sversion >= 130000 ? "\n" + " (SELECT (NULLIF(a.relid, t.tgrelid))::pg_catalog.regclass\n" + " FROM pg_catalog.pg_trigger AS u,\n" + " pg_catalog.pg_partition_ancestors(t.tgrelid) AS a\n" + " WHERE u.tgname = t.tgname AND u.tgrelid = a.relid\n" + " AND u.tgparentid = 0 LIMIT 1) AS parent" : "NULL AS parent"), oid); if (pset.sversion >= 110000) -- 2.17.0
