Thread: \connect case-folding change maybe not such a good idea

Peter, I'm having second thoughts about this change:

2001-09-02 19:52  petere

    * src/bin/psql/command.c: Parse the arguments of \connect as SQL
    identifiers, so that they expose the expected behavior in
    mixed-case situations.

    bug report from James Pattie, 2001-08-31

which you made in response to bug #435,
http://archives.postgresql.org/pgsql-bugs/2001-08/msg00174.php

AFAIR there wasn't a whole lot of discussion at the time.  Certainly
no one realized that this would have the effect of breaking pg_dump and
pg_dumpall for mixed-case database names and user names --- but it did.
Dump scripts used to not quote names in \connect commands.  That worked
correctly with 7.1 psql, and does not work now.

A few days ago I committed changes to pg_dump and pg_dumpall to make
them put double quotes around mixed-case names in \connect commands.
But I am wondering whether it wouldn't be a better idea to revert both
that change and the psql one.

Allow me to point out some context.  psql (and, I hope, all our other
command-line clients) does not case-fold database and user names when
they appear on a command line --- eg, psql -U JoeBlow JoesDatabase.
This is appropriate mainly because it's so dang painful to arrange for
double-quotes in a shell-command argument; you'd have to quote or escape
them, eg, psql -U '"JoeBlow"' '"JoesDatabase"'.  (IIRC, we used to
case-fold command-line arguments, but realized it was a bad move.)

So the definitional question is really "are psql's backslash commands
more like shell commands, or more like SQL commands?".  Put that way,
I don't think it's an entirely simple question.  You could certainly
make an argument that the parameters of \connect ought to be treated
the same as psql's command-line parameters.

Probably on balance the new behavior would make more sense, if we were
working in a green field.  But we are not.  In particular, we have a
serious problem with lack of forward compatibility of 7.1 dumps.  Yeah,
we can tell people to dump with the (not yet available) 7.2.1 pg_dump,
but that's cold comfort for someone who's already dumped and initdb'd
over his 7.1 installation.

I think the wisest course may be to revert to the 7.1 \connect behavior
and redefine that bug as user error.  (After all, he might be happy now
with "\connect BUGS", but he still won't be happy with "psql BUGS".)
What do you think?

            regards, tom lane

I said:
> I think the wisest course may be to revert to the 7.1 \connect behavior

There is another possibility, which is to migrate to the new behavior
over time.  I like having pg_dump quote the names in \connect, because
that gives us a shot at working with names that have embedded spaces.
We could leave pg_dump as it stands in CVS tip, and modify psql to
work as follows:
    1. Quoted names in \connect are taken as quoted identifiers.
    2. Unquoted names are taken literally (not downcased).
Point 2 would allow forwards compatibility of existing dump files,
while point 1 makes the new pg_dump behavior work.  After a release
or two we could migrate to regular SQL-identifier handling in \connect.

This'd require a little bit of uglification in psql (another option_type
for scan_option()) but it doesn't seem too terribly awful.

Comments?

            regards, tom lane

Tom Lane writes:

> I said:
> > I think the wisest course may be to revert to the 7.1 \connect behavior
>
> There is another possibility, which is to migrate to the new behavior
> over time.  I like having pg_dump quote the names in \connect, because
> that gives us a shot at working with names that have embedded spaces.
> We could leave pg_dump as it stands in CVS tip, and modify psql to
> work as follows:
>     1. Quoted names in \connect are taken as quoted identifiers.
>     2. Unquoted names are taken literally (not downcased).
> Point 2 would allow forwards compatibility of existing dump files,
> while point 1 makes the new pg_dump behavior work.  After a release
> or two we could migrate to regular SQL-identifier handling in \connect.

Fine with me.

--
Peter Eisentraut   peter_e@gmx.net