public inbox for [email protected]
help / color / mirror / Atom feedFrom: Peter Smith <[email protected]>
To: Jim Jones <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: PSQL - prevent describe listing tables that are already in listed schemas
Date: Wed, 20 May 2026 09:59:46 +1000
Message-ID: <CAHut+PvfAQQTaV4qQzivBx_LLhveA0h=ugy_MRRR+NjwndNPgg@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAHut+PvSOmRrQX+VrFYHtFipV9hM=p99FeOwYCzkuU2BOaLu7Q@mail.gmail.com>
<[email protected]>
<CAHut+Pturr_EY3WgVMT5+rrcKLm9HcRCk5X0WH8zvwtD2K2ixA@mail.gmail.com>
<[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
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]
Subject: Re: PSQL - prevent describe listing tables that are already in listed schemas
In-Reply-To: <CAHut+PvfAQQTaV4qQzivBx_LLhveA0h=ugy_MRRR+NjwndNPgg@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