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

Hi Peter

On 18/05/2026 05:20, Peter Smith wrote:

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

I agree that the proposed paatch makes it easier to read the listed
tables and better aligns with the docs.

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;

With this patch I get:

postgres=# \dRp+ pub1
Publication pub1
Owner | All tables | All sequences | Inserts | Updates | Deletes |
Truncates | Generated columns | Via root | Description
-------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+-------------
jim | f | f | t | t | t | t
| none | f |
Tables:
"public.t1"
Tables from schemas:
"s"

Without it:

postgres=# \dRp+ pub1
Publication pub1
Owner | All tables | All sequences | Inserts | Updates | Deletes |
Truncates | Generated columns | Via root | Description
-------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+-------------
jim | f | f | t | t | t | t
| none | f |
Tables:
"public.t1"
"s.t2" WHERE (c > 42)
"s.t3"
"s.t4"
Tables from schemas:
"s"

One nitpick: you forgot a \n at the end of "WHERE pn.pnpubid = '%s')".

Best, Jim

Hi Jim.

Thanks for reviewing and testing my patch. PSA v2 with that missing \n restored.

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

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

On Tue, May 19, 2026 at 10:08 PM 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=# \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.

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?

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

On 20/05/2026 01:59, Peter Smith wrote:

Apparently, there was no existing test case to demonstrate that \d+
case you found. Do we need to bother adding one?

I think adding a test here would be worthwhile -- publications.sql
already defines the tables and publications, and a test would help catch
regressions caused by future refactoring.

What about this?

diff --git a/src/test/regress/sql/publication.sql
b/src/test/regress/sql/publication.sql
index 041e14a4de6..fac54b02e27 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -83,6 +83,8 @@ CREATE PUBLICATION testpub_forschema FOR TABLES IN
SCHEMA pub_test;
 CREATE PUBLICATION testpub_for_tbl_schema FOR TABLES IN SCHEMA
pub_test, TABLE pub_test.testpub_nopk;
 RESET client_min_messages;
 \dRp+ testpub_for_tbl_schema
+-- table also covered by a published schema should appear only once in
\d output
+\d pub_test.testpub_nopk

 -- weird parser corner case
 CREATE PUBLICATION testpub_parsertst FOR TABLE pub_test.testpub_nopk,
CURRENT_SCHEMA;
@@ -406,6 +408,9 @@ CREATE PUBLICATION testpub6 FOR TABLES IN SCHEMA
testpub_rf_schema2;
 ALTER PUBLICATION testpub6 SET TABLES IN SCHEMA testpub_rf_schema2,
TABLE testpub_rf_schema2.testpub_rf_tbl6 WHERE (i < 99);
 RESET client_min_messages;
 \dRp+ testpub6
+-- table with a row-filter, also covered by a published schema, should
appear
+-- only once in \d output and without the row filter
+\d testpub_rf_schema2.testpub_rf_tbl6
 -- fail - virtual generated column uses user-defined function
 -- (Actually, this already fails at CREATE TABLE rather than at CREATE
 -- PUBLICATION, but let's keep the test in case the former gets

I previously said the issue was in \d+, but it’s actually in \d. I’ve
updated the commit message accordingly.

db=# \d testpub_rf_schema2.testpub_rf_tbl6
Table "testpub_rf_schema2.testpub_rf_tbl6"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
i | integer | | |
Publications:
"testpub6" WHERE (i < 99)
"testpub6"

PSA v5.

Thanks!

Best, Jim

On Wed, May 20, 2026 at 5:30 AM Peter Smith <smithpb2250@gmail.com> wrote:

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.

+1 for the idea.

For the describePublications (\dRp+) case, should we add an (sversion

= 150000) guard around the new change, since it accesses

pg_publication_namespace, which is only available in PG15 and above?
Thoughts?

--
Thanks,
Nisha

Hi Nisha

On 20/05/2026 09:09, Nisha Moond wrote:

For the describePublications (\dRp+) case, should we add an (sversion

= 150000) guard around the new change, since it accesses

pg_publication_namespace, which is only available in PG15 and above?
Thoughts?

I considered that in my first review, but since PG14 will be EOL'd by
the time PG20 is shipped, I thought it wouldn't be necessary.

Thanks!

Best, Jim

On Wed, May 20, 2026 at 1:00 PM Jim Jones <jim.jones@uni-muenster.de> wrote:

Hi Nisha

On 20/05/2026 09:09, Nisha Moond wrote:

For the describePublications (\dRp+) case, should we add an (sversion

= 150000) guard around the new change, since it accesses

pg_publication_namespace, which is only available in PG15 and above?
Thoughts?

I considered that in my first review, but since PG14 will be EOL'd by
the time PG20 is shipped, I thought it wouldn't be necessary.

Okay, that makes sense.

--
Thanks,
Nisha

On Wed, May 20, 2026 at 6:28 PM Nisha Moond <nisha.moond412@gmail.com> wrote:

On Wed, May 20, 2026 at 1:00 PM Jim Jones <jim.jones@uni-muenster.de> wrote:

Hi Nisha

On 20/05/2026 09:09, Nisha Moond wrote:

For the describePublications (\dRp+) case, should we add an (sversion

= 150000) guard around the new change, since it accesses

pg_publication_namespace, which is only available in PG15 and above?
Thoughts?

I considered that in my first review, but since PG14 will be EOL'd by
the time PG20 is shipped, I thought it wouldn't be necessary.

Okay, that makes sense.

The new tests in patch v5 look good to me.

I think Nisha was correct about adding a PG15 version check. It was an
accidental omission in my first patch. Even if PG14 is EOL-ed by the
time this change is released, it is trivial to keep the psql \dRp+
command behaving as-is rather than crashing due to an internal SQL
error. So, I prefer to err on the side of caution and add the version
check for now; a committer can remove it if they deem it unnecessary.

Added Nisha as a reviewer in the commit message.

PSA v6.

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

On 21/05/2026 01:34, Peter Smith wrote:

I think Nisha was correct about adding a PG15 version check. It was an
accidental omission in my first patch. Even if PG14 is EOL-ed by the
time this change is released, it is trivial to keep the psql \dRp+
command behaving as-is rather than crashing due to an internal SQL
error. So, I prefer to err on the side of caution and add the version
check for now; a committer can remove it if they deem it unnecessary.

In that case, I have no further comments on this patch and will mark the
CF entry as "Ready for Committer"

Thanks!

Best, Jim