MIME-Version: 1.0
References: 
 <CAHut+PvSOmRrQX+VrFYHtFipV9hM=p99FeOwYCzkuU2BOaLu7Q@mail.gmail.com>
 <5420ddcb-7b55-4a73-b8c5-3602abfac78b@uni-muenster.de>
 <CAHut+Pturr_EY3WgVMT5+rrcKLm9HcRCk5X0WH8zvwtD2K2ixA@mail.gmail.com>
 <1b1c88ce-2c61-4faf-b647-496525fc177f@uni-muenster.de>
In-Reply-To: <1b1c88ce-2c61-4faf-b647-496525fc177f@uni-muenster.de>
From: Peter Smith <smithpb2250@gmail.com>
Date: Wed, 20 May 2026 09:59:46 +1000
Message-ID: 
 <CAHut+PvfAQQTaV4qQzivBx_LLhveA0h=ugy_MRRR+NjwndNPgg@mail.gmail.com>
Subject: Re: PSQL - prevent describe listing tables that are already in listed
 schemas
To: Jim Jones <jim.jones@uni-muenster.de>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: multipart/mixed; boundary="000000000000e3e1d80652347aee"
Archived-At: 
 <https://www.postgresql.org/message-id/CAHut%2BPvfAQQTaV4qQzivBx_LLhveA0h%3Dugy_MRRR%2BNjwndNPgg%40mail.gmail.com>
Precedence: bulk

--000000000000e3e1d80652347aee
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Tue, May 19, 2026 at 10:08=E2=80=AFPM 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=3D# \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 =3D p.oid\n"
> "       AND pn.pnnspid =3D c.relnamespace)\n",
>
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
>
> Example:
>
> postgres=3D# 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=3D# \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=3D# CREATE PUBLICATION pub2 FOR TABLE s.t2;
> CREATE PUBLICATION
> postgres=3D# \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=3D# \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=3D# \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?

=3D=3D=3D=3D=3D=3D
Kind Regards,
Peter Smith.
Fujitsu Australia

--000000000000e3e1d80652347aee
Content-Type: application/octet-stream;
	name="v4-0001-Fix-psql-duplicate-items-for-dRp-and-d.patch"
Content-Disposition: attachment;
	filename="v4-0001-Fix-psql-duplicate-items-for-dRp-and-d.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_mpdan9lc0>
X-Attachment-Id: f_mpdan9lc0

RnJvbSA0ZTI5NWQ1MGUyNTBkNWU5NTVmOWQyZWJiZTI1OTM1ODU4YjgzYTI4IE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBQZXRlciBTbWl0aCA8cGV0ZXIuYi5zbWl0aEBmdWppdHN1LmNv
bT4KRGF0ZTogV2VkLCAyMCBNYXkgMjAyNiAwOToyNDo1NyArMTAwMApTdWJqZWN0OiBbUEFUQ0gg
djRdIEZpeCBwc3FsIGR1cGxpY2F0ZSBpdGVtcyBmb3IgXGRScCsgYW5kIFxkKwoKV2hlbiBhIHB1
YmxpY2F0aW9uIHB1YjEgaXMgZGVmaW5lZCB1c2luZyBib3RoICJUQUJMRVMgSU4gU0NIRU1BIG15
c2NoZW1hIiwgYW5kCiJUQUJMRSBteXNjaGVtYS50MSIsIGl0IGNhdXNlZCBkdXBsaWNhdGVkIGl0
ZW1zIGluIHRoZSBwc3FsIGRlc2NyaWJlIG91dHB1dC4KClRoaXMgcGF0Y2ggc2ltcGxpZmllcyB0
aGUgb3V0cHV0IGFuZCBhbGlnbnMgYmV0dGVyIHdpdGggdGhlIGRvY3VtZW50YXRpb24uCgpOb3c6
CiJcZFJwKyBwdWIxIiAgc2hvd3Mgb25seSBTY2hlbWEgIm15c2NoZW1hIiwgbm90IGFsc28gVGFi
bGUgIm15c2NoZW1hLnQxIgoiXGQrIHQxIiAgICAgIHNob3dzIFB1YmxpY2F0aW9uICJwdWIxIiBv
bmx5IG9uY2UKCkF1dGhvcjogUGV0ZXIgU21pdGggPHNtaXRocGIyMjUwQGdtYWlsLmNvbT4KQ28t
YXV0aG9yOiBKaW0gSm9uZXMgPGppbS5qb25lc0B1bmktbXVlbnN0ZXIuZGU+CkRpc2N1c3Npb246
IGh0dHBzOi8vd3d3LnBvc3RncmVzcWwub3JnL21lc3NhZ2UtaWQvZmxhdC9DQUh1dCUyQlB2U09t
UnJRWCUyQlZyRllIdEZpcFY5aE0lM0RwOTlGZU93WUN6a3VVMkJPYUx1N1ElNDBtYWlsLmdtYWls
LmNvbQotLS0KIHNyYy9iaW4vcHNxbC9kZXNjcmliZS5jICAgICAgICAgICAgICAgICAgIHwgMTUg
KysrKysrKysrKysrKy0tCiBzcmMvdGVzdC9yZWdyZXNzL2V4cGVjdGVkL3B1YmxpY2F0aW9uLm91
dCB8ICA2IC0tLS0tLQogMiBmaWxlcyBjaGFuZ2VkLCAxMyBpbnNlcnRpb25zKCspLCA4IGRlbGV0
aW9ucygtKQoKZGlmZiAtLWdpdCBhL3NyYy9iaW4vcHNxbC9kZXNjcmliZS5jIGIvc3JjL2Jpbi9w
c3FsL2Rlc2NyaWJlLmMKaW5kZXggZTE0NDk2NTRmOTYuLmUzMjZkYWY2MzJmIDEwMDY0NAotLS0g
YS9zcmMvYmluL3BzcWwvZGVzY3JpYmUuYworKysgYi9zcmMvYmluL3BzcWwvZGVzY3JpYmUuYwpA
QCAtMzE5OSw3ICszMTk5LDEyIEBAIGRlc2NyaWJlT25lVGFibGVEZXRhaWxzKGNvbnN0IGNoYXIg
KnNjaGVtYW5hbWUsCiAJCQkJCQkJCSAgIkZST00gcGdfY2F0YWxvZy5wZ19wdWJsaWNhdGlvbiBw
XG4iCiAJCQkJCQkJCSAgIiAgICAgSk9JTiBwZ19jYXRhbG9nLnBnX3B1YmxpY2F0aW9uX3JlbCBw
ciBPTiBwLm9pZCA9IHByLnBycHViaWRcbiIKIAkJCQkJCQkJICAiICAgICBKT0lOIHBnX2NhdGFs
b2cucGdfY2xhc3MgYyBPTiBjLm9pZCA9IHByLnBycmVsaWRcbiIKLQkJCQkJCQkJICAiV0hFUkUg
cHIucHJyZWxpZCA9ICclcydcbiIsCisJCQkJCQkJCSAgIldIRVJFIHByLnBycmVsaWQgPSAnJXMn
XG4iCisJCQkJCQkJCSAgIiAgQU5EIE5PVCBFWElTVFMgKFxuIgorCQkJCQkJCQkgICIgICAgIFNF
TEVDVCAxXG4iCisJCQkJCQkJCSAgIiAgICAgRlJPTSBwZ19jYXRhbG9nLnBnX3B1YmxpY2F0aW9u
X25hbWVzcGFjZSBwblxuIgorCQkJCQkJCQkgICIgICAgIFdIRVJFIHBuLnBucHViaWQgPSBwLm9p
ZFxuIgorCQkJCQkJCQkgICIgICAgICAgQU5EIHBuLnBubnNwaWQgPSBjLnJlbG5hbWVzcGFjZSlc
biIsCiAJCQkJCQkJCSAgb2lkLCBvaWQsIG9pZCk7CiAKIAkJCQlpZiAocHNldC5zdmVyc2lvbiA+
PSAxOTAwMDApCkBAIC03MDE1LDcgKzcwMjAsMTMgQEAgZGVzY3JpYmVQdWJsaWNhdGlvbnMoY29u
c3QgY2hhciAqcGF0dGVybikKIAkJCQkJCQkgICIgICAgIHBnX2NhdGFsb2cucGdfcHVibGljYXRp
b25fcmVsIHByXG4iCiAJCQkJCQkJICAiV0hFUkUgYy5yZWxuYW1lc3BhY2UgPSBuLm9pZFxuIgog
CQkJCQkJCSAgIiAgQU5EIGMub2lkID0gcHIucHJyZWxpZFxuIgotCQkJCQkJCSAgIiAgQU5EIHBy
LnBycHViaWQgPSAnJXMnXG4iLCBwdWJpZCk7CisJCQkJCQkJICAiICBBTkQgcHIucHJwdWJpZCA9
ICclcydcbiIKKwkJCQkJCQkgICIgIEFORCBOT1QgRVhJU1RTIChcbiIKKwkJCQkJCQkgICIgICAg
IFNFTEVDVCAxXG4iCisJCQkJCQkJICAiICAgICBGUk9NIHBnX2NhdGFsb2cucGdfcHVibGljYXRp
b25fbmFtZXNwYWNlIHBuXG4iCisJCQkJCQkJICAiICAgICBXSEVSRSBwbi5wbnB1YmlkID0gcHIu
cHJwdWJpZFxuIgorCQkJCQkJCSAgIiAgICAgICBBTkQgcG4ucG5uc3BpZCA9IGMucmVsbmFtZXNw
YWNlKVxuIiwKKwkJCQkJCQkgIHB1YmlkKTsKIAogCQkJaWYgKHBzZXQuc3ZlcnNpb24gPj0gMTkw
MDAwKQogCQkJCWFwcGVuZFBRRXhwQnVmZmVyU3RyKCZidWYsICIgIEFORCBOT1QgcHIucHJleGNl
cHRcbiIpOwpkaWZmIC0tZ2l0IGEvc3JjL3Rlc3QvcmVncmVzcy9leHBlY3RlZC9wdWJsaWNhdGlv
bi5vdXQgYi9zcmMvdGVzdC9yZWdyZXNzL2V4cGVjdGVkL3B1YmxpY2F0aW9uLm91dAppbmRleCAy
OWU1NGIyMTRhMC4uYjI5YjZiNTMxMTkgMTAwNjQ0Ci0tLSBhL3NyYy90ZXN0L3JlZ3Jlc3MvZXhw
ZWN0ZWQvcHVibGljYXRpb24ub3V0CisrKyBiL3NyYy90ZXN0L3JlZ3Jlc3MvZXhwZWN0ZWQvcHVi
bGljYXRpb24ub3V0CkBAIC0xNDYsOCArMTQ2LDYgQEAgUkVTRVQgY2xpZW50X21pbl9tZXNzYWdl
czsKICAgICAgICAgICBPd25lciAgICAgICAgICAgfCBBbGwgdGFibGVzIHwgQWxsIHNlcXVlbmNl
cyB8IEluc2VydHMgfCBVcGRhdGVzIHwgRGVsZXRlcyB8IFRydW5jYXRlcyB8IEdlbmVyYXRlZCBj
b2x1bW5zIHwgVmlhIHJvb3QgfCBEZXNjcmlwdGlvbiAKIC0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tKy0tLS0tLS0tLS0tLSstLS0tLS0tLS0tLS0tLS0rLS0tLS0tLS0tKy0tLS0tLS0tLSstLS0t
LS0tLS0rLS0tLS0tLS0tLS0rLS0tLS0tLS0tLS0tLS0tLS0tLSstLS0tLS0tLS0tKy0tLS0tLS0t
LS0tLS0KICByZWdyZXNzX3B1YmxpY2F0aW9uX3VzZXIgfCBmICAgICAgICAgIHwgZiAgICAgICAg
ICAgICB8IHQgICAgICAgfCB0ICAgICAgIHwgdCAgICAgICB8IHQgICAgICAgICB8IG5vbmUgICAg
ICAgICAgICAgIHwgZiAgICAgICAgfCAKLVRhYmxlczoKLSAgICAicHViX3Rlc3QudGVzdHB1Yl9u
b3BrIgogVGFibGVzIGZyb20gc2NoZW1hczoKICAgICAicHViX3Rlc3QiCiAKQEAgLTE2Nyw4ICsx
NjUsNiBAQCBBTFRFUiBQVUJMSUNBVElPTiB0ZXN0cHViX2ZvcnNjaGVtYSBBREQgVEFCTEUgcHVi
X3Rlc3QudGVzdHB1Yl9ub3BrOwogICAgICAgICAgIE93bmVyICAgICAgICAgICB8IEFsbCB0YWJs
ZXMgfCBBbGwgc2VxdWVuY2VzIHwgSW5zZXJ0cyB8IFVwZGF0ZXMgfCBEZWxldGVzIHwgVHJ1bmNh
dGVzIHwgR2VuZXJhdGVkIGNvbHVtbnMgfCBWaWEgcm9vdCB8IERlc2NyaXB0aW9uIAogLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0rLS0tLS0tLS0tLS0tKy0tLS0tLS0tLS0tLS0tLSstLS0tLS0t
LS0rLS0tLS0tLS0tKy0tLS0tLS0tLSstLS0tLS0tLS0tLSstLS0tLS0tLS0tLS0tLS0tLS0tKy0t
LS0tLS0tLS0rLS0tLS0tLS0tLS0tLQogIHJlZ3Jlc3NfcHVibGljYXRpb25fdXNlciB8IGYgICAg
ICAgICAgfCBmICAgICAgICAgICAgIHwgdCAgICAgICB8IHQgICAgICAgfCB0ICAgICAgIHwgdCAg
ICAgICAgIHwgbm9uZSAgICAgICAgICAgICAgfCBmICAgICAgICB8IAotVGFibGVzOgotICAgICJw
dWJfdGVzdC50ZXN0cHViX25vcGsiCiBUYWJsZXMgZnJvbSBzY2hlbWFzOgogICAgICJwdWJfdGVz
dCIKIApAQCAtODMyLDggKzgyOCw2IEBAIFJFU0VUIGNsaWVudF9taW5fbWVzc2FnZXM7CiAgICAg
ICAgICAgT3duZXIgICAgICAgICAgIHwgQWxsIHRhYmxlcyB8IEFsbCBzZXF1ZW5jZXMgfCBJbnNl
cnRzIHwgVXBkYXRlcyB8IERlbGV0ZXMgfCBUcnVuY2F0ZXMgfCBHZW5lcmF0ZWQgY29sdW1ucyB8
IFZpYSByb290IHwgRGVzY3JpcHRpb24gCiAtLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLSstLS0t
LS0tLS0tLS0rLS0tLS0tLS0tLS0tLS0tKy0tLS0tLS0tLSstLS0tLS0tLS0rLS0tLS0tLS0tKy0t
LS0tLS0tLS0tKy0tLS0tLS0tLS0tLS0tLS0tLS0rLS0tLS0tLS0tLSstLS0tLS0tLS0tLS0tCiAg
cmVncmVzc19wdWJsaWNhdGlvbl91c2VyIHwgZiAgICAgICAgICB8IGYgICAgICAgICAgICAgfCB0
ICAgICAgIHwgdCAgICAgICB8IHQgICAgICAgfCB0ICAgICAgICAgfCBub25lICAgICAgICAgICAg
ICB8IGYgICAgICAgIHwgCi1UYWJsZXM6Ci0gICAgInRlc3RwdWJfcmZfc2NoZW1hMi50ZXN0cHVi
X3JmX3RibDYiIFdIRVJFIChpIDwgOTkpCiBUYWJsZXMgZnJvbSBzY2hlbWFzOgogICAgICJ0ZXN0
cHViX3JmX3NjaGVtYTIiCiAKLS0gCjIuNDcuMwoK
--000000000000e3e1d80652347aee--