public inbox for [email protected]
help / color / mirror / Atom feedPSQL - prevent describe listing tables that are already in listed schemas
11+ messages / 3 participants
[nested] [flat]
* PSQL - prevent describe listing tables that are already in listed schemas
@ 2026-05-18 03:20 Peter Smith <[email protected]>
2026-05-19 06:23 ` Re: PSQL - prevent describe listing tables that are already in listed schemas Jim Jones <[email protected]>
0 siblings, 1 reply; 11+ messages in thread
From: Peter Smith @ 2026-05-18 03:20 UTC (permalink / raw)
To: PostgreSQL Hackers <[email protected]>
I noticed a quirk of the psql publication "describe" command (\dRp+).
Background: It is permitted for a FOR TABLE publication to overlap
with a FOR TABLES IN SCHEMA publication. When a specified table is a
member of a published schema (and there is no column list), then there
is no clash -- it is just silently absorbed by the schema superset.
So, the following is fine:
CREATE SCHEMA myschema;
CREATE TABLE t99(c int);
CREATE TABLE myschema.t1(c int);
CREATE PUBLICATION pub1 FOR TABLES IN SCHEMA myschema, TABLE myschema.t1, t99;
However, I noticed that \dRp+ displays this publication with table
"myschema.t1" still separately listed:
e.g. CURRENT BEHAVIOUR
test_pub=# \dRp+ pub1
Publication pub1
Owner | All tables | All sequences | Inserts | Updates | Deletes |
Truncates | Generated columns | Via root | Descri
ption
----------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+-------
------
postgres | f | f | t | t | t |
t | none | f |
Tables:
"myschema.t1"
"public.t99"
Tables from schemas:
"myschema"
~~~
IMO it would make more sense if a table is *not* displayed separately
when the schema superset is also present.
e.g. OUTPUT AFTER PATCHED
test_pub=# \dRp+ pub1
Publication pub1
Owner | All tables | All sequences | Inserts | Updates | Deletes |
Truncates | Generated columns | Via root | Descri
ption
----------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+-------
------
postgres | f | f | t | t | t |
t | none | f |
Tables:
"public.t99"
Tables from schemas:
"myschema"
======
Here are a couple more reasons why I think this patch is helpful:
1. If the specified table had a row filter, then it is confusing to
display that row filter under "Tables:" when at the same time the DOCS
[2] says "The row filter on a table becomes redundant if FOR TABLES IN
SCHEMA is specified and the table belongs to the referred schema."
2. The output will become still more confusing after the FOR TABLES IN
SCHEMA EXCEPT gets implemented [1]. Because it is not obvious, you
need to read this a couple of times to be sure what is in and out.
e.g. Output with the EXCEPT patch applied:
Tables:
"myschema2.t2"
Tables from schemas:
"myschema2"
Except tables:
"myschema2.t1"
~~~
PSA patch v1. Thoughts?
======
[1] https://www.postgresql.org/message-id/flat/CABdArM5sw4Q1ZU8HGdo4BSc1A_%2B8xtUNq17j6wcir%3DyMUy19Cg%4...
[2] https://www.postgresql.org/docs/devel/sql-createpublication.html
Kind Regards,
Peter Smith.
Fujitsu Australia
Attachments:
[application/octet-stream] v1-0001-Fix-psql-publication-describe-for-tables-in-schem.patch (3.0K, 2-v1-0001-Fix-psql-publication-describe-for-tables-in-schem.patch)
download | inline diff:
From f28e8c3a5d1ed5e7ab17d978293d19ab35dac810 Mon Sep 17 00:00:00 2001
From: Peter Smith <[email protected]>
Date: Mon, 18 May 2026 12:58:58 +1000
Subject: [PATCH v1] 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..bec2ce057f9 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')",
+ 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.47.3
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: PSQL - prevent describe listing tables that are already in listed schemas
2026-05-18 03:20 PSQL - prevent describe listing tables that are already in listed schemas Peter Smith <[email protected]>
@ 2026-05-19 06:23 ` Jim Jones <[email protected]>
2026-05-19 07:08 ` Re: PSQL - prevent describe listing tables that are already in listed schemas Peter Smith <[email protected]>
0 siblings, 1 reply; 11+ messages in thread
From: Jim Jones @ 2026-05-19 06:23 UTC (permalink / raw)
To: Peter Smith <[email protected]>; PostgreSQL Hackers <[email protected]>
Hi Peter
On 18/05/2026 05:20, Peter Smith wrote:
> Here are a couple more reasons why I think this patch is helpful:
>
> 1. If the specified table had a row filter, then it is confusing to
> display that row filter under "Tables:" when at the same time the DOCS
> [2] says "The row filter on a table becomes redundant if FOR TABLES IN
> SCHEMA is specified and the table belongs to the referred schema."
>
> 2. The output will become still more confusing after the FOR TABLES IN
> SCHEMA EXCEPT gets implemented [1]. Because it is not obvious, you
> need to read this a couple of times to be sure what is in and out.
> e.g. Output with the EXCEPT patch applied:
> Tables:
> "myschema2.t2"
> Tables from schemas:
> "myschema2"
> Except tables:
> "myschema2.t1"
>
> ~~~
>
> PSA patch v1. Thoughts?
+1
I agree that the proposed paatch makes it easier to read the listed
tables and better aligns with the docs.
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;
With this patch I get:
postgres=# \dRp+ pub1
Publication pub1
Owner | All tables | All sequences | Inserts | Updates | Deletes |
Truncates | Generated columns | Via root | Description
-------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+-------------
jim | f | f | t | t | t | t
| none | f |
Tables:
"public.t1"
Tables from schemas:
"s"
Without it:
postgres=# \dRp+ pub1
Publication pub1
Owner | All tables | All sequences | Inserts | Updates | Deletes |
Truncates | Generated columns | Via root | Description
-------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+-------------
jim | f | f | t | t | t | t
| none | f |
Tables:
"public.t1"
"s.t2" WHERE (c > 42)
"s.t3"
"s.t4"
Tables from schemas:
"s"
One nitpick: you forgot a \n at the end of "WHERE pn.pnpubid = '%s')".
Best, Jim
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: PSQL - prevent describe listing tables that are already in listed schemas
2026-05-18 03:20 PSQL - prevent describe listing tables that are already in listed schemas Peter Smith <[email protected]>
2026-05-19 06:23 ` Re: PSQL - prevent describe listing tables that are already in listed schemas Jim Jones <[email protected]>
@ 2026-05-19 07:08 ` Peter Smith <[email protected]>
2026-05-19 12:08 ` Re: PSQL - prevent describe listing tables that are already in listed schemas Jim Jones <[email protected]>
0 siblings, 1 reply; 11+ messages in thread
From: Peter Smith @ 2026-05-19 07:08 UTC (permalink / raw)
To: Jim Jones <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
Hi Jim.
Thanks for reviewing and testing my patch. PSA v2 with that missing \n restored.
======
Kind Regards,
Peter Smith.
Fujitsu Australia
Attachments:
[application/octet-stream] v2-0001-Fix-psql-publication-describe-for-tables-in-schem.patch (3.0K, 2-v2-0001-Fix-psql-publication-describe-for-tables-in-schem.patch)
download | inline diff:
From a5709dc36c90efb585240d1fe6b2d6be3e3319bb Mon Sep 17 00:00:00 2001
From: Peter Smith <[email protected]>
Date: Tue, 19 May 2026 17:04:49 +1000
Subject: [PATCH v2] 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.47.3
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: PSQL - prevent describe listing tables that are already in listed schemas
2026-05-18 03:20 PSQL - prevent describe listing tables that are already in listed schemas Peter Smith <[email protected]>
2026-05-19 06:23 ` Re: PSQL - prevent describe listing tables that are already in listed schemas Jim Jones <[email protected]>
2026-05-19 07:08 ` Re: PSQL - prevent describe listing tables that are already in listed schemas Peter Smith <[email protected]>
@ 2026-05-19 12:08 ` Jim Jones <[email protected]>
2026-05-19 23:59 ` Re: PSQL - prevent describe listing tables that are already in listed schemas Peter Smith <[email protected]>
0 siblings, 1 reply; 11+ messages in thread
From: Jim Jones @ 2026-05-19 12:08 UTC (permalink / raw)
To: Peter Smith <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
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
Attachments:
[text/x-patch] v3-0001-Fix-psql-publication-describe-for-tables-in-schem.patch (3.0K, 2-v3-0001-Fix-psql-publication-describe-for-tables-in-schem.patch)
download | inline diff:
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
[text/x-patch] v3-0002-Fix-publication-duplication-in-table-decription.patch (1.1K, 3-v3-0002-Fix-publication-duplication-in-table-decription.patch)
download | inline diff:
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: PSQL - prevent describe listing tables that are already in listed schemas
2026-05-18 03:20 PSQL - prevent describe listing tables that are already in listed schemas Peter Smith <[email protected]>
2026-05-19 06:23 ` Re: PSQL - prevent describe listing tables that are already in listed schemas Jim Jones <[email protected]>
2026-05-19 07:08 ` Re: PSQL - prevent describe listing tables that are already in listed schemas Peter Smith <[email protected]>
2026-05-19 12:08 ` Re: PSQL - prevent describe listing tables that are already in listed schemas Jim Jones <[email protected]>
@ 2026-05-19 23:59 ` Peter Smith <[email protected]>
2026-05-20 07:03 ` Re: PSQL - prevent describe listing tables that are already in listed schemas Jim Jones <[email protected]>
2026-05-20 07:09 ` Re: PSQL - prevent describe listing tables that are already in listed schemas Nisha Moond <[email protected]>
0 siblings, 2 replies; 11+ messages in thread
From: Peter Smith @ 2026-05-19 23:59 UTC (permalink / raw)
To: Jim Jones <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
On Tue, May 19, 2026 at 10:08 PM Jim Jones <[email protected]> wrote:
>
> 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.
>
I agree we should address that \d+ quirk at the same time.
Thanks for finding/fixing it in v3-0002.
Your 0002 patch works for me, although I'm thinking those SQL fixes
ought to be made more similar:
1. Both use "n.oid NOT IN (SELECT pn.pnnspid ..."
2. Or both use "AND NOT EXISTS (SELECT 1 FROM ... WHERE ...)"
~
PSA v4, where I have combined the patches, and chosen your SQL style
for my previous \dRp+ fix.
Apparently, there was no existing test case to demonstrate that \d+
case you found. Do we need to bother adding one?
======
Kind Regards,
Peter Smith.
Fujitsu Australia
Attachments:
[application/octet-stream] v4-0001-Fix-psql-duplicate-items-for-dRp-and-d.patch (4.3K, 2-v4-0001-Fix-psql-duplicate-items-for-dRp-and-d.patch)
download | inline diff:
From 4e295d50e250d5e955f9d2ebbe25935858b83a28 Mon Sep 17 00:00:00 2001
From: Peter Smith <[email protected]>
Date: Wed, 20 May 2026 09:24:57 +1000
Subject: [PATCH v4] Fix psql duplicate items for \dRp+ and \d+
When a publication pub1 is defined using both "TABLES IN SCHEMA myschema", and
"TABLE myschema.t1", it caused duplicated items in the psql describe output.
This patch simplifies the output and aligns better with the documentation.
Now:
"\dRp+ pub1" shows only Schema "myschema", not also Table "myschema.t1"
"\d+ t1" shows Publication "pub1" only once
Author: Peter Smith <[email protected]>
Co-author: Jim Jones <[email protected]>
Discussion: https://www.postgresql.org/message-id/flat/CAHut%2BPvSOmRrQX%2BVrFYHtFipV9hM%3Dp99FeOwYCzkuU2BOaLu7Q%40mail.gmail.com
---
src/bin/psql/describe.c | 15 +++++++++++++--
src/test/regress/expected/publication.out | 6 ------
2 files changed, 13 insertions(+), 8 deletions(-)
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index e1449654f96..e326daf632f 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)
@@ -7015,7 +7020,13 @@ 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 NOT EXISTS (\n"
+ " SELECT 1\n"
+ " FROM pg_catalog.pg_publication_namespace pn\n"
+ " WHERE pn.pnpubid = pr.prpubid\n"
+ " AND pn.pnnspid = c.relnamespace)\n",
+ 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.47.3
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: PSQL - prevent describe listing tables that are already in listed schemas
2026-05-18 03:20 PSQL - prevent describe listing tables that are already in listed schemas Peter Smith <[email protected]>
2026-05-19 06:23 ` Re: PSQL - prevent describe listing tables that are already in listed schemas Jim Jones <[email protected]>
2026-05-19 07:08 ` Re: PSQL - prevent describe listing tables that are already in listed schemas Peter Smith <[email protected]>
2026-05-19 12:08 ` Re: PSQL - prevent describe listing tables that are already in listed schemas Jim Jones <[email protected]>
2026-05-19 23:59 ` Re: PSQL - prevent describe listing tables that are already in listed schemas Peter Smith <[email protected]>
@ 2026-05-20 07:03 ` Jim Jones <[email protected]>
1 sibling, 0 replies; 11+ messages in thread
From: Jim Jones @ 2026-05-20 07:03 UTC (permalink / raw)
To: Peter Smith <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
On 20/05/2026 01:59, Peter Smith wrote:
> Apparently, there was no existing test case to demonstrate that \d+
> case you found. Do we need to bother adding one?
I think adding a test here would be worthwhile -- publications.sql
already defines the tables and publications, and a test would help catch
regressions caused by future refactoring.
What about this?
diff --git a/src/test/regress/sql/publication.sql
b/src/test/regress/sql/publication.sql
index 041e14a4de6..fac54b02e27 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -83,6 +83,8 @@ CREATE PUBLICATION testpub_forschema FOR TABLES IN
SCHEMA pub_test;
CREATE PUBLICATION testpub_for_tbl_schema FOR TABLES IN SCHEMA
pub_test, TABLE pub_test.testpub_nopk;
RESET client_min_messages;
\dRp+ testpub_for_tbl_schema
+-- table also covered by a published schema should appear only once in
\d output
+\d pub_test.testpub_nopk
-- weird parser corner case
CREATE PUBLICATION testpub_parsertst FOR TABLE pub_test.testpub_nopk,
CURRENT_SCHEMA;
@@ -406,6 +408,9 @@ CREATE PUBLICATION testpub6 FOR TABLES IN SCHEMA
testpub_rf_schema2;
ALTER PUBLICATION testpub6 SET TABLES IN SCHEMA testpub_rf_schema2,
TABLE testpub_rf_schema2.testpub_rf_tbl6 WHERE (i < 99);
RESET client_min_messages;
\dRp+ testpub6
+-- table with a row-filter, also covered by a published schema, should
appear
+-- only once in \d output and without the row filter
+\d testpub_rf_schema2.testpub_rf_tbl6
-- fail - virtual generated column uses user-defined function
-- (Actually, this already fails at CREATE TABLE rather than at CREATE
-- PUBLICATION, but let's keep the test in case the former gets
I previously said the issue was in \d+, but it’s actually in \d. I’ve
updated the commit message accordingly.
db=# \d testpub_rf_schema2.testpub_rf_tbl6
Table "testpub_rf_schema2.testpub_rf_tbl6"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
i | integer | | |
Publications:
"testpub6" WHERE (i < 99)
"testpub6"
PSA v5.
Thanks!
Best, Jim
Attachments:
[text/x-patch] v5-0001-Fix-psql-duplicate-items-for-dRp-and-d.patch (6.8K, 2-v5-0001-Fix-psql-duplicate-items-for-dRp-and-d.patch)
download | inline diff:
From 2454614eee0187104518afc28d62f2571fc82ff9 Mon Sep 17 00:00:00 2001
From: Jim Jones <[email protected]>
Date: Wed, 20 May 2026 08:50:15 +0200
Subject: [PATCH v5] Fix psql duplicate items for \dRp+ and \d
When a publication pub1 is defined using both "TABLES IN SCHEMA myschema", and
"TABLE myschema.t1", it caused duplicated items in the psql describe output.
This patch simplifies the output and aligns better with the documentation.
Now:
"\dRp+ pub1" shows only Schema "myschema", not also Table "myschema.t1"
"\d t1" shows Publication "pub1" only once
Author: Peter Smith <[email protected]>
Co-author: Jim Jones <[email protected]>
Discussion: https://www.postgresql.org/message-id/flat/CAHut%2BPvSOmRrQX%2BVrFYHtFipV9hM%3Dp99FeOwYCzkuU2BOaLu7Q%40mail.gmail.com
---
src/bin/psql/describe.c | 15 ++++++++++--
src/test/regress/expected/publication.out | 29 ++++++++++++++++++-----
src/test/regress/sql/publication.sql | 5 ++++
3 files changed, 41 insertions(+), 8 deletions(-)
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index e1449654f96..e326daf632f 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)
@@ -7015,7 +7020,13 @@ 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 NOT EXISTS (\n"
+ " SELECT 1\n"
+ " FROM pg_catalog.pg_publication_namespace pn\n"
+ " WHERE pn.pnpubid = pr.prpubid\n"
+ " AND pn.pnnspid = c.relnamespace)\n",
+ 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..9c6386d518a 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -146,11 +146,22 @@ 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"
+-- table also covered by a published schema should appear only once in \d output
+\d pub_test.testpub_nopk
+ Table "pub_test.testpub_nopk"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ foo | integer | | |
+ bar | integer | | |
+Included in publications:
+ "testpub_for_tbl_schema"
+ "testpub_foralltables"
+ "testpub_forschema"
+ "testpub_fortable"
+
-- weird parser corner case
CREATE PUBLICATION testpub_parsertst FOR TABLE pub_test.testpub_nopk, CURRENT_SCHEMA;
ERROR: invalid table name
@@ -167,8 +178,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,11 +841,19 @@ 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"
+-- table with a row-filter, also covered by a published schema, should appear
+-- only once in \d output and without the row filter
+\d testpub_rf_schema2.testpub_rf_tbl6
+ Table "testpub_rf_schema2.testpub_rf_tbl6"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ i | integer | | |
+Included in publications:
+ "testpub6"
+
-- fail - virtual generated column uses user-defined function
-- (Actually, this already fails at CREATE TABLE rather than at CREATE
-- PUBLICATION, but let's keep the test in case the former gets
diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql
index 041e14a4de6..fac54b02e27 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -83,6 +83,8 @@ CREATE PUBLICATION testpub_forschema FOR TABLES IN SCHEMA pub_test;
CREATE PUBLICATION testpub_for_tbl_schema FOR TABLES IN SCHEMA pub_test, TABLE pub_test.testpub_nopk;
RESET client_min_messages;
\dRp+ testpub_for_tbl_schema
+-- table also covered by a published schema should appear only once in \d output
+\d pub_test.testpub_nopk
-- weird parser corner case
CREATE PUBLICATION testpub_parsertst FOR TABLE pub_test.testpub_nopk, CURRENT_SCHEMA;
@@ -406,6 +408,9 @@ CREATE PUBLICATION testpub6 FOR TABLES IN SCHEMA testpub_rf_schema2;
ALTER PUBLICATION testpub6 SET TABLES IN SCHEMA testpub_rf_schema2, TABLE testpub_rf_schema2.testpub_rf_tbl6 WHERE (i < 99);
RESET client_min_messages;
\dRp+ testpub6
+-- table with a row-filter, also covered by a published schema, should appear
+-- only once in \d output and without the row filter
+\d testpub_rf_schema2.testpub_rf_tbl6
-- fail - virtual generated column uses user-defined function
-- (Actually, this already fails at CREATE TABLE rather than at CREATE
-- PUBLICATION, but let's keep the test in case the former gets
--
2.54.0
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: PSQL - prevent describe listing tables that are already in listed schemas
2026-05-18 03:20 PSQL - prevent describe listing tables that are already in listed schemas Peter Smith <[email protected]>
2026-05-19 06:23 ` Re: PSQL - prevent describe listing tables that are already in listed schemas Jim Jones <[email protected]>
2026-05-19 07:08 ` Re: PSQL - prevent describe listing tables that are already in listed schemas Peter Smith <[email protected]>
2026-05-19 12:08 ` Re: PSQL - prevent describe listing tables that are already in listed schemas Jim Jones <[email protected]>
2026-05-19 23:59 ` Re: PSQL - prevent describe listing tables that are already in listed schemas Peter Smith <[email protected]>
@ 2026-05-20 07:09 ` Nisha Moond <[email protected]>
2026-05-20 07:30 ` Re: PSQL - prevent describe listing tables that are already in listed schemas Jim Jones <[email protected]>
1 sibling, 1 reply; 11+ messages in thread
From: Nisha Moond @ 2026-05-20 07:09 UTC (permalink / raw)
To: Peter Smith <[email protected]>; +Cc: Jim Jones <[email protected]>; PostgreSQL Hackers <[email protected]>
On Wed, May 20, 2026 at 5:30 AM Peter Smith <[email protected]> wrote:
>
> I agree we should address that \d+ quirk at the same time.
>
> Thanks for finding/fixing it in v3-0002.
>
> Your 0002 patch works for me, although I'm thinking those SQL fixes
> ought to be made more similar:
> 1. Both use "n.oid NOT IN (SELECT pn.pnnspid ..."
> 2. Or both use "AND NOT EXISTS (SELECT 1 FROM ... WHERE ...)"
>
> ~
>
> PSA v4, where I have combined the patches, and chosen your SQL style
> for my previous \dRp+ fix.
>
+1 for the idea.
For the describePublications (\dRp+) case, should we add an (sversion
>= 150000) guard around the new change, since it accesses
pg_publication_namespace, which is only available in PG15 and above?
Thoughts?
--
Thanks,
Nisha
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: PSQL - prevent describe listing tables that are already in listed schemas
2026-05-18 03:20 PSQL - prevent describe listing tables that are already in listed schemas Peter Smith <[email protected]>
2026-05-19 06:23 ` Re: PSQL - prevent describe listing tables that are already in listed schemas Jim Jones <[email protected]>
2026-05-19 07:08 ` Re: PSQL - prevent describe listing tables that are already in listed schemas Peter Smith <[email protected]>
2026-05-19 12:08 ` Re: PSQL - prevent describe listing tables that are already in listed schemas Jim Jones <[email protected]>
2026-05-19 23:59 ` Re: PSQL - prevent describe listing tables that are already in listed schemas Peter Smith <[email protected]>
2026-05-20 07:09 ` Re: PSQL - prevent describe listing tables that are already in listed schemas Nisha Moond <[email protected]>
@ 2026-05-20 07:30 ` Jim Jones <[email protected]>
2026-05-20 08:27 ` Re: PSQL - prevent describe listing tables that are already in listed schemas Nisha Moond <[email protected]>
0 siblings, 1 reply; 11+ messages in thread
From: Jim Jones @ 2026-05-20 07:30 UTC (permalink / raw)
To: Nisha Moond <[email protected]>; Peter Smith <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
Hi Nisha
On 20/05/2026 09:09, Nisha Moond wrote:
> For the describePublications (\dRp+) case, should we add an (sversion
>> = 150000) guard around the new change, since it accesses
> pg_publication_namespace, which is only available in PG15 and above?
> Thoughts?
I considered that in my first review, but since PG14 will be EOL'd by
the time PG20 is shipped, I thought it wouldn't be necessary.
Thanks!
Best, Jim
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: PSQL - prevent describe listing tables that are already in listed schemas
2026-05-18 03:20 PSQL - prevent describe listing tables that are already in listed schemas Peter Smith <[email protected]>
2026-05-19 06:23 ` Re: PSQL - prevent describe listing tables that are already in listed schemas Jim Jones <[email protected]>
2026-05-19 07:08 ` Re: PSQL - prevent describe listing tables that are already in listed schemas Peter Smith <[email protected]>
2026-05-19 12:08 ` Re: PSQL - prevent describe listing tables that are already in listed schemas Jim Jones <[email protected]>
2026-05-19 23:59 ` Re: PSQL - prevent describe listing tables that are already in listed schemas Peter Smith <[email protected]>
2026-05-20 07:09 ` Re: PSQL - prevent describe listing tables that are already in listed schemas Nisha Moond <[email protected]>
2026-05-20 07:30 ` Re: PSQL - prevent describe listing tables that are already in listed schemas Jim Jones <[email protected]>
@ 2026-05-20 08:27 ` Nisha Moond <[email protected]>
2026-05-20 23:34 ` Re: PSQL - prevent describe listing tables that are already in listed schemas Peter Smith <[email protected]>
0 siblings, 1 reply; 11+ messages in thread
From: Nisha Moond @ 2026-05-20 08:27 UTC (permalink / raw)
To: Jim Jones <[email protected]>; +Cc: Peter Smith <[email protected]>; PostgreSQL Hackers <[email protected]>
On Wed, May 20, 2026 at 1:00 PM Jim Jones <[email protected]> wrote:
>
> Hi Nisha
>
> On 20/05/2026 09:09, Nisha Moond wrote:
> > For the describePublications (\dRp+) case, should we add an (sversion
> >> = 150000) guard around the new change, since it accesses
> > pg_publication_namespace, which is only available in PG15 and above?
> > Thoughts?
>
>
> I considered that in my first review, but since PG14 will be EOL'd by
> the time PG20 is shipped, I thought it wouldn't be necessary.
>
Okay, that makes sense.
--
Thanks,
Nisha
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: PSQL - prevent describe listing tables that are already in listed schemas
2026-05-18 03:20 PSQL - prevent describe listing tables that are already in listed schemas Peter Smith <[email protected]>
2026-05-19 06:23 ` Re: PSQL - prevent describe listing tables that are already in listed schemas Jim Jones <[email protected]>
2026-05-19 07:08 ` Re: PSQL - prevent describe listing tables that are already in listed schemas Peter Smith <[email protected]>
2026-05-19 12:08 ` Re: PSQL - prevent describe listing tables that are already in listed schemas Jim Jones <[email protected]>
2026-05-19 23:59 ` Re: PSQL - prevent describe listing tables that are already in listed schemas Peter Smith <[email protected]>
2026-05-20 07:09 ` Re: PSQL - prevent describe listing tables that are already in listed schemas Nisha Moond <[email protected]>
2026-05-20 07:30 ` Re: PSQL - prevent describe listing tables that are already in listed schemas Jim Jones <[email protected]>
2026-05-20 08:27 ` Re: PSQL - prevent describe listing tables that are already in listed schemas Nisha Moond <[email protected]>
@ 2026-05-20 23:34 ` Peter Smith <[email protected]>
2026-05-21 05:53 ` Re: PSQL - prevent describe listing tables that are already in listed schemas Jim Jones <[email protected]>
0 siblings, 1 reply; 11+ messages in thread
From: Peter Smith @ 2026-05-20 23:34 UTC (permalink / raw)
To: Nisha Moond <[email protected]>; +Cc: Jim Jones <[email protected]>; PostgreSQL Hackers <[email protected]>
On Wed, May 20, 2026 at 6:28 PM Nisha Moond <[email protected]> wrote:
>
> On Wed, May 20, 2026 at 1:00 PM Jim Jones <[email protected]> wrote:
> >
> > Hi Nisha
> >
> > On 20/05/2026 09:09, Nisha Moond wrote:
> > > For the describePublications (\dRp+) case, should we add an (sversion
> > >> = 150000) guard around the new change, since it accesses
> > > pg_publication_namespace, which is only available in PG15 and above?
> > > Thoughts?
> >
> >
> > I considered that in my first review, but since PG14 will be EOL'd by
> > the time PG20 is shipped, I thought it wouldn't be necessary.
> >
>
> Okay, that makes sense.
>
The new tests in patch v5 look good to me.
I think Nisha was correct about adding a PG15 version check. It was an
accidental omission in my first patch. Even if PG14 is EOL-ed by the
time this change is released, it is trivial to keep the psql \dRp+
command behaving as-is rather than crashing due to an internal SQL
error. So, I prefer to err on the side of caution and add the version
check for now; a committer can remove it if they deem it unnecessary.
Added Nisha as a reviewer in the commit message.
PSA v6.
======
Kind Regards,
Peter Smith.
Fujitsu Australia
Attachments:
[application/octet-stream] v6-0001-Fix-psql-duplicate-items-for-dRp-and-d.patch (6.9K, 2-v6-0001-Fix-psql-duplicate-items-for-dRp-and-d.patch)
download | inline diff:
From 22ba8636fd8c30ab44911941ed9df5845eb995d3 Mon Sep 17 00:00:00 2001
From: Peter Smith <[email protected]>
Date: Thu, 21 May 2026 09:30:35 +1000
Subject: [PATCH v6] Fix psql duplicate items for \dRp+ and \d
When a publication pub1 is defined using both "TABLES IN SCHEMA myschema", and
"TABLE myschema.t1", it caused duplicated items in the psql describe output.
This patch simplifies the output and aligns better with the documentation.
Now:
"\dRp+ pub1" shows only Schema "myschema", not also Table "myschema.t1"
"\d t1" shows Publication "pub1" only once
Author: Peter Smith <[email protected]>
Co-author: Jim Jones <[email protected]>
Reviewed-by: Nisha Moond <[email protected]>
Discussion: https://www.postgresql.org/message-id/flat/CAHut%2BPvSOmRrQX%2BVrFYHtFipV9hM%3Dp99FeOwYCzkuU2BOaLu7Q%40mail.gmail.com
---
src/bin/psql/describe.c | 20 +++++++++++++++-
src/test/regress/expected/publication.out | 29 ++++++++++++++++++-----
src/test/regress/sql/publication.sql | 5 ++++
3 files changed, 47 insertions(+), 7 deletions(-)
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index e1449654f96..fe7d8fe7019 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)
@@ -7016,6 +7021,19 @@ describePublications(const char *pattern)
"WHERE c.relnamespace = n.oid\n"
" AND c.oid = pr.prrelid\n"
" AND pr.prpubid = '%s'\n", pubid);
+ if (pset.sversion >= 150000)
+ {
+ /*
+ * Don't list tables that are also covered by a published
+ * schema.
+ */
+ appendPQExpBuffer(&buf,
+ " AND NOT EXISTS (\n"
+ " SELECT 1\n"
+ " FROM pg_catalog.pg_publication_namespace pn\n"
+ " WHERE pn.pnpubid = pr.prpubid\n"
+ " AND pn.pnnspid = c.relnamespace)\n");
+ }
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..9c6386d518a 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -146,11 +146,22 @@ 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"
+-- table also covered by a published schema should appear only once in \d output
+\d pub_test.testpub_nopk
+ Table "pub_test.testpub_nopk"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ foo | integer | | |
+ bar | integer | | |
+Included in publications:
+ "testpub_for_tbl_schema"
+ "testpub_foralltables"
+ "testpub_forschema"
+ "testpub_fortable"
+
-- weird parser corner case
CREATE PUBLICATION testpub_parsertst FOR TABLE pub_test.testpub_nopk, CURRENT_SCHEMA;
ERROR: invalid table name
@@ -167,8 +178,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,11 +841,19 @@ 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"
+-- table with a row-filter, also covered by a published schema, should appear
+-- only once in \d output and without the row filter
+\d testpub_rf_schema2.testpub_rf_tbl6
+ Table "testpub_rf_schema2.testpub_rf_tbl6"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ i | integer | | |
+Included in publications:
+ "testpub6"
+
-- fail - virtual generated column uses user-defined function
-- (Actually, this already fails at CREATE TABLE rather than at CREATE
-- PUBLICATION, but let's keep the test in case the former gets
diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql
index 041e14a4de6..fac54b02e27 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -83,6 +83,8 @@ CREATE PUBLICATION testpub_forschema FOR TABLES IN SCHEMA pub_test;
CREATE PUBLICATION testpub_for_tbl_schema FOR TABLES IN SCHEMA pub_test, TABLE pub_test.testpub_nopk;
RESET client_min_messages;
\dRp+ testpub_for_tbl_schema
+-- table also covered by a published schema should appear only once in \d output
+\d pub_test.testpub_nopk
-- weird parser corner case
CREATE PUBLICATION testpub_parsertst FOR TABLE pub_test.testpub_nopk, CURRENT_SCHEMA;
@@ -406,6 +408,9 @@ CREATE PUBLICATION testpub6 FOR TABLES IN SCHEMA testpub_rf_schema2;
ALTER PUBLICATION testpub6 SET TABLES IN SCHEMA testpub_rf_schema2, TABLE testpub_rf_schema2.testpub_rf_tbl6 WHERE (i < 99);
RESET client_min_messages;
\dRp+ testpub6
+-- table with a row-filter, also covered by a published schema, should appear
+-- only once in \d output and without the row filter
+\d testpub_rf_schema2.testpub_rf_tbl6
-- fail - virtual generated column uses user-defined function
-- (Actually, this already fails at CREATE TABLE rather than at CREATE
-- PUBLICATION, but let's keep the test in case the former gets
--
2.47.3
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: PSQL - prevent describe listing tables that are already in listed schemas
2026-05-18 03:20 PSQL - prevent describe listing tables that are already in listed schemas Peter Smith <[email protected]>
2026-05-19 06:23 ` Re: PSQL - prevent describe listing tables that are already in listed schemas Jim Jones <[email protected]>
2026-05-19 07:08 ` Re: PSQL - prevent describe listing tables that are already in listed schemas Peter Smith <[email protected]>
2026-05-19 12:08 ` Re: PSQL - prevent describe listing tables that are already in listed schemas Jim Jones <[email protected]>
2026-05-19 23:59 ` Re: PSQL - prevent describe listing tables that are already in listed schemas Peter Smith <[email protected]>
2026-05-20 07:09 ` Re: PSQL - prevent describe listing tables that are already in listed schemas Nisha Moond <[email protected]>
2026-05-20 07:30 ` Re: PSQL - prevent describe listing tables that are already in listed schemas Jim Jones <[email protected]>
2026-05-20 08:27 ` Re: PSQL - prevent describe listing tables that are already in listed schemas Nisha Moond <[email protected]>
2026-05-20 23:34 ` Re: PSQL - prevent describe listing tables that are already in listed schemas Peter Smith <[email protected]>
@ 2026-05-21 05:53 ` Jim Jones <[email protected]>
0 siblings, 0 replies; 11+ messages in thread
From: Jim Jones @ 2026-05-21 05:53 UTC (permalink / raw)
To: Peter Smith <[email protected]>; Nisha Moond <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
On 21/05/2026 01:34, Peter Smith wrote:
> I think Nisha was correct about adding a PG15 version check. It was an
> accidental omission in my first patch. Even if PG14 is EOL-ed by the
> time this change is released, it is trivial to keep the psql \dRp+
> command behaving as-is rather than crashing due to an internal SQL
> error. So, I prefer to err on the side of caution and add the version
> check for now; a committer can remove it if they deem it unnecessary.
In that case, I have no further comments on this patch and will mark the
CF entry as "Ready for Committer"
Thanks!
Best, Jim
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2026-05-21 05:53 UTC | newest]
Thread overview: 11+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-05-18 03:20 PSQL - prevent describe listing tables that are already in listed schemas Peter Smith <[email protected]>
2026-05-19 06:23 ` Jim Jones <[email protected]>
2026-05-19 07:08 ` Peter Smith <[email protected]>
2026-05-19 12:08 ` Jim Jones <[email protected]>
2026-05-19 23:59 ` Peter Smith <[email protected]>
2026-05-20 07:03 ` Jim Jones <[email protected]>
2026-05-20 07:09 ` Nisha Moond <[email protected]>
2026-05-20 07:30 ` Jim Jones <[email protected]>
2026-05-20 08:27 ` Nisha Moond <[email protected]>
2026-05-20 23:34 ` Peter Smith <[email protected]>
2026-05-21 05:53 ` Jim Jones <[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