Horton

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

Jim Jones <jim.jones@uni-muenster.de>

View thread
From: Jim Jones <jim.jones@uni-muenster.de>
To: Peter Smith <smithpb2250@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2026-05-19T12:08:36Z
Lists: pgsql-hackers

Attachments

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.

Best, Jim