reindexdb: Change quoting rule of --schema?
Hi all,
(Original author and Alvaro in CC, for comments)
reindexdb --schema has the idea of not quoting the schema given in
input of the command line. For example, if a schema uses upper-case
characters, the command could fail. Perhaps a fmtIdEnc() should be
applied, in the same fashion as --tablespace? This point has come
from Alvaro, during a recent review for a separate patch.
Here is the suggested change:
case REINDEX_SCHEMA:
- appendPQExpBufferStr(sql, name);
+ appendPQExpBufferStr(sql, fmtIdEnc(name, PQclientEncoding(conn)));
There may be a point in treating that as a bug, but that's also a
backward-incompatible change so perhaps that could just be left as-is.
The new quoting behavior feels upgraded to me, though, but perhaps I'm
just biased.
Comments and/or opinions are welcome.
Thanks,
--
Michael
On Sat, Nov 14, 2026 at 11:25:18AM +0900, Michael Paquier wrote:
reindexdb --schema has the idea of not quoting the schema given in
input of the command line. For example, if a schema uses upper-case
characters, the command could fail. Perhaps a fmtIdEnc() should be
applied, in the same fashion as --tablespace? This point has come
from Alvaro, during a recent review for a separate patch.Here is the suggested change: case REINDEX_SCHEMA: - appendPQExpBufferStr(sql, name); + appendPQExpBufferStr(sql, fmtIdEnc(name, PQclientEncoding(conn)));There may be a point in treating that as a bug, but that's also a
backward-incompatible change so perhaps that could just be left as-is.
The new quoting behavior feels upgraded to me, though, but perhaps I'm
just biased.
I think the change is mildly positive on balance. The rule would then be that
possibly-schema-qualified options like --table need interior quoting, but
single-name options don't. So a shell invocation would use
--table='"Embedded""QuoteNSP"."T"' but would use
--schema='Embedded"QuoteNSP'. I wouldn't back-patch it, though.
--- a/doc/src/sgml/ref/reindexdb.sgml +++ b/doc/src/sgml/ref/reindexdb.sgml @@ -210,6 +210,7 @@ PostgreSQL documentation <listitem> <para> Reindex <replaceable class="parameter">schema</replaceable> only. + (This name is processed as a double-quoted identifier.)
While this wording is copied from --tablespace, I think it unhelpfully implies
that "" inside the name will reduce to ", which is not the case. It would be
more precise to say that the argument is treated as a literal schema name, not
as an SQL identifier that needs parsing. (Passing the value through the shell
may still require quoting at that level.) Something like that.
Alternatively, perhaps say nothing. If someone passes a quoted name here,
they'll tend to get a clean error.
The --table and --index options have more acute need for docs, since their
parsing is more complex.
On Sat, Nov 14, 2026 at 10:34 AM Noah Misch <noah@leadboat.com> wrote:
On Sat, Nov 14, 2026 at 11:25:18AM +0900, Michael Paquier wrote:
reindexdb --schema has the idea of not quoting the schema given in
input of the command line. For example, if a schema uses upper-case
characters, the command could fail. Perhaps a fmtIdEnc() should be
applied, in the same fashion as --tablespace? This point has come
from Alvaro, during a recent review for a separate patch.Here is the suggested change: case REINDEX_SCHEMA: - appendPQExpBufferStr(sql, name); + appendPQExpBufferStr(sql, fmtIdEnc(name, PQclientEncoding(conn)));There may be a point in treating that as a bug, but that's also a
backward-incompatible change so perhaps that could just be left as-is.
The new quoting behavior feels upgraded to me, though, but perhaps I'm
just biased.I think the change is mildly positive on balance. The rule would then be that
possibly-schema-qualified options like --table need interior quoting, but
single-name options don't. So a shell invocation would use
--table='"Embedded""QuoteNSP"."T"' but would use
--schema='Embedded"QuoteNSP'. I wouldn't back-patch it, though.
+1 for the change, master only.
The one downside is that passing an already-quoted name like -S
'"Foo"' stops working, but I think that's acceptable for master.
Regards,
--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
On Wed, Nov 18, 2026 at 03:38:05PM -0800, Masahiko Sawada wrote:
On Sat, Nov 14, 2026 at 10:34 AM Noah Misch <noah@leadboat.com> wrote:
I think the change is mildly positive on balance. The rule would then be that
possibly-schema-qualified options like --table need interior quoting, but
single-name options don't. So a shell invocation would use
--table='"Embedded""QuoteNSP"."T"' but would use
--schema='Embedded"QuoteNSP'. I wouldn't back-patch it, though.+1 for the change, master only.
The one downside is that passing an already-quoted name like -S
'"Foo"' stops working, but I think that's acceptable for master.
I'd like to believe that close to nobody has the idea of using quoted
chracters in their schema names, but if they do it would burt. I'm
much more stressed about upper-case characters though, still my heart
tells me that as a HEAD-only change this is an improvement in the
long-run. Thanks for the input, both of you.
--
Michael
On 2026-Nov-20, Michael Paquier wrote:
I'd like to believe that close to nobody has the idea of using quoted
chracters in their schema names, but if they do it would burt. I'm
much more stressed about upper-case characters though, still my heart
tells me that as a HEAD-only change this is an improvement in the
long-run.
That's my impression as well.
It strikes me that we're pretty inconsistent about how to treat literal
names when passed as command line arguments. If you create schemas like
this
create schema "foo bar";
create table "foo bar"."this is a table" ();
then run tools such as vacuumdb, reindexdb, clusterdb, they will all
expect the names to be given with the SQL quotes, or fail:
$ reindexdb --table=foo\ bar.this\ is\ a\ table
reindexdb: error: query failed: ERROR: invalid name syntax
LINE 4: AND c.oid OPERATOR(pg_catalog.=) 'foo bar.this is a table'...
^
(Here, the name is fed exactly as given to cast to regclass, which fails
to interpret because of the unescaped spaces)
Then you could try to add escaped quotes:
0$ reindexdb --table=\"foo\ bar.this\ is\ a\ table\"
reindexdb: error: query failed: ERROR: relation "foo bar.this is a table" does not exist
LINE 4: AND c.oid OPERATOR(pg_catalog.=) '"foo bar.this is a table...
^
But this one also fails because the whole string is processed as a
single identifier; so the bit before the dot is not a schema name. The
lookup then fails.
What you can do is quote the schema name separately from the relation
name:
$ reindexdb --table=\"foo\ bar\".\"this\ is\ a\ table\"
NOTICE: table "this is a table" has no indexes to reindex
But I think this is borderline ridiculous, even if you can claim that
this is Technically Correct (it's often said that being technically
correct is the worst kind of correct). By contrast, if you try the same
thing with pg_dump, it's a lot better:
$ pg_dump --table=foo\ bar.this\ is\ a\ table
<outputs the table>
And it's even nicer with
$ pg_dump --table="foo bar.this is a table"
because you can see the CREATE TABLE line has the correct quotes
which are slightly different from what you used in the command line:
CREATE TABLE "foo bar"."this is a table" (
though the user must realize that the quotes they gave in the command
line argument are not seen by pg_dump at all! -- so this also works, and
it's exactly the same although it looks different:
$ pg_dump --table="foo bar"."this is a table"
BTW I don't know if I'm alone in this but sometimes I type command lines
like
git commit -m typo\ fix
which is probably an odd thing to do. But at times I do like
git commit -m a\ short\ one" oh no I decided to type longer"
when I tire of backslash-escaping spaces in the middle of it.
(But yeah, IMO uppercase should be preserved as given in the command
line without requiring quotes to be explicitly present.)
Anyway -- with this patch you're changing the behavior of that one
argument of reindexdb, but I wonder if we should look at the other tools
in src/bin/scripts (and elsewhere!?) more holistically and make them all
behave not only in a consistent way, but also in a sensible, useful way.
I do agree that this should be changed in master only, though, because
it's a behavior change.
--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
"It takes less than 2 seconds to get to 78% complete; that's a good sign.
A few seconds later it's at 90%, but it seems to have stuck there. Did
somebody make percentages logarithmic while I wasn't looking?"
http://smylers.hates-software.com/2005/09/08/1995c749.html