Date: Wed, 22 Dec 2021 15:03:08 -0600
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>
Subject: Re: \d with triggers: more than one row returned by a subquery used
 as an expression
Message-ID: <20211222210308.GD17618@telsasoft.com>
References: <20211217154356.GJ17618@telsasoft.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="fuRgIt480xB0EInP"
Content-Disposition: inline
In-Reply-To: <20211217154356.GJ17618@telsasoft.com>
User-Agent: Mutt/1.9.4 (2018-02-28)
Archived-At: 
 <https://www.postgresql.org/message-id/20211222210308.GD17618%40telsasoft.com>
Precedence: bulk


--fuRgIt480xB0EInP
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

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.GU20208@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.


--fuRgIt480xB0EInP
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
 filename="0001-psql-fix-d-for-statement-triggers-with-same-name-on-.patch"

From 1c7282f5c06197e178bd23c50a226a148cb66bc8 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryzbyj@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.GS20208@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


--fuRgIt480xB0EInP
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
 filename="0002-psql-Add-newlines-and-spacing-in-trigger-query-for-d.patch"

From 543badde5a9bc536d5072cdafd4947a46bfb3efa Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryzbyj@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


--fuRgIt480xB0EInP--