Re: reindexdb: Change quoting rule of --schema?

From: Alvaro Herrera <alvherre(at)kurilemu(dot)de>
To: Michael Paquier <michael(at)paquier(dot)xyz>
Cc: Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com>, Noah Misch <noah(at)leadboat(dot)com>, Postgres hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: reindexdb: Change quoting rule of --schema?
Date: 2026-11-20 14:41:58
Message-ID: awBXOIB-WBAmptU7@alvherre.pgsql
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Manu 2026-11-20 14:43:29 Re: Progress reporting: a debug trace and a test framework
Previous Message Manu 2026-11-20 14:39:41 Re: O(N^3) planning time in choose_plan_name() since 8c49a484e8e