| 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 |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tatsuo Ishii | 2026-05-18 03:45:51 | Re: Should IGNORE NULLS cache nullness for volatile arguments? |
| Previous Message | Michael Paquier | 2026-05-18 03:15:36 | Re: [PATCH] Fix ProcKill lock-group vs procLatch recycle race |