Thread: pg_dump case folding bug

If PostgreSQL failed to compile on your computer or you found a bug that
is likely to be specific to one platform then please fill out this form
and e-mail it to pgsql-ports@postgresql.org.

To report any other bug, fill out the form below and e-mail it to
pgsql-bugs@postgresql.org.

If you not only found the problem but solved it and generated a patch
then e-mail it to pgsql-patches@postgresql.org instead.  Please use the
command "diff -c" to generate the patch.

You may also enter a bug report at http://www.postgresql.org/ instead of
e-mail-ing this form.

============================================================================
                        POSTGRESQL BUG REPORT TEMPLATE
============================================================================


Your name       : Russell Smith
Your email address  : As From address


System Configuration
---------------------
  Architecture (example: Intel Pentium)     : AMD 2000XP

  Operating System (example: Linux 2.4.18)  : Gentoo Linux 2.4.20-r9

  PostgreSQL version (example: PostgreSQL-8.0):   PostgreSQL-8.0-beta4

  Compiler used (example:  gcc 2.95.2)      : gcc (GCC) 3.3.3 20040412 (Gentoo Linux 3.3.3-r6, ssp-3.3.2-2, pie-8.7.6)


Please enter a FULL description of your problem:
------------------------------------------------
pg_dump -i -h '10.0.0.5' -p '5432' -s 'test' -t '"Test"'

pg_dump does not fold case, and quote table and schema names correctly.
the above line will dump the table named "Test"

pg_dump -i -h '10.0.0.5' -p '5432' -s 'test' -t 'Test'
the above will dump the table named Test

I believe the correct output from 1 should be to dump the table named Test.
and correct output from 2 should dump the table named test


Please describe a way to repeat the problem.   Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------
Reproduction from commands in description.


If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------
Attempted patches attached.  Most code taken from libpq and modified for pg_dump.

Russell Smith <mr-russ@pws.com.au> writes:
> pg_dump does not fold case, and quote table and schema names correctly.

This is not a bug; it is a behavior we deliberately adopted years ago,
after unsuccessful experiments with behavior like what you propose.

The rule is that names appearing on a shell command line are taken
literally, without dequoting or case folding.  Yes, this is inconsistent
with the behavior if the same string were entered within an SQL context,
but then again the command line isn't an SQL context.

The main reason for this position is that the shell imposes its own
quoting rules that we can't avoid, and these rules are not very
compatible with the SQL identifier quoting rules.  Do you *really*
want to have to type '"Test"' when you could just write Test ?

Even more to the point, the only argument in favor of adding code to do
it like this is to try to make the shell command line context just like
the SQL context, but *you can't make it so*.  If you could make "Test"
on the command line work just like "Test" in SQL it'd be great ... but
you can't because the shell will strip the double quotes before you ever
see them.

We went around a few times on this, but eventually decided it was
unhelpful to try to emulate the SQL quoting behavior.

BTW, this behavior is consistent across all our command-line tools;
if we did want to change it it'd affect much more than just pg_dump.

            regards, tom lane

On Sat, 13 Nov 2004 04:05 pm, Tom Lane wrote:
> Russell Smith <mr-russ@pws.com.au> writes:
> > pg_dump does not fold case, and quote table and schema names correctly.
>
> This is not a bug; it is a behavior we deliberately adopted years ago,
> after unsuccessful experiments with behavior like what you propose.
>
> The rule is that names appearing on a shell command line are taken
> literally, without dequoting or case folding.  Yes, this is inconsistent
> with the behavior if the same string were entered within an SQL context,
> but then again the command line isn't an SQL context.
>
> The main reason for this position is that the shell imposes its own
> quoting rules that we can't avoid, and these rules are not very
> compatible with the SQL identifier quoting rules.  Do you *really*
> want to have to type '"Test"' when you could just write Test ?
>
> Even more to the point, the only argument in favor of adding code to do
> it like this is to try to make the shell command line context just like
> the SQL context, but *you can't make it so*.  If you could make "Test"
> on the command line work just like "Test" in SQL it'd be great ... but
> you can't because the shell will strip the double quotes before you ever
> see them.
>
> We went around a few times on this, but eventually decided it was
> unhelpful to try to emulate the SQL quoting behavior.
>
Thanks Tom, I found this out after talking to the pg_dump developers.
I had discussed with Neil if this should be implement and proposed I
have a go at it.  I have since seen otherwise and identified the bug
in phppgadmin causing the problem.

Thanks for the detailed answer.


> BTW, this behavior is consistent across all our command-line tools;
> if we did want to change it it'd affect much more than just pg_dump.
>
>    regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
>