PSQL schema "describe" \dn is not escaping quotes

Hi Peter,

I took a look at your patch. The core fix is correct and
does address the reported crash: the old code built the
query with a raw `'%s'` substitution of the user-supplied pattern,
so a pattern containing a single quote (\dn "it's a bug")
broke out of the string literal and produced a syntax error
on the server.

A few things worth addressing before this is finalized, though:

1. Indentation

if (!validateSQLNamePattern(...))
goto error_return; // should be indented one more tab

2. The new validateSQLNamePattern() call duplicates validation that
already happened.

The validateSQLNamePattern() has been called before your change. Since only the escaping/query-building side effect is actually
needed here. You can call the lower-level processSQLNamePattern() directly and doesn't check its return value, because no additional validation is needed:

processSQLNamePattern(pset.db, &buf, pattern, false, false,
NULL, "n.nspname", NULL,
NULL, NULL, NULL);

Thanks,
Steven
________________________________________
From: Peter Smith <smithpb2250@gmail.com>
Sent: Tuesday, July 28, 2026 15:07
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: PSQL schema "describe" \dn is not escaping quotes

The psql schema "describe" command (\dn) is not correctly escaping the
schema pattern arg given to it.

This results in an internal SQL error like below:

test_pub=# \dn "it's a bug"
2026-07-28 15:44:55.498 AEST [28319] ERROR:  syntax error at or near
"s" at character 251
2026-07-28 15:44:55.498 AEST [28319] STATEMENT:  /* Get publications
that publish this schema */
        SELECT pubname
        FROM pg_catalog.pg_publication p
             JOIN pg_catalog.pg_publication_namespace pn ON p.oid = pn.pnpubid
             JOIN pg_catalog.pg_namespace n ON n.oid = pn.pnnspid
        WHERE n.nspname = '"it's a bug"'
        ORDER BY 1
ERROR:  syntax error at or near "s"
LINE 6: WHERE n.nspname = '"it's a bug"'
                               ^
~~~

PSA a v1 patch to address this.

~~~

After applying my patch, now you can do stuff like the following:

test_pub=# CREATE SCHEMA "it's working";
CREATE SCHEMA
test_pub=# CREATE PUBLICATION pub1 FOR TABLES IN SCHEMA "it's working";
CREATE PUBLICATION
test_pub=# \dn "it's working"
     List of schemas
     Name     |  Owner
--------------+----------
 it's working | postgres
Included in publications:
    "pub1"

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

Hi Peter & Steven

On 28/07/2026 10:19, Steven Niu wrote:

1. Indentation

if (!validateSQLNamePattern(...))
goto error_return; // should be indented one more tab

2. The new validateSQLNamePattern() call duplicates validation that
already happened.

The validateSQLNamePattern() has been called before your change. Since only the escaping/query-building side effect is actually
needed here. You can call the lower-level processSQLNamePattern() directly and doesn't check its return value, because no additional validation is needed:

processSQLNamePattern(pset.db, &buf, pattern, false, false,
NULL, "n.nspname", NULL,
NULL, NULL, NULL);

I'm also wondering if we should use appendPQExpBufferStr instead of
appendPQExpBuffer here, since there are no format strings -- just to be
consistent with the other queries in the same file.

appendPQExpBuffer(&buf,
"SELECT pubname \n"
"FROM pg_catalog.pg_publication p\n"
" JOIN pg_catalog.pg_publication_namespace pn ON p.oid = pn.pnpubid\n"
" JOIN pg_catalog.pg_namespace n ON n.oid = pn.pnnspid \n");
...
appendPQExpBuffer(&buf, "ORDER BY 1;");

Other than that the patch LGTM.

Thanks!

Best, Jim

Thanks to both for the reviews.

On Tue, Jul 28, 2026 at 11:18 PM Jim Jones <jim.jones@uni-muenster.de> wrote:

Hi Peter & Steven

On 28/07/2026 10:19, Steven Niu wrote:

1. Indentation

if (!validateSQLNamePattern(...))
goto error_return; // should be indented one more tab

Now I have run pgindent.

2. The new validateSQLNamePattern() call duplicates validation that
already happened.

The validateSQLNamePattern() has been called before your change. Since only the escaping/query-building side effect is actually
needed here. You can call the lower-level processSQLNamePattern() directly and doesn't check its return value, because no additional validation is needed:

processSQLNamePattern(pset.db, &buf, pattern, false, false,
NULL, "n.nspname", NULL,
NULL, NULL, NULL);

Done. Thanks for pointing me to this lower function.

I'm also wondering if we should use appendPQExpBufferStr instead of
appendPQExpBuffer here, since there are no format strings -- just to be
consistent with the other queries in the same file.

appendPQExpBuffer(&buf,
"SELECT pubname \n"
"FROM pg_catalog.pg_publication p\n"
" JOIN pg_catalog.pg_publication_namespace pn ON p.oid = pn.pnpubid\n"
" JOIN pg_catalog.pg_namespace n ON n.oid = pn.pnnspid \n");
...
appendPQExpBuffer(&buf, "ORDER BY 1;");

Done. Oops.

~~~

PSA patch v2 to address all of the above.

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

Thanks for the patch!

Here are my review comments on v2:-

1) A test case is missing for the patch. You can add the same test you
already shared.

2) I ran make world, and the patch looks good with it.

3) It looks like this needs to be backported. I checked; it is reproducible
up to STABLE 15. It is not reproducible in STABLE 14.

Regards,
Nishant Sharma.
EDB, Pune.
https://www.enterprisedb.com/

On Wed, Jul 29, 2026 at 7:58 PM Nishant Sharma
<nishant.sharma@enterprisedb.com> wrote:

Thanks for the patch!

Here are my review comments on v2:-

1) A test case is missing for the patch. You can add the same test you already shared.

Thanks for your review.

PSA v3. This is the same as v2, but now includes some test cases.

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

Hi Peter,

I applied v3 and gave it a test today. Some notes:

1) v3-0001-Fix-escapes-for-psql-describe-schema-patterns.patch applies
cleanly with `git am` on top of current master (tip 1f97b252dfe).

2) publication regression test passes cleanly, including the new
embedded-quote cases you added. No diffs.

3) I also performed a manual test on an instance:

CREATE SCHEMA "it's a bug";
\dn "it's a bug"
With v3 applied it now correctly returns:

List of schemas
Name | Owner
------------+--------------
it's a bug | jiaoshuntian
(1 row)

So the fix behaves as described and the test coverage looks adequate
to me.

Regards,
Shuntian Jiao

Hi Peter,

Thanks for the patch. Nice review comments.

I applied v3 on master and did some testing of my own. A few observations.

1) The patch fixes more than the reported case, which I think is worth
calling out in the commit message. Because the old code compared
n.nspname against the raw pattern text, the footer was silently missing for
*any* double-quoted pattern, not just ones containing an embedded single
quote:

-- unpatched
test=# \dn sch_a
List of schemas
Name | Owner
-------+----------
sch_a | postgres
Included in publications:
"pub_a"

test=# \dn "sch_a"
List of schemas
Name | Owner
-------+----------
sch_a | postgres
(1 row) <-- footer silently missing

FWIW I checked describe.c for other places where a user-supplied pattern is
interpolated into query text, and this is the only one
every other use of 'pattern' is either a pg_log_error() message or goes
through validateSQLNamePattern().

2) I'm less sure about switching this query to pattern matching.
processSQLNamePattern() turns it into a regex match, so it can now
match several schemas, but the footer belongs to the whole table rather
than to any one row:

-- v3
test=# \dn sch*
List of schemas
Name | Owner
-------+----------
sch_a | postgres
sch_b | postgres
Included in publications:
"pub_a"
"pub_b"

There's no way to tell which publication goes with which schema, and with
"\dn *" you get every schema publication in the database lumped
into one footer.
Unpatched, "\dn sch*" prints no footer at all (nspname = 'sch*' matches
nothing), so this is new behaviour.

Since the footer only really makes sense for a single schema, how about
keying off the row the main query already returned, rather than
re-interpreting the pattern?

if (pattern && PQntuples(res) == 1 && pset.sversion >= 150000)
{
...
appendPQExpBufferStr(&buf,
"SELECT pubname \n"
"FROM pg_catalog.pg_publication p\n"
" JOIN
pg_catalog.pg_publication_namespace pn ON p.oid = pn.pnpubid\n"
" JOIN pg_catalog.pg_namespace n ON
n.oid = pn.pnnspid \n"
"WHERE n.nspname = ");
appendStringLiteralConn(&buf, PQgetvalue(res, 0, 0), pset.db);
appendPQExpBufferStr(&buf, "\nORDER BY 1");

That is exactly escaped, can't drift from what's actually displayed, and
skips the second query entirely when it wouldn't be meaningful.

Minor nit:
- One test line has a stray trailing semicolon:
\d "it's my public table";
psql strips it so it's harmless, but the neighbouring lines don't have one.

Regards,
Surya Poondla

On Wed, Sep 2, 2026 at 9:48 AM surya poondla <suryapoondla4@gmail.com> wrote:

Hi Peter,

Thanks for the patch. Nice review comments.

I applied v3 on master and did some testing of my own. A few observations.

1) The patch fixes more than the reported case, which I think is worth calling out in the commit message. Because the old code compared
n.nspname against the raw pattern text, the footer was silently missing for *any* double-quoted pattern, not just ones containing an embedded single quote:

-- unpatched
test=# \dn sch_a
List of schemas
Name | Owner
-------+----------
sch_a | postgres
Included in publications:
"pub_a"

test=# \dn "sch_a"
List of schemas
Name | Owner
-------+----------
sch_a | postgres
(1 row) <-- footer silently missing

FWIW I checked describe.c for other places where a user-supplied pattern is interpolated into query text, and this is the only one
every other use of 'pattern' is either a pg_log_error() message or goes through validateSQLNamePattern().

2) I'm less sure about switching this query to pattern matching. processSQLNamePattern() turns it into a regex match, so it can now
match several schemas, but the footer belongs to the whole table rather than to any one row:

-- v3
test=# \dn sch*
List of schemas
Name | Owner
-------+----------
sch_a | postgres
sch_b | postgres
Included in publications:
"pub_a"
"pub_b"

There's no way to tell which publication goes with which schema, and with "\dn *" you get every schema publication in the database lumped
into one footer.

Good catch. I see your point. When I tried the similar scenaro for
tables it clearly separated them, so I am working to make the describe
schemas behave like that. Will post a new patch when ready.

CREATE TABLE t1_a(a int);
CREATE TABLE t1_b(a int);
CREATE TABLE t1_c(a int);
CREATE PUBLICATION pub_a FOR TABLE t1_a;
CREATE PUBLICATION pub_b FOR TABLE t1_b;

test_pub=# \d t1_a
Table "public.t1_a"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | |
Included in publications:
"pub_a"

test_pub=# \d t1_*
Table "public.t1_a"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | |
Included in publications:
"pub_a"

Table "public.t1_b"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | |
Included in publications:
"pub_b"

Table "public.t1_c"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | |

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

On Wed, Sep 2, 2026 at 9:48 AM surya poondla <suryapoondla4@gmail.com> wrote:

Hi Peter,

Thanks for the patch. Nice review comments.

I applied v3 on master and did some testing of my own. A few observations.

1) The patch fixes more than the reported case, which I think is worth calling out in the commit message. Because the old code compared
n.nspname against the raw pattern text, the footer was silently missing for *any* double-quoted pattern, not just ones containing an embedded single quote:

-- unpatched
test=# \dn sch_a
List of schemas
Name | Owner
-------+----------
sch_a | postgres
Included in publications:
"pub_a"

test=# \dn "sch_a"
List of schemas
Name | Owner
-------+----------
sch_a | postgres
(1 row) <-- footer silently missing

FWIW I checked describe.c for other places where a user-supplied pattern is interpolated into query text, and this is the only one
every other use of 'pattern' is either a pg_log_error() message or goes through validateSQLNamePattern().

Done. Commit message is updated.

2) I'm less sure about switching this query to pattern matching. processSQLNamePattern() turns it into a regex match, so it can now
match several schemas, but the footer belongs to the whole table rather than to any one row:

-- v3
test=# \dn sch*
List of schemas
Name | Owner
-------+----------
sch_a | postgres
sch_b | postgres
Included in publications:
"pub_a"
"pub_b"

There's no way to tell which publication goes with which schema, and with "\dn *" you get every schema publication in the database lumped
into one footer.

Thanks for reporting this issue!

It caused me to rewrite everything, but I think the patch is now much
better for having done it -- it now closely mimics the `describe
table` behavior.

Minor nit:
- One test line has a stray trailing semicolon:
\d "it's my public table";
psql strips it so it's harmless, but the neighbouring lines don't have one.

OK, the spurious semi-colon is removed.

I've also added more test cases for verified multiple schema/footers
with a common \dn pattern.

~~~

PSA patch v4.

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

On Wed, Sep 2, 2026 at 7:19 PM Peter Smith <smithpb2250@gmail.com> wrote:

I've also added more test cases for verified multiple schema/footers
with a common \dn pattern.

~~~

PSA patch v4.

Hello Peter,

Thanks for the patch. I tested a few things and found a bug:

postgres=# CREATE SCHEMA "MySchema";
CREATE SCHEMA
postgres=# CREATE PUBLICATION pub_mixed FOR TABLES IN SCHEMA "MySchema";
CREATE PUBLICATION
postgres=# \dn "MySchema"
Schema "MySchema"
Name | Owner
------+-------

The patch doesn't seem to work with certain quoted words. I don't
think you should be calling validateSQLNamePattern() and
processSQLNamePattern() from within describeOneSchemaDetails().
validateSQLNamePattern and processSQLNamePattern() assume that you are
passing in patterns rather than actual schema names and then go on to
standardise the name (like lowercasing it), but by the time the code
is inside describeOneSchemaDetails(), you already have the actual
schema name. These functions then convert the actual schema name
MySchema to myschema but then don't find it in the database, which is
why it isn't shown in the describe output.

regards,
Ajin Cherian
Fujitsu Australia

On Fri, Sep 4, 2026 at 2:33 PM Ajin Cherian <itsajin@gmail.com> wrote:

On Wed, Sep 2, 2026 at 7:19 PM Peter Smith <smithpb2250@gmail.com> wrote:

I've also added more test cases for verified multiple schema/footers
with a common \dn pattern.

~~~

PSA patch v4.

Hello Peter,

Thanks for the patch. I tested a few things and found a bug:

postgres=# CREATE SCHEMA "MySchema";
CREATE SCHEMA
postgres=# CREATE PUBLICATION pub_mixed FOR TABLES IN SCHEMA "MySchema";
CREATE PUBLICATION
postgres=# \dn "MySchema"
Schema "MySchema"
Name | Owner
------+-------

The patch doesn't seem to work with certain quoted words. I don't
think you should be calling validateSQLNamePattern() and
processSQLNamePattern() from within describeOneSchemaDetails().
validateSQLNamePattern and processSQLNamePattern() assume that you are
passing in patterns rather than actual schema names and then go on to
standardise the name (like lowercasing it), but by the time the code
is inside describeOneSchemaDetails(), you already have the actual
schema name. These functions then convert the actual schema name
MySchema to myschema but then don't find it in the database, which is
why it isn't shown in the describe output.

Erk. Thankyou for finding that.

I am surprised that patch v4 was able to pass existing regression
tests with that bug. The \dn test cases must've had some gaps. I have
added more tests for checking schema case in quoted names.

PSA patch v5.

Also, I changed the CF entry back to "Needs Review" in case there are
more problems lurking.

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

On Fri, Sep 4, 2026 at 6:30 PM Peter Smith <smithpb2250@gmail.com> wrote:

Erk. Thankyou for finding that.

I am surprised that patch v4 was able to pass existing regression
tests with that bug. The \dn test cases must've had some gaps. I have
added more tests for checking schema case in quoted names.

PSA patch v5.

Also, I changed the CF entry back to "Needs Review" in case there are
more problems lurking.

Thanks for the updated match with the fixes and the new tests. The fix
works and the new tests are welcome. A few minor comments:

1.
+ for (int i = 0; i < num_schemas; i++)
+ {
+ const char *nspname = PQgetvalue(res, i, 0);
+
+ describeOneSchemaDetails(nspname, verbose);
+ }
+ }

The return value (bool) of describeOneSchemaDetails is ignored. Is it
even required?

2.
The code now allocates memory for title as well but is not freed in
the error path.

+ initPQExpBuffer(&title);
+ printfPQExpBuffer(&title, _("Schema \"%s\""), schemaname);

regards,
Ajin Cherian
Fujitsu Australia

On Thu, Sep 10, 2026 at 7:52 PM Ajin Cherian <itsajin@gmail.com> wrote:

...

Thanks for the updated match with the fixes and the new tests. The fix
works and the new tests are welcome. A few minor comments:

1.
+ for (int i = 0; i < num_schemas; i++)
+ {
+ const char *nspname = PQgetvalue(res, i, 0);
+
+ describeOneSchemaDetails(nspname, verbose);
+ }
+ }

The return value (bool) of describeOneSchemaDetails is ignored. Is it
even required?

The return was there to mirror the other describeOneXXX functions, but
I neglected to check it. Now handling the result, similar to other
functions.

2.
The code now allocates memory for title as well but is not freed in
the error path.

+ initPQExpBuffer(&title);
+ printfPQExpBuffer(&title, _("Schema \"%s\""), schemaname);

Fixed.

PSA patch v6.

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

Hi, Peter,

I have two comments for V6 patch:

1. In function describeOneSchemaDetails(), res gets assigned by PSQLexec(),
if res is NULL, code flow goes to error_return label where termPQExpBuffer(&title) is called.

However, the title is NOT initialized at all in this case.
Suggest to initialize the title right after the initialization of buf.

2. Also in function describeOneSchemaDetails(), res gets assigned by PSQLexec();

res = PSQLexec(buf.data);

If the PQclear() is successful, we will go inside the Footer processing,
if pset.sversion >= 150000 and following PSQLexec() fails,

result = PSQLexec(buf.data);
if (!result)
goto error_return;

code flow goes to error_return label. But there is no PQclear(res), and I think it may cause memory leak;
Suggest to initialize the res to be NULL and add PQclear(res) in error_return branch.

Thanks,
Steven

________________________________________
From: Peter Smith <smithpb2250@gmail.com>
Sent: Friday, September 11, 2026 15:55
To: Ajin Cherian <itsajin@gmail.com>
Cc: surya poondla <suryapoondla4@gmail.com>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>; jiaoshuntian@gmail.com <jiaoshuntian@gmail.com>; nishant.sharma@enterprisedb.com <nishant.sharma@enterprisedb.com>; jim.jones@uni-muenster.de <jim.jones@uni-muenster.de>; niushiji@gmail.com <niushiji@gmail.com>
Subject: Re: PSQL schema "describe" \dn is not escaping quotes

On Thu, Sep 10, 2026 at 7:52 PM Ajin Cherian <itsajin@gmail.com> wrote:

...

Thanks for the updated match with the fixes and the new tests. The fix
works and the new tests are welcome. A few minor comments:

1.
+ for (int i = 0; i < num_schemas; i++)
+ {
+ const char *nspname = PQgetvalue(res, i, 0);
+
+ describeOneSchemaDetails(nspname, verbose);
+ }
+ }

The return value (bool) of  describeOneSchemaDetails is ignored. Is it
even required?

The return was there to mirror the other describeOneXXX functions, but
I neglected to check it. Now handling the result, similar to other
functions.

2.
The code now allocates memory for title as well but is not freed in
the error path.

+ initPQExpBuffer(&title);
+ printfPQExpBuffer(&title, _("Schema \"%s\""), schemaname);

Fixed.

PSA patch v6.

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

On Thu, Sep 24, 2026 at 3:39 PM Steven Niu <niushiji@gmail.com> wrote:

Hi, Peter,

I have two comments for V6 patch:

Thanks for your review!

1. In function describeOneSchemaDetails(), res gets assigned by PSQLexec(),
if res is NULL, code flow goes to error_return label where termPQExpBuffer(&title) is called.

However, the title is NOT initialized at all in this case.
Suggest to initialize the title right after the initialization of buf.

Fixed as suggested.

2. Also in function describeOneSchemaDetails(), res gets assigned by PSQLexec();

res = PSQLexec(buf.data);

If the PQclear() is successful, we will go inside the Footer processing,
if pset.sversion >= 150000 and following PSQLexec() fails,

result = PSQLexec(buf.data);
if (!result)
goto error_return;

code flow goes to error_return label. But there is no PQclear(res), and I think it may cause memory leak;
Suggest to initialize the res to be NULL and add PQclear(res) in error_return branch.

Fixed as suggested.

~

PSA patch v7.

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

Hi Peter,

Thanks for v7. It applies cleanly on master, builds without warnings, and
make check passes.
The quoting fix itself looks right: both queries now take the actual schema
name via appendStringLiteralConn(). I tried names with embedded ', ", \
and ., plus mixed case. They all display correctly with the right
publications footer, and \dn "e_sch1" now shows the footer that master
silently left out.

I have a couple of comments:
1. Backpatching. Nishant showed the bug goes back to PG15, but v7 also
changes what \dn prints whenever a pattern is given:
- each match is now its own "Schema "x"" table instead of one "List of
schemas"
- the "(N rows)" footer is gone
- in non-quiet mode, a pattern with no match is now an error

I don't think changes to user-visible output can go into 15–18. Could this
be split into two patches? 0001 would be a minimal escaping fix that can be
backpatched, e.g. keying the footer off PQgetvalue(res, 0, 0) when exactly
one row comes back. 0002 would be the per-schema display, master only, and
would probably need its own discussion. psql-ref.sgml also still says
matching schemas "are listed", so 0002 would need a doc update.

2. The no-match behaviour doesn't actually match \dt. The comment says "to
be same as \dt", but listTables() prints the error and then returns true,
while listSchemas() jumps to error_return and returns false. So under
ON_ERROR_STOP, \dn nosuch now aborts a script where it used to print an
empty table:

$ psql -v ON_ERROR_STOP=1 -c '\dn e_nosuch' -c 'select 1'; echo "exit=$?"
Did not find any schemas named "e_nosuch".
exit=1
$ psql -v ON_ERROR_STOP=1 -c '\dt e_nosuch' -c 'select 1'; echo "exit=$?"
Did not find any tables named "e_nosuch".
?column?
----------
1
(1 row)

exit=0

Also:
- the no-pattern message "Did not find any schemas" has no trailing
period, unlike the other messages in describe.c
- in quiet mode a no-match still prints the old empty "List of
schemas" table, so there are two output styles depending on -q
- pg_regress runs psql with -q, so none of the new error paths are
exercised by the regression tests

3. \dn * now prints a separate table for every schema in the database,
pg_catalog, pg_toast and information_schema included. With CSV output, a
multi-match pattern now produces several header rows in a row, which would
break anything that parses the output:
\pset format csv
\dn e_sch*
Name,Owner
e_sch1,surya
Name,Owner
e_sch2,surya
Name,Owner
e_sch3,surya
This is one more reason to keep the redesign out of the bug fix, or at
least call it out explicitly.

4. Minor issues in describeOneSchemaDetails():
- The footer query has no separator after the literal. With
ECHO_HIDDEN it shows up as WHERE n.nspname = 'e_sch1'ORDER BY 1;. It works,
but a "\n" before ORDER BY would fix it.
- The footer query still uses appendPQExpBuffer() with no format
arguments. Jim pointed this out for v1, but v2 only changed the ORDER BY
line. appendPQExpBufferStr() would do.
- myopt.topt.default_footer = false is only set inside the sversion >=
150000 branch, so against an older server each schema table still ends with
"(1 row)".

5. Tests:
- CREATE PUBLICATION pub_sch_1 FOR TABLES IN SCHEMA SCH_1 is unquoted,
so it publishes sch_1, not "SCH_1". The expected output is correct, but a
reader will probably assume the opposite. Could you add a comment, or use
distinct names?
- There's no coverage for \dn+ with a pattern or for names containing
a backslash.

Regards,
Surya Poondla

On Tue, Oct 6, 2026 at 5:18 AM surya poondla <suryapoondla4@gmail.com> wrote:

Hi Peter,

Thanks for v7. It applies cleanly on master, builds without warnings, and make check passes.
The quoting fix itself looks right: both queries now take the actual schema name via appendStringLiteralConn(). I tried names with embedded ', ", \
and ., plus mixed case. They all display correctly with the right publications footer, and \dn "e_sch1" now shows the footer that master silently left out.

I have a couple of comments:
1. Backpatching. Nishant showed the bug goes back to PG15, but v7 also changes what \dn prints whenever a pattern is given:
- each match is now its own "Schema "x"" table instead of one "List of schemas"
- the "(N rows)" footer is gone
- in non-quiet mode, a pattern with no match is now an error

I don't think changes to user-visible output can go into 15–18. Could this be split into two patches? 0001 would be a minimal escaping fix that can be backpatched, e.g. keying the footer off PQgetvalue(res, 0, 0) when exactly one row comes back. 0002 would be the per-schema display, master only, and would probably need its own discussion. psql-ref.sgml also still says matching schemas "are listed", so 0002 would need a doc update.

2. The no-match behaviour doesn't actually match \dt. The comment says "to be same as \dt", but listTables() prints the error and then returns true, while listSchemas() jumps to error_return and returns false. So under ON_ERROR_STOP, \dn nosuch now aborts a script where it used to print an empty table:

$ psql -v ON_ERROR_STOP=1 -c '\dn e_nosuch' -c 'select 1'; echo "exit=$?"
Did not find any schemas named "e_nosuch".
exit=1
$ psql -v ON_ERROR_STOP=1 -c '\dt e_nosuch' -c 'select 1'; echo "exit=$?"
Did not find any tables named "e_nosuch".
?column?
----------
1
(1 row)

exit=0

Also:
- the no-pattern message "Did not find any schemas" has no trailing period, unlike the other messages in describe.c
- in quiet mode a no-match still prints the old empty "List of schemas" table, so there are two output styles depending on -q
- pg_regress runs psql with -q, so none of the new error paths are exercised by the regression tests

3. \dn * now prints a separate table for every schema in the database, pg_catalog, pg_toast and information_schema included. With CSV output, a multi-match pattern now produces several header rows in a row, which would break anything that parses the output:
\pset format csv
\dn e_sch*
Name,Owner
e_sch1,surya
Name,Owner
e_sch2,surya
Name,Owner
e_sch3,surya
This is one more reason to keep the redesign out of the bug fix, or at least call it out explicitly.

4. Minor issues in describeOneSchemaDetails():
- The footer query has no separator after the literal. With ECHO_HIDDEN it shows up as WHERE n.nspname = 'e_sch1'ORDER BY 1;. It works, but a "\n" before ORDER BY would fix it.
- The footer query still uses appendPQExpBuffer() with no format arguments. Jim pointed this out for v1, but v2 only changed the ORDER BY line. appendPQExpBufferStr() would do.
- myopt.topt.default_footer = false is only set inside the sversion >= 150000 branch, so against an older server each schema table still ends with "(1 row)".

5. Tests:
- CREATE PUBLICATION pub_sch_1 FOR TABLES IN SCHEMA SCH_1 is unquoted, so it publishes sch_1, not "SCH_1". The expected output is correct, but a reader will probably assume the opposite. Could you add a comment, or use distinct names?
- There's no coverage for \dn+ with a pattern or for names containing a backslash.

Regards,
Surya Poondla

Hi Surya,

Thanks for your review comments!

I am not is there are any the backpatching requirements for this since
its a rare (nobody raised it before me) psql display-only bug.

OTOH, I recognise your point that I need to separate the minimal
bug-fix, from where I went beyond that and tampered with existing \dn
behaviour.

I am currently splitting the patches and plan to post something new next week...

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

Hi Surya.

Here is the v8-0001 patch.

This removes all the v7 behavioral changes I had made to \dn, and now
fixes only the original escape bug.

Note - It was necessary to change the SQL to use to_regnamespace() function.

Just doing:
...
"WHERE n.nspname = ");
appendStringLiteralConn(&buf, schemaname, pset.db);

does not work because \dn "HAS ' QUOTE" becomes '"HAS '' QUOTE"',
which solves the escape problem OK, but then the SQL won't find any
matching namespace with those double-quotes.

~~~

Meaniwhile, I did some more study about the behaviour changes that I
was previously attempting:

For "tables" there are options to use either \d or \dt

The \dt *always* give output as a LIST.
\dt = LIST of everything
\dt pattern = LIST of tables matching a pattern
\dt name = LIST of 1 table only

And, the \d can give additional object information in footers (e.g
what publication includes or excludes this table)

\d = Lists everything also with footer information
\d pattern = Lists of tables matching the pattern but also with footer
information
\d name = One table also with footer information

~

OTOH for "schemas" there is no choice which describe command you can
use. The \d does not work for schemas, and the \dn (schema equivalent
of \dt) always gives a LIST.

In v7, I was trying to give "\dn pattern" the ability to show footer
information, but as you correctly pointed out, that was breaking the
existing functionality of \dn for CSV lists.

I think I must abandon that idea because it seems \dn must either give
a LIST, or a list of objects with footers; it cannot try to
simulateously do both.

(The HEAD code does show footers for "\dn name" but because is using
string equality for the namespace name it can yeild only single
results, so the CSV is ok).

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

Hi Peter,

Thanks for v8. Cutting it down to the minimal fix makes sense to me, and I
agree the redesign is best dropped.

v8 no longer applies to master since Tom's 294d2196c7a, which replaced the
printfPQExpBuffer() label line with addQueryComment(). The conflict is
trivial (keep addQueryComment() and your new query). With that resolved it
builds cleanly, make check passes, and names with embedded ', ", \ and .
all show the right footer.

My concern is with to_regnamespace(). It parses the raw pattern as an
identifier, while the main query treats it as a pattern, and its behaviour
depends on the server version:

1. psql patterns may quote just part of a name (e.g. "FOO"bar matches
FOObar), but that isn't a valid identifier. On PG16+, to_regnamespace()
just returns NULL for it. On PG15 it throws "invalid name syntax" (see
REL_15_STABLE regproc.c, stringToQualifiedNameList()). So against a PG15
server, \dn "FOO"bar would fail entirely instead of listing the schema.
This matters even if the fix goes to master only, since the footer query
runs against any server version >= 150000. I haven't tried this against a
PG15 server; it's from reading the code.

2. On PG16+, to_regnamespace() goes through regnamespacein(), which also
accepts a numeric OID, so a numeric pattern resolves to the wrong schema:

CREATE SCHEMA "2200";
CREATE PUBLICATION e_pub_public FOR TABLES IN SCHEMA public;
\dn 2200
List of schemas
Name | Owner
------+-------
2200 | surya
Included in publications:
"e_pub_public"
That is the publication of public (OID 2200), not of schema "2200".

3. to_regnamespace() truncates to NAMEDATALEN-1 but the pattern match
doesn't, so an over-long pattern prints an empty list with a footer. With a
63-character schema e_long_xxx...x published by e_pub_long:

\dn e_long_xxx...xzzz
List of schemas
Name | Owner
------+-------
Included in publications:
"e_pub_long"
Contrived, but it's the same mismatch.

All three go away if the footer is keyed off the row the main query
actually returned, as I suggested earlier, rather than off the pattern:

if (pattern && PQntuples(res) == 1 && pset.sversion >= 150000)
{
...
"WHERE n.nspname = ");
appendStringLiteralConn(&buf, PQgetvalue(res, 0, 0), pset.db);
appendPQExpBufferStr(&buf, "\nORDER BY 1;");
That value is the real nspname, so the double-quote problem you described
doesn't come up. It doesn't depend on the server version, and the footer
always matches the displayed row. Like v8, it also stops master's habit of
showing a footer for a schema that wasn't listed. Today an unquoted \dn
E_Mixed prints an empty list with the publications of "E_Mixed". The one
new behaviour is that a wildcard pattern matching exactly one schema would
get a footer, which seems like the right result anyway.

Regarding tests, mixed case is what broke v4, and quoting/case folding is
exactly what the footer lookup has to get right, so a quoted mixed-case
name such as \dn "MySchema" with a publication would be worth adding, along
with a numeric name like the one above.

On backpatching, I'll leave that to the committer. It is a hard SQL error
for legitimate (unusual) schema names, and the fix is small.

Regards,
Surya Poondla