Re: PSQL - prevent describe listing tables that are already in listed schemas

On Tue, May 19, 2026 at 10:08 PM Jim Jones <jim(dot)jones(at)uni-muenster(dot)de> 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