Thread

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

Peter Smith <smithpb2250@gmail.com> — 2026-05-19T23:59:46Z

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