public inbox for [email protected]
help / color / mirror / Atom feedFrom: Peter Smith <[email protected]>
To: PostgreSQL Hackers <[email protected]>
Subject: PSQL - prevent describe listing tables that are already in listed schemas
Date: Mon, 18 May 2026 13:20:14 +1000
Message-ID: <CAHut+PvSOmRrQX+VrFYHtFipV9hM=p99FeOwYCzkuU2BOaLu7Q@mail.gmail.com> (raw)
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
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]
Subject: Re: PSQL - prevent describe listing tables that are already in listed schemas
In-Reply-To: <CAHut+PvSOmRrQX+VrFYHtFipV9hM=p99FeOwYCzkuU2BOaLu7Q@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