MIME-Version: 1.0
From: Peter Smith <smithpb2250@gmail.com>
Date: Mon, 18 May 2026 13:20:14 +1000
Message-ID: 
 <CAHut+PvSOmRrQX+VrFYHtFipV9hM=p99FeOwYCzkuU2BOaLu7Q@mail.gmail.com>
Subject: PSQL - prevent describe listing tables that are already in listed
 schemas
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: multipart/mixed; boundary="00000000000018028a06520f0cae"
Archived-At: 
 <https://www.postgresql.org/message-id/CAHut%2BPvSOmRrQX%2BVrFYHtFipV9hM%3Dp99FeOwYCzkuU2BOaLu7Q%40mail.gmail.com>
Precedence: bulk

--00000000000018028a06520f0cae
Content-Type: text/plain; charset="UTF-8"

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

--00000000000018028a06520f0cae
Content-Type: application/octet-stream;
	name="v1-0001-Fix-psql-publication-describe-for-tables-in-schem.patch"
Content-Disposition: attachment;
	filename="v1-0001-Fix-psql-publication-describe-for-tables-in-schem.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_mpamtiqf0>
X-Attachment-Id: f_mpamtiqf0

RnJvbSBmMjhlOGMzYTVkMWVkNWU3YWIxN2Q5NzgyOTNkMTlhYjM1ZGFjODEwIE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBQZXRlciBTbWl0aCA8cGV0ZXIuYi5zbWl0aEBmdWppdHN1LmNv
bT4KRGF0ZTogTW9uLCAxOCBNYXkgMjAyNiAxMjo1ODo1OCArMTAwMApTdWJqZWN0OiBbUEFUQ0gg
djFdIEZpeCBwc3FsIHB1YmxpY2F0aW9uIGRlc2NyaWJlIGZvciB0YWJsZXMgaW4gc2NoZW1hCgot
LS0KIHNyYy9iaW4vcHNxbC9kZXNjcmliZS5jICAgICAgICAgICAgICAgICAgIHwgNyArKysrKyst
CiBzcmMvdGVzdC9yZWdyZXNzL2V4cGVjdGVkL3B1YmxpY2F0aW9uLm91dCB8IDYgLS0tLS0tCiAy
IGZpbGVzIGNoYW5nZWQsIDYgaW5zZXJ0aW9ucygrKSwgNyBkZWxldGlvbnMoLSkKCmRpZmYgLS1n
aXQgYS9zcmMvYmluL3BzcWwvZGVzY3JpYmUuYyBiL3NyYy9iaW4vcHNxbC9kZXNjcmliZS5jCmlu
ZGV4IGUxNDQ5NjU0Zjk2Li5iZWMyY2UwNTdmOSAxMDA2NDQKLS0tIGEvc3JjL2Jpbi9wc3FsL2Rl
c2NyaWJlLmMKKysrIGIvc3JjL2Jpbi9wc3FsL2Rlc2NyaWJlLmMKQEAgLTcwMTUsNyArNzAxNSwx
MiBAQCBkZXNjcmliZVB1YmxpY2F0aW9ucyhjb25zdCBjaGFyICpwYXR0ZXJuKQogCQkJCQkJCSAg
IiAgICAgcGdfY2F0YWxvZy5wZ19wdWJsaWNhdGlvbl9yZWwgcHJcbiIKIAkJCQkJCQkgICJXSEVS
RSBjLnJlbG5hbWVzcGFjZSA9IG4ub2lkXG4iCiAJCQkJCQkJICAiICBBTkQgYy5vaWQgPSBwci5w
cnJlbGlkXG4iCi0JCQkJCQkJICAiICBBTkQgcHIucHJwdWJpZCA9ICclcydcbiIsIHB1YmlkKTsK
KwkJCQkJCQkgICIgIEFORCBwci5wcnB1YmlkID0gJyVzJ1xuIgorCQkJCQkJCSAgIiAgQU5EIG4u
b2lkIE5PVCBJTiAoXG4iCisJCQkJCQkJICAiICAgICBTRUxFQ1QgcG4ucG5uc3BpZFxuIgorCQkJ
CQkJCSAgIiAgICAgRlJPTSBwZ19jYXRhbG9nLnBnX3B1YmxpY2F0aW9uX25hbWVzcGFjZSBwblxu
IgorCQkJCQkJCSAgIiAgICAgV0hFUkUgcG4ucG5wdWJpZCA9ICclcycpIiwKKwkJCQkJCQkgIHB1
YmlkLCBwdWJpZCk7CiAKIAkJCWlmIChwc2V0LnN2ZXJzaW9uID49IDE5MDAwMCkKIAkJCQlhcHBl
bmRQUUV4cEJ1ZmZlclN0cigmYnVmLCAiICBBTkQgTk9UIHByLnByZXhjZXB0XG4iKTsKZGlmZiAt
LWdpdCBhL3NyYy90ZXN0L3JlZ3Jlc3MvZXhwZWN0ZWQvcHVibGljYXRpb24ub3V0IGIvc3JjL3Rl
c3QvcmVncmVzcy9leHBlY3RlZC9wdWJsaWNhdGlvbi5vdXQKaW5kZXggMjllNTRiMjE0YTAuLmIy
OWI2YjUzMTE5IDEwMDY0NAotLS0gYS9zcmMvdGVzdC9yZWdyZXNzL2V4cGVjdGVkL3B1YmxpY2F0
aW9uLm91dAorKysgYi9zcmMvdGVzdC9yZWdyZXNzL2V4cGVjdGVkL3B1YmxpY2F0aW9uLm91dApA
QCAtMTQ2LDggKzE0Niw2IEBAIFJFU0VUIGNsaWVudF9taW5fbWVzc2FnZXM7CiAgICAgICAgICAg
T3duZXIgICAgICAgICAgIHwgQWxsIHRhYmxlcyB8IEFsbCBzZXF1ZW5jZXMgfCBJbnNlcnRzIHwg
VXBkYXRlcyB8IERlbGV0ZXMgfCBUcnVuY2F0ZXMgfCBHZW5lcmF0ZWQgY29sdW1ucyB8IFZpYSBy
b290IHwgRGVzY3JpcHRpb24gCiAtLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLSstLS0tLS0tLS0t
LS0rLS0tLS0tLS0tLS0tLS0tKy0tLS0tLS0tLSstLS0tLS0tLS0rLS0tLS0tLS0tKy0tLS0tLS0t
LS0tKy0tLS0tLS0tLS0tLS0tLS0tLS0rLS0tLS0tLS0tLSstLS0tLS0tLS0tLS0tCiAgcmVncmVz
c19wdWJsaWNhdGlvbl91c2VyIHwgZiAgICAgICAgICB8IGYgICAgICAgICAgICAgfCB0ICAgICAg
IHwgdCAgICAgICB8IHQgICAgICAgfCB0ICAgICAgICAgfCBub25lICAgICAgICAgICAgICB8IGYg
ICAgICAgIHwgCi1UYWJsZXM6Ci0gICAgInB1Yl90ZXN0LnRlc3RwdWJfbm9wayIKIFRhYmxlcyBm
cm9tIHNjaGVtYXM6CiAgICAgInB1Yl90ZXN0IgogCkBAIC0xNjcsOCArMTY1LDYgQEAgQUxURVIg
UFVCTElDQVRJT04gdGVzdHB1Yl9mb3JzY2hlbWEgQUREIFRBQkxFIHB1Yl90ZXN0LnRlc3RwdWJf
bm9wazsKICAgICAgICAgICBPd25lciAgICAgICAgICAgfCBBbGwgdGFibGVzIHwgQWxsIHNlcXVl
bmNlcyB8IEluc2VydHMgfCBVcGRhdGVzIHwgRGVsZXRlcyB8IFRydW5jYXRlcyB8IEdlbmVyYXRl
ZCBjb2x1bW5zIHwgVmlhIHJvb3QgfCBEZXNjcmlwdGlvbiAKIC0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tKy0tLS0tLS0tLS0tLSstLS0tLS0tLS0tLS0tLS0rLS0tLS0tLS0tKy0tLS0tLS0tLSst
LS0tLS0tLS0rLS0tLS0tLS0tLS0rLS0tLS0tLS0tLS0tLS0tLS0tLSstLS0tLS0tLS0tKy0tLS0t
LS0tLS0tLS0KICByZWdyZXNzX3B1YmxpY2F0aW9uX3VzZXIgfCBmICAgICAgICAgIHwgZiAgICAg
ICAgICAgICB8IHQgICAgICAgfCB0ICAgICAgIHwgdCAgICAgICB8IHQgICAgICAgICB8IG5vbmUg
ICAgICAgICAgICAgIHwgZiAgICAgICAgfCAKLVRhYmxlczoKLSAgICAicHViX3Rlc3QudGVzdHB1
Yl9ub3BrIgogVGFibGVzIGZyb20gc2NoZW1hczoKICAgICAicHViX3Rlc3QiCiAKQEAgLTgzMiw4
ICs4MjgsNiBAQCBSRVNFVCBjbGllbnRfbWluX21lc3NhZ2VzOwogICAgICAgICAgIE93bmVyICAg
ICAgICAgICB8IEFsbCB0YWJsZXMgfCBBbGwgc2VxdWVuY2VzIHwgSW5zZXJ0cyB8IFVwZGF0ZXMg
fCBEZWxldGVzIHwgVHJ1bmNhdGVzIHwgR2VuZXJhdGVkIGNvbHVtbnMgfCBWaWEgcm9vdCB8IERl
c2NyaXB0aW9uIAogLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0rLS0tLS0tLS0tLS0tKy0tLS0t
LS0tLS0tLS0tLSstLS0tLS0tLS0rLS0tLS0tLS0tKy0tLS0tLS0tLSstLS0tLS0tLS0tLSstLS0t
LS0tLS0tLS0tLS0tLS0tKy0tLS0tLS0tLS0rLS0tLS0tLS0tLS0tLQogIHJlZ3Jlc3NfcHVibGlj
YXRpb25fdXNlciB8IGYgICAgICAgICAgfCBmICAgICAgICAgICAgIHwgdCAgICAgICB8IHQgICAg
ICAgfCB0ICAgICAgIHwgdCAgICAgICAgIHwgbm9uZSAgICAgICAgICAgICAgfCBmICAgICAgICB8
IAotVGFibGVzOgotICAgICJ0ZXN0cHViX3JmX3NjaGVtYTIudGVzdHB1Yl9yZl90Ymw2IiBXSEVS
RSAoaSA8IDk5KQogVGFibGVzIGZyb20gc2NoZW1hczoKICAgICAidGVzdHB1Yl9yZl9zY2hlbWEy
IgogCi0tIAoyLjQ3LjMKCg==
--00000000000018028a06520f0cae--