public inbox for [email protected]
help / color / mirror / Atom feedFrom: Peter Smith <[email protected]>
To: Nisha Moond <[email protected]>
Cc: Jim Jones <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: PSQL - prevent describe listing tables that are already in listed schemas
Date: Thu, 21 May 2026 09:34:11 +1000
Message-ID: <CAHut+PtecJnFckGxEMT0-yhW=nTDR9aVcWpn_G+rFWTLTkP=YA@mail.gmail.com> (raw)
In-Reply-To: <CABdArM4_=HZjkQAxy5OA2u0h+ZLCacQGNQREot8-nXJ2QNTgbA@mail.gmail.com>
References: <CAHut+PvSOmRrQX+VrFYHtFipV9hM=p99FeOwYCzkuU2BOaLu7Q@mail.gmail.com>
<[email protected]>
<CAHut+Pturr_EY3WgVMT5+rrcKLm9HcRCk5X0WH8zvwtD2K2ixA@mail.gmail.com>
<[email protected]>
<CAHut+PvfAQQTaV4qQzivBx_LLhveA0h=ugy_MRRR+NjwndNPgg@mail.gmail.com>
<CABdArM6Ls0DRkqmpg5oDjwJhkrC9rvUQ9+y1jNmwRG+cq57cVQ@mail.gmail.com>
<[email protected]>
<CABdArM4_=HZjkQAxy5OA2u0h+ZLCacQGNQREot8-nXJ2QNTgbA@mail.gmail.com>
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
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: PSQL - prevent describe listing tables that are already in listed schemas
In-Reply-To: <CAHut+PtecJnFckGxEMT0-yhW=nTDR9aVcWpn_G+rFWTLTkP=YA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox