Обсуждение: EXCEPT TABLE - Case inconsistency for describe \d and \dRp+
Hi.
While experimenting with "CREATE PUBLICATION ... FOR ALL TABLES EXCEPT
(TABLE...)" I noticed a small "case" inconsistency.
Background: The \d and \dRp+ command were enhanced in commit fd36606
[1][2] to say what tables are excluded from what publications.
e.g.
# -- describe the publications
# \dRp+ pub1
Publication pub1
Owner | All tables | All sequences | Inserts | Updates | Deletes |
Truncates | Generated columns | Via root | Description
----------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+-------------
postgres | t | f | t | t | t | t
| none | f |
Except tables:
"public.t1"
"public.t2"
# \dRp+ pub2
Publication pub2
Owner | All tables | All sequences | Inserts | Updates | Deletes |
Truncates | Generated columns | Via root | Description
----------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+-------------
postgres | f | f | t | t | t | t
| none | f |
Tables:
"public.t2"
"public.t3"
# -- describe the tables
# \d t1
Table "public.t1"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | |
Except Publications:
"pub1"
# \d t2
Table "public.t2"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | |
Publications:
"pub2"
Except Publications:
"pub1"
~~~
Notice that that the describe publication says:
"Tables:" and "Except tables:" (Lowercase "tables")
Notice that the describe table says:
"Publications:" and "Except Publications:" (Uppercase "Publications")
I think they should use a consistent case:
eg1. (both lowercase) Except tables; Except publications, OR
eg2. (bother uppercase) Except Tables; Except Publications
~~~
I did not yet provide a patch because I was not sure which way
(uppercase/lowercase) is best, so wanted to hear some opinions about
that first.
(I'm expecting that some DOCS examples and/or TESTS for "describe"
will also be impacted).
Thoughts?
======
[1]
https://github.com/postgres/postgres/commit/fd366065e06ae953c4f2d973d5c5f0474f3b87b6#diff-cdb325f8994210a4cfacc19525f11e9b368a3f78641b67105c105449df9b645cR6859
[2]
https://github.com/postgres/postgres/commit/fd366065e06ae953c4f2d973d5c5f0474f3b87b6#diff-cdb325f8994210a4cfacc19525f11e9b368a3f78641b67105c105449df9b645cR3183
Kind Regards,
Peter Smith.
Fujitsu Australia
On Fri, Apr 10, 2026 at 12:43 PM Peter Smith <smithpb2250@gmail.com> wrote:
>
> Notice that that the describe publication says:
> "Tables:" and "Except tables:" (Lowercase "tables")
>
> Notice that the describe table says:
> "Publications:" and "Except Publications:" (Uppercase "Publications")
>
> I think they should use a consistent case:
> eg1. (both lowercase) Except tables; Except publications, OR
> eg2. (bother uppercase) Except Tables; Except Publications
>
We should follow eg1. I checked few examples where we follow same
pattern as eg1:
Example-1: (See: "Tables from schemas")
postgres=# \dRp+ pub1
Publication pub1
Owner | All tables | All sequences | Inserts | Updates | Deletes |
Truncates | Generated columns | Via root | Description
-------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+-------------
amit | f | f | t | t | t | t
| none | f |
Tables from schemas:
"sch1"
Example-2: ((See: "No connections")
postgres=# create user u1 CONNECTION LIMIT 0;
CREATE ROLE
postgres=# \du u1
List of roles
Role name | Attributes
-----------+----------------
u1 | No connections
--
With Regards,
Amit Kapila.
On Fri, Apr 10, 2026 at 8:19 PM Amit Kapila <amit.kapila16@gmail.com> wrote: > > On Fri, Apr 10, 2026 at 12:43 PM Peter Smith <smithpb2250@gmail.com> wrote: > > > > Notice that that the describe publication says: > > "Tables:" and "Except tables:" (Lowercase "tables") > > > > Notice that the describe table says: > > "Publications:" and "Except Publications:" (Uppercase "Publications") > > > > I think they should use a consistent case: > > eg1. (both lowercase) Except tables; Except publications, OR > > eg2. (bother uppercase) Except Tables; Except Publications > > > > We should follow eg1. I checked few examples where we follow same > pattern as eg1: > > Example-1: (See: "Tables from schemas") > postgres=# \dRp+ pub1 > Publication pub1 > Owner | All tables | All sequences | Inserts | Updates | Deletes | > Truncates | Generated columns | Via root | Description > -------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- > amit | f | f | t | t | t | t > | none | f | > Tables from schemas: > "sch1" > > Example-2: ((See: "No connections") > postgres=# create user u1 CONNECTION LIMIT 0; > CREATE ROLE > postgres=# \du u1 > List of roles > Role name | Attributes > -----------+---------------- > u1 | No connections > Hi Amit. Thanks for the advice. PSA patch v1 to change "Except Publications" to "Except publications". ====== Kind Regards, Peter Smith. Fujitsu Australia
Вложения
On Mon, 13 Apr 2026 at 07:45, Peter Smith <smithpb2250@gmail.com> wrote: > > On Fri, Apr 10, 2026 at 8:19 PM Amit Kapila <amit.kapila16@gmail.com> wrote: > > > > On Fri, Apr 10, 2026 at 12:43 PM Peter Smith <smithpb2250@gmail.com> wrote: > > > > > > Notice that that the describe publication says: > > > "Tables:" and "Except tables:" (Lowercase "tables") > > > > > > Notice that the describe table says: > > > "Publications:" and "Except Publications:" (Uppercase "Publications") > > > > > > I think they should use a consistent case: > > > eg1. (both lowercase) Except tables; Except publications, OR > > > eg2. (bother uppercase) Except Tables; Except Publications > > > > > > > We should follow eg1. I checked few examples where we follow same > > pattern as eg1: > > > > Example-1: (See: "Tables from schemas") > > postgres=# \dRp+ pub1 > > Publication pub1 > > Owner | All tables | All sequences | Inserts | Updates | Deletes | > > Truncates | Generated columns | Via root | Description > > -------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------------- > > amit | f | f | t | t | t | t > > | none | f | > > Tables from schemas: > > "sch1" > > > > Example-2: ((See: "No connections") > > postgres=# create user u1 CONNECTION LIMIT 0; > > CREATE ROLE > > postgres=# \du u1 > > List of roles > > Role name | Attributes > > -----------+---------------- > > u1 | No connections > > > > Hi Amit. > > Thanks for the advice. > > PSA patch v1 to change "Except Publications" to "Except publications". The commit message could be improved: Date: Mon, 13 Apr 2026 12:12:00 +1000 Subject: [PATCH v1] describe table except publications --- src/bin/psql/describe.c | 2 +- Other than that, the patch looks good to me. Regards, Vignesh.
On Mon, Apr 13, 2026 at 1:21 PM vignesh C <vignesh21@gmail.com> wrote: > ... > The commit message could be improved: > Date: Mon, 13 Apr 2026 12:12:00 +1000 > Subject: [PATCH v1] describe table except publications > > --- > src/bin/psql/describe.c | 2 +- > > Other than that, the patch looks good to me. > Hi Vignesh. Thanks for the review. PSA patch v2. It is the same as v1, but with an improved commit message, ====== Kind Regards, Peter Smith. Fujitsu Australia
Вложения
On Mon, Apr 13, 2026 at 3:23 PM Peter Smith <smithpb2250@gmail.com> wrote: > > On Mon, Apr 13, 2026 at 1:21 PM vignesh C <vignesh21@gmail.com> wrote: > > > ... > > The commit message could be improved: > > Date: Mon, 13 Apr 2026 12:12:00 +1000 > > Subject: [PATCH v1] describe table except publications > > > > --- > > src/bin/psql/describe.c | 2 +- > > > > Other than that, the patch looks good to me. > > > > Hi Vignesh. > > Thanks for the review. > > PSA patch v2. It is the same as v1, but with an improved commit message, > Hi Amit. Thanks for pushing. ====== Kind Regards Peter Smith. Fujitsu Australia
On 2026-Apr-10, Peter Smith wrote: > # -- describe the tables > # \d t1 > Table "public.t1" > Column | Type | Collation | Nullable | Default > --------+---------+-----------+----------+--------- > a | integer | | | > Except Publications: > "pub1" I have a hard time understanding what an "except publication" is. I think this would be clearer if the header said "Excluded from publications:" A footer section under a publication saying "Except tables" on the other hand is crystal clear. -- Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
On Fri, Apr 17, 2026 at 12:10 AM Álvaro Herrera <alvherre@kurilemu.de> wrote:
>
> On 2026-Apr-10, Peter Smith wrote:
>
> > # -- describe the tables
> > # \d t1
> > Table "public.t1"
> > Column | Type | Collation | Nullable | Default
> > --------+---------+-----------+----------+---------
> > a | integer | | |
> > Except Publications:
> > "pub1"
>
> I have a hard time understanding what an "except publication" is. I
> think this would be clearer if the header said "Excluded from publications:"
>
I agree that your suggestion is clearer.
If we make that change, then should the "Publications:" footer also be
modified similarly (to "Included in publications:") so they match?
e.g.
CURRENTLY
test_pub=# \d t1
Table "public.t1"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | |
Publications:
"pub1"
"pub2"
Except publications:
"pub3"
~~~
SUGGESTION
test_pub=# \d t1
Table "public.t1"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | |
Included in publications:
"pub1"
"pub2"
Excluded from publications:
"pub3"
~~~
I will wait for more opinions/consensus before providing a patch.
======
Kind Regards,
Peter Smith.
Fujitsu Australia
On Fri, Apr 17, 2026 at 4:34 AM Peter Smith <smithpb2250@gmail.com> wrote: > > On Fri, Apr 17, 2026 at 12:10 AM Álvaro Herrera <alvherre@kurilemu.de> wrote: > > > > On 2026-Apr-10, Peter Smith wrote: > > > > > # -- describe the tables > > > # \d t1 > > > Table "public.t1" > > > Column | Type | Collation | Nullable | Default > > > --------+---------+-----------+----------+--------- > > > a | integer | | | > > > Except Publications: > > > "pub1" > > > > I have a hard time understanding what an "except publication" is. I > > think this would be clearer if the header said "Excluded from publications:" > > > > I agree that your suggestion is clearer. > > If we make that change, then should the "Publications:" footer also be > modified similarly (to "Included in publications:") so they match? > > e.g. > > CURRENTLY > > test_pub=# \d t1 > Table "public.t1" > Column | Type | Collation | Nullable | Default > --------+---------+-----------+----------+--------- > a | integer | | | > Publications: > "pub1" > "pub2" > Except publications: > "pub3" > > ~~~ > > SUGGESTION > > test_pub=# \d t1 > Table "public.t1" > Column | Type | Collation | Nullable | Default > --------+---------+-----------+----------+--------- > a | integer | | | > Included in publications: > "pub1" > "pub2" > Excluded from publications: > "pub3" > > ~~~ > +1 for the proposal. It makes the description clear. -- With Regards, Amit Kapila.
On Fri, Apr 17, 2026 at 3:24 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
>
> On Fri, Apr 17, 2026 at 4:34 AM Peter Smith <smithpb2250@gmail.com> wrote:
> >
> > On Fri, Apr 17, 2026 at 12:10 AM Álvaro Herrera <alvherre@kurilemu.de> wrote:
> > >
> > > On 2026-Apr-10, Peter Smith wrote:
> > >
> > > > # -- describe the tables
> > > > # \d t1
> > > > Table "public.t1"
> > > > Column | Type | Collation | Nullable | Default
> > > > --------+---------+-----------+----------+---------
> > > > a | integer | | |
> > > > Except Publications:
> > > > "pub1"
> > >
> > > I have a hard time understanding what an "except publication" is. I
> > > think this would be clearer if the header said "Excluded from publications:"
> > >
> >
> > I agree that your suggestion is clearer.
> >
> > If we make that change, then should the "Publications:" footer also be
> > modified similarly (to "Included in publications:") so they match?
> >
> > e.g.
> >
> > CURRENTLY
> >
> > test_pub=# \d t1
> > Table "public.t1"
> > Column | Type | Collation | Nullable | Default
> > --------+---------+-----------+----------+---------
> > a | integer | | |
> > Publications:
> > "pub1"
> > "pub2"
> > Except publications:
> > "pub3"
> >
> > ~~~
> >
> > SUGGESTION
> >
> > test_pub=# \d t1
> > Table "public.t1"
> > Column | Type | Collation | Nullable | Default
> > --------+---------+-----------+----------+---------
> > a | integer | | |
> > Included in publications:
> > "pub1"
> > "pub2"
> > Excluded from publications:
> > "pub3"
> >
> > ~~~
> >
>
> +1 for the proposal. It makes the description clear.
>
OK. Thanks.
Next question: am I only changing this for the table describe (\d)?
I am asking because I noticed the schema describe (\dn) also has a
footer called "Publications:".
----------
test_pub=# CREATE PUBLICATION pub7 FOR TABLES IN SCHEMA my_schema;
CREATE PUBLICATION
test_pub=# \dn+ my_schema
List of schemas
Name | Owner | Access privileges | Description
-----------+----------+-------------------+-------------
my_schema | postgres | |
Publications:
"pub7"
----------
Would you like this schema footer also changed to say "Included in
publications:"?
======
Kind Regards,
Peter Smith.
Fujitsu Australia
On Fri, Apr 17, 2026 at 3:24 PM Amit Kapila <amit.kapila16@gmail.com> wrote: > > On Fri, Apr 17, 2026 at 4:34 AM Peter Smith <smithpb2250@gmail.com> wrote: > > > > On Fri, Apr 17, 2026 at 12:10 AM Álvaro Herrera <alvherre@kurilemu.de> wrote: > > > > > > On 2026-Apr-10, Peter Smith wrote: > > > > > > > # -- describe the tables > > > > # \d t1 > > > > Table "public.t1" > > > > Column | Type | Collation | Nullable | Default > > > > --------+---------+-----------+----------+--------- > > > > a | integer | | | > > > > Except Publications: > > > > "pub1" > > > > > > I have a hard time understanding what an "except publication" is. I > > > think this would be clearer if the header said "Excluded from publications:" > > > > > > > I agree that your suggestion is clearer. > > > > If we make that change, then should the "Publications:" footer also be > > modified similarly (to "Included in publications:") so they match? > > > > e.g. > > > > CURRENTLY > > > > test_pub=# \d t1 > > Table "public.t1" > > Column | Type | Collation | Nullable | Default > > --------+---------+-----------+----------+--------- > > a | integer | | | > > Publications: > > "pub1" > > "pub2" > > Except publications: > > "pub3" > > > > ~~~ > > > > SUGGESTION > > > > test_pub=# \d t1 > > Table "public.t1" > > Column | Type | Collation | Nullable | Default > > --------+---------+-----------+----------+--------- > > a | integer | | | > > Included in publications: > > "pub1" > > "pub2" > > Excluded from publications: > > "pub3" > > > > ~~~ > > > > +1 for the proposal. It makes the description clear. > PSA v3 patches. v3-0001 - Fixed the TABLE describe footer titles as suggested. - "Included in publications:" - "Excluded from publications:" v3-0002 - Made similar footer title changes for SEQUENCE describe v3-0003 - Made similar footer title changes for SHCEMA describe. In passing, I found that this \dn describe output was not even being tested, so I added the missing regression test. ====== Kind Regards, Peter Smith. Fujitsu Australia.
Вложения
On Mon, 20 Apr 2026 at 08:27, Peter Smith <smithpb2250@gmail.com> wrote: > > On Fri, Apr 17, 2026 at 3:24 PM Amit Kapila <amit.kapila16@gmail.com> wrote: > > > > On Fri, Apr 17, 2026 at 4:34 AM Peter Smith <smithpb2250@gmail.com> wrote: > > > > > > On Fri, Apr 17, 2026 at 12:10 AM Álvaro Herrera <alvherre@kurilemu.de> wrote: > > > > > > > > On 2026-Apr-10, Peter Smith wrote: > > > > > > > > > # -- describe the tables > > > > > # \d t1 > > > > > Table "public.t1" > > > > > Column | Type | Collation | Nullable | Default > > > > > --------+---------+-----------+----------+--------- > > > > > a | integer | | | > > > > > Except Publications: > > > > > "pub1" > > > > > > > > I have a hard time understanding what an "except publication" is. I > > > > think this would be clearer if the header said "Excluded from publications:" > > > > > > > > > > I agree that your suggestion is clearer. > > > > > > If we make that change, then should the "Publications:" footer also be > > > modified similarly (to "Included in publications:") so they match? > > > > > > e.g. > > > > > > CURRENTLY > > > > > > test_pub=# \d t1 > > > Table "public.t1" > > > Column | Type | Collation | Nullable | Default > > > --------+---------+-----------+----------+--------- > > > a | integer | | | > > > Publications: > > > "pub1" > > > "pub2" > > > Except publications: > > > "pub3" > > > > > > ~~~ > > > > > > SUGGESTION > > > > > > test_pub=# \d t1 > > > Table "public.t1" > > > Column | Type | Collation | Nullable | Default > > > --------+---------+-----------+----------+--------- > > > a | integer | | | > > > Included in publications: > > > "pub1" > > > "pub2" > > > Excluded from publications: > > > "pub3" > > > > > > ~~~ > > > > > > > +1 for the proposal. It makes the description clear. > > > > PSA v3 patches. > > v3-0001 - Fixed the TABLE describe footer titles as suggested. > - "Included in publications:" > - "Excluded from publications:" > > v3-0002 - Made similar footer title changes for SEQUENCE describe > > v3-0003 - Made similar footer title changes for SHCEMA describe. In > passing, I found that this \dn describe output was not even being > tested, so I added the missing regression test. I'm not sure separate patches are necessary for this change; it might be better to merge them into a single patch. Other than that, the changes look good. Regards, Vignesh
> > > > PSA v3 patches. > > > > v3-0001 - Fixed the TABLE describe footer titles as suggested. > > - "Included in publications:" > > - "Excluded from publications:" > > > > v3-0002 - Made similar footer title changes for SEQUENCE describe > > > > v3-0003 - Made similar footer title changes for SHCEMA describe. In > > passing, I found that this \dn describe output was not even being > > tested, so I added the missing regression test. > > I'm not sure separate patches are necessary for this change; it might > be better to merge them into a single patch. Other than that, the > changes look good. > I separated them because only the changes to TABLE \d had previously been discussed. If everyone thinks all changes are good, then I can combine them into one patch. ====== Kind Regards, Peter Smith. Fujitsu Australia.
On 4/20/2026 2:46 PM, Peter Smith wrote: >>> PSA v3 patches. >>> >>> v3-0001 - Fixed the TABLE describe footer titles as suggested. >>> - "Included in publications:" >>> - "Excluded from publications:" >>> >>> v3-0002 - Made similar footer title changes for SEQUENCE describe >>> >>> v3-0003 - Made similar footer title changes for SHCEMA describe. In >>> passing, I found that this \dn describe output was not even being >>> tested, so I added the missing regression test. >> I'm not sure separate patches are necessary for this change; it might >> be better to merge them into a single patch. Other than that, the >> changes look good. >> > I separated them because only the changes to TABLE \d had previously > been discussed. > > If everyone thinks all changes are good, then I can combine them into one patch. > > ====== > Kind Regards, > Peter Smith. > Fujitsu Australia. > While reviewing 0003, I noticed a formatting issue in the following line: + * schema mapping count + 1 (for storing NULL). There is an extra space after "count +". Regards, Yuchen Li
On Mon, Apr 20, 2026 at 6:50 PM Yuchen Li <liyuchen_xyz@163.com> wrote: > > On 4/20/2026 2:46 PM, Peter Smith wrote: > >>> PSA v3 patches. > >>> > >>> v3-0001 - Fixed the TABLE describe footer titles as suggested. > >>> - "Included in publications:" > >>> - "Excluded from publications:" > >>> > >>> v3-0002 - Made similar footer title changes for SEQUENCE describe > >>> > >>> v3-0003 - Made similar footer title changes for SHCEMA describe. In > >>> passing, I found that this \dn describe output was not even being > >>> tested, so I added the missing regression test. > >> I'm not sure separate patches are necessary for this change; it might > >> be better to merge them into a single patch. Other than that, the > >> changes look good. > >> > > I separated them because only the changes to TABLE \d had previously > > been discussed. > > > > If everyone thinks all changes are good, then I can combine them into one patch. > > > > ====== > > Kind Regards, > > Peter Smith. > > Fujitsu Australia. > > > While reviewing 0003, I noticed a formatting issue in the following line: > > + * schema mapping count + 1 (for storing NULL). > > There is an extra space after "count +". > PSA v4, now combined as Vignesh suggested, and with the extra space also fixed. ====== Kind Regards, Peter Smith. Fujitsu Australia.
Вложения
On Tue, Apr 21, 2026 at 12:16 PM Peter Smith <smithpb2250@gmail.com> wrote: > > PSA v4, now combined as Vignesh suggested, and with the extra space also fixed. > I would like to explicitly mention in the commit message that "Tag for included publications was a pre-existing behavior but it is better to change it for making it consistent with excluded publications tag. So, how about a commit message as follows: "psql: Improve describe footer titles for publications. The psql describe (`\d`) footer titles were previously unintuitive when listing publications that included or excluded specific tables. Even though the tag for included publications was pre-existing, it is better to update it to "Included in publications:" to match the phrasing of the "Excluded from publications:" tag. Footer titles for sequence and schema descriptions have been updated similarly to maintain consistency." -- With Regards, Amit Kapila.