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

From: Peter Smith <smithpb2250(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: PSQL - prevent describe listing tables that are already in listed schemas
Date: 2026-05-18 03:20:14
Message-ID: CAHut+PvSOmRrQX+VrFYHtFipV9hM=p99FeOwYCzkuU2BOaLu7Q@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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%40mail.gmail.com
[2] https://www.postgresql.org/docs/devel/sql-createpublication.html

Kind Regards,
Peter Smith.
Fujitsu Australia

Attachment Content-Type Size
v1-0001-Fix-psql-publication-describe-for-tables-in-schem.patch application/octet-stream 3.0 KB

Browse pgsql-hackers by date

  From Date Subject
Previous Message Michael Paquier 2026-05-18 03:15:36 Re: [PATCH] Fix ProcKill lock-group vs procLatch recycle race