On 19/05/2026 09:08, Peter Smith wrote:
> Thanks for reviewing and testing my patch. PSA v2 with that missing \n
> restored.
LGTM.
In the same light, we might also want to take a look at \d+. Currently
it can display the publication twice:
postgres=# \d+ s.t2
Table "s.t2"
Column | Type | Collation | Nullable | Default | Storage |
Compression | Stats target | Description
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
c | integer | | | | plain |
| |
Included in publications:
"pub1" WHERE (c > 42)
"pub1"
Access method: heap
Adding a similar logic to describeOneTableDetails might do the trick:
" AND NOT EXISTS (\n"
" SELECT 1\n"
" FROM pg_catalog.pg_publication_namespace pn\n"
" WHERE pn.pnpubid = p.oid\n"
" AND pn.pnnspid = c.relnamespace)\n",
==============
Example:
postgres=# CREATE SCHEMA s;
CREATE TABLE public.t1(c int);
CREATE TABLE s.t2(c int);
CREATE TABLE s.t3(c int);
CREATE TABLE s.t4(c int);
CREATE PUBLICATION pub1 FOR
TABLES IN SCHEMA s,
TABLE s.t3, s.t4,
s.t2 WHERE (c > 42),
public.t1;
postgres=# \d+ s.t2
Table "s.t2"
Column | Type | Collation | Nullable | Default | Storage |
Compression | Stats target | Description
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
c | integer | | | | plain |
| |
Included in publications:
"pub1"
Access method: heap
postgres=# CREATE PUBLICATION pub2 FOR TABLE s.t2;
CREATE PUBLICATION
postgres=# \d+ s.t2
Table "s.t2"
Column | Type | Collation | Nullable | Default | Storage |
Compression | Stats target | Description
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
c | integer | | | | plain |
| |
Included in publications:
"pub1"
"pub2"
Access method: heap
postgres=# \d+ s.t3
Table "s.t3"
Column | Type | Collation | Nullable | Default | Storage |
Compression | Stats target | Description
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
c | integer | | | | plain |
| |
Included in publications:
"pub1"
Access method: heap
postgres=# \d public.t1
Table "public.t1"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
c | integer | | |
Included in publications:
"pub1"
What do you think?
PSA a POC in v3-0002.
Best, Jim
From 82d3a425d7dda6c11cfbfcb8b3dadd57cc6d2af9 Mon Sep 17 00:00:00 2001
From: Peter Smith <[email protected]>
Date: Tue, 19 May 2026 17:04:49 +1000
Subject: [PATCH v3 1/2] Fix psql publication describe for tables in schema
---
src/bin/psql/describe.c | 7 ++++++-
src/test/regress/expected/publication.out | 6 ------
2 files changed, 6 insertions(+), 7 deletions(-)
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index e1449654f96..f5551896598 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -7015,7 +7015,12 @@ describePublications(const char *pattern)
" pg_catalog.pg_publication_rel pr\n"
"WHERE c.relnamespace = n.oid\n"
" AND c.oid = pr.prrelid\n"
- " AND pr.prpubid = '%s'\n", pubid);
+ " AND pr.prpubid = '%s'\n"
+ " AND n.oid NOT IN (\n"
+ " SELECT pn.pnnspid\n"
+ " FROM pg_catalog.pg_publication_namespace pn\n"
+ " WHERE pn.pnpubid = '%s'\n)",
+ pubid, pubid);
if (pset.sversion >= 190000)
appendPQExpBufferStr(&buf, " AND NOT pr.prexcept\n");
diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out
index 29e54b214a0..b29b6b53119 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -146,8 +146,6 @@ RESET client_min_messages;
Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description
--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+-------------
regress_publication_user | f | f | t | t | t | t | none | f |
-Tables:
- "pub_test.testpub_nopk"
Tables from schemas:
"pub_test"
@@ -167,8 +165,6 @@ ALTER PUBLICATION testpub_forschema ADD TABLE pub_test.testpub_nopk;
Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description
--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+-------------
regress_publication_user | f | f | t | t | t | t | none | f |
-Tables:
- "pub_test.testpub_nopk"
Tables from schemas:
"pub_test"
@@ -832,8 +828,6 @@ RESET client_min_messages;
Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description
--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+-------------
regress_publication_user | f | f | t | t | t | t | none | f |
-Tables:
- "testpub_rf_schema2.testpub_rf_tbl6" WHERE (i < 99)
Tables from schemas:
"testpub_rf_schema2"
--
2.54.0
From d1536e616ce07c27cfb32a09eac46bae600f1d68 Mon Sep 17 00:00:00 2001
From: Jim Jones <[email protected]>
Date: Tue, 19 May 2026 14:00:48 +0200
Subject: [PATCH v3 2/2] Fix publication duplication in table decription
---
src/bin/psql/describe.c | 7 ++++++-
1 file changed, 6 insertions(+), 1 deletion(-)
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index f5551896598..7baf65ab664 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -3199,7 +3199,12 @@ describeOneTableDetails(const char *schemaname,
"FROM pg_catalog.pg_publication p\n"
" JOIN pg_catalog.pg_publication_rel pr ON p.oid = pr.prpubid\n"
" JOIN pg_catalog.pg_class c ON c.oid = pr.prrelid\n"
- "WHERE pr.prrelid = '%s'\n",
+ "WHERE pr.prrelid = '%s'\n"
+ " AND NOT EXISTS (\n"
+ " SELECT 1\n"
+ " FROM pg_catalog.pg_publication_namespace pn\n"
+ " WHERE pn.pnpubid = p.oid\n"
+ " AND pn.pnnspid = c.relnamespace)\n",
oid, oid, oid);
if (pset.sversion >= 190000)
--
2.54.0
