Thread: [GENERAL] pg_dump and quoted identifiers

Inspired by this question:

   http://dba.stackexchange.com/q/158044/1822

I tried that for myself, and it seems that pg_dump indeed can not parse quoted identifiers:

    psql (9.6.1)
    Type "help" for help.

    postgres=# create table "Statuses" (id integer);
    CREATE TABLE
    postgres=# \q

    -bash-4.1$ pg_dump -d postgres -t "Statuses"
    pg_dump: no matching tables were found

    -bash-4.1$ pg_dump -d postgres -t '"Statuses"'
    pg_dump: no matching tables were found

    -bash-4.1$ pg_dump -d postgres -t 'public."Statuses"'
    pg_dump: no matching tables were found

Running 9.6.1 on CentOS 6 but under Windows this is the same.

Any ideas?

Thomas Kellerer <spam_eater@gmx.net> writes:
>     postgres=# create table "Statuses" (id integer);
>     CREATE TABLE

>     -bash-4.1$ pg_dump -d postgres -t "Statuses"
>     pg_dump: no matching tables were found

This is expected since those quotes are eaten by the shell.

>     -bash-4.1$ pg_dump -d postgres -t '"Statuses"'
>     pg_dump: no matching tables were found

>     -bash-4.1$ pg_dump -d postgres -t 'public."Statuses"'
>     pg_dump: no matching tables were found

These cases work for me.  Maybe your shell is doing something weird
with the quotes?

            regards, tom lane

David G. Johnston schrieb am 13.12.2016 um 18:05:
> On Tue, Dec 13, 2016 at 9:43 AM, Pavel Stehule <pavel.stehule@gmail.com <mailto:pavel.stehule@gmail.com>>wrote:
>
>
>     pg_dump -t '"Statuses"' postgres
>
>
> ​To elaborate - your shell was removing the double-quotes. You need
> make it so after shell processing the double-quotes remain. Wrapping
> the double-quote string in single-quotes should do it.

That was one of the options I tried, but that neither worked on Linux (using bash) nor on Windows

Thomas

Tom Lane schrieb am 13.12.2016 um 18:02:
>>     -bash-4.1$ pg_dump -d postgres -t '"Statuses"'
>>     pg_dump: no matching tables were found
>
>>     -bash-4.1$ pg_dump -d postgres -t 'public."Statuses"'
>>     pg_dump: no matching tables were found
>
> These cases work for me.  Maybe your shell is doing something weird
> with the quotes?

Hmm, that's the default bash from CentOS 6 (don't know the exact version)

Thomas

Thomas Kellerer <spam_eater@gmx.net> writes:
> Tom Lane schrieb am 13.12.2016 um 18:02:
>> These cases work for me.  Maybe your shell is doing something weird
>> with the quotes?

> Hmm, that's the default bash from CentOS 6 (don't know the exact version)

I'm using bash from current RHEL6, should be the same.

I'm suspicious that you're not actually typing plain-ASCII single and
double quotes, but some fancy curly quote character.

            regards, tom lane

Tom Lane schrieb am 13.12.2016 um 19:35:
>>> These cases work for me.  Maybe your shell is doing something weird
>>> with the quotes?
>
>> Hmm, that's the default bash from CentOS 6 (don't know the exact version)
>
> I'm using bash from current RHEL6, should be the same.
>
> I'm suspicious that you're not actually typing plain-ASCII single and
> double quotes, but some fancy curly quote character.

Definitely not. I typed this manually on the command line using Putty

Bash version is "GNU bash, version 4.1.2(1)-release (x86_64-redhat-linux-gnu)"

On 12/13/2016 11:18 PM, Thomas Kellerer wrote:
> Tom Lane schrieb am 13.12.2016 um 19:35:
>>>> These cases work for me.  Maybe your shell is doing something weird
>>>> with the quotes?
>>
>>> Hmm, that's the default bash from CentOS 6 (don't know the exact version)
>>
>> I'm using bash from current RHEL6, should be the same.
>>
>> I'm suspicious that you're not actually typing plain-ASCII single and
>> double quotes, but some fancy curly quote character.
>
> Definitely not. I typed this manually on the command line using Putty

So you are reaching the Bash shell via Putty on a Windows machine, correct?

So have you tried the answer from the SO question?:

"\"Statuses\""

>
> Bash version is "GNU bash, version 4.1.2(1)-release (x86_64-redhat-linux-gnu)"
>
>
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Adrian Klaver schrieb am 14.12.2016 um 15:32:
>>> I'm suspicious that you're not actually typing plain-ASCII single and
>>> double quotes, but some fancy curly quote character.
>>
>> Definitely not. I typed this manually on the command line using Putty
>
> So you are reaching the Bash shell via Putty on a Windows machine, correct?

Correct.

> So have you tried the answer from the SO question?:
>
> "\"Statuses\""

Still doesn't work:

  -bash-4.1$ pg_dump -d postgres -t "\"Statuses\""
  pg_dump: no matching tables were found

Must apparently be something strange with the bash on that server.

It's not really important, as we never use quoted identifiers where I work anyway (and the above problem is precisely
oneof the reasons).  

I was just curious what the correct solution would be, but apparently there is no single answer and it depends on the
environment.

Thomas

On 12/14/2016 11:37 PM, Thomas Kellerer wrote:
> Adrian Klaver schrieb am 14.12.2016 um 15:32:
>>>> I'm suspicious that you're not actually typing plain-ASCII single and
>>>> double quotes, but some fancy curly quote character.
>>>
>>> Definitely not. I typed this manually on the command line using Putty
>>
>> So you are reaching the Bash shell via Putty on a Windows machine, correct?
>
> Correct.
>
>> So have you tried the answer from the SO question?:
>>
>> "\"Statuses\""
>
> Still doesn't work:
>
>   -bash-4.1$ pg_dump -d postgres -t "\"Statuses\""
>   pg_dump: no matching tables were found
>
> Must apparently be something strange with the bash on that server.

Have you tried using using something other then Putty to connect to the
machine and/or from a non-Windows machine?

>
> It's not really important, as we never use quoted identifiers where I work anyway (and the above problem is precisely
oneof the reasons). 
>
> I was just curious what the correct solution would be, but apparently there is no single answer and it depends on the
environment.
>
> Thomas
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Thomas Kellerer <spam_eater@gmx.net> writes:
> Adrian Klaver schrieb am 14.12.2016 um 15:32:
>> So have you tried the answer from the SO question?:

> Still doesn't work:
>   -bash-4.1$ pg_dump -d postgres -t "\"Statuses\""
>   pg_dump: no matching tables were found

Hmm.  It might shed some light if you put "echo" in front of that
to see what gets printed:

$ echo pg_dump -d postgres -t "\"Statuses\""
pg_dump -d postgres -t "Statuses"

            regards, tom lane

On Thu, Dec 15, 2016 at 4:20 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
...
> Hmm.  It might shed some light if you put "echo" in front of that
> to see what gets printed:
>
> $ echo pg_dump -d postgres -t "\"Statuses\""
> pg_dump -d postgres -t "Statuses"

Also, when having strange issues, I've found the combo

echo <original command> | od -tx1 -tc

very useful, this help rule out potential fancy quotes pointed previously


Francisco Olarte.

Tom Lane schrieb am 15.12.2016 um 16:20:
>> Still doesn't work:
>>   -bash-4.1$ pg_dump -d postgres -t "\"Statuses\""
>>   pg_dump: no matching tables were found
>
> Hmm.  It might shed some light if you put "echo" in front of that
> to see what gets printed:
>
> $ echo pg_dump -d postgres -t "\"Statuses\""
> pg_dump -d postgres -t "Statuses"

Same here:

  -bash-4.1$ echo pg_dump -d postgres -t "\"Statuses\""
  pg_dump -d postgres -t "Statuses"


> Also, when having strange issues, I've found the combo
>
> echo <original command> | od -tx1 -tc
>
> very useful, this help rule out potential fancy quotes pointed previously


    -bash-4.1$ echo pg_dump -d postgres -t "\"Statuses\"" | od -tx1 -tc
    0000000  70  67  5f  64  75  6d  70  20  2d  64  20  70  6f  73  74  67
              p   g   _   d   u   m   p       -   d       p   o   s   t   g
    0000020  72  65  73  20  2d  74  20  22  53  74  61  74  75  73  65  73
              r   e   s       -   t       "   S   t   a   t   u   s   e   s
    0000040  22  0a
              "  \n
    0000042

On Thursday, December 15, 2016 5:15:44 PM EST Thomas Kellerer wrote:
> Tom Lane schrieb am 15.12.2016 um 16:20:
> >> Still doesn't work:
> >>   -bash-4.1$ pg_dump -d postgres -t "\"Statuses\""
> >>   pg_dump: no matching tables were found
> >
> > Hmm.  It might shed some light if you put "echo" in front of that
> > to see what gets printed:
> >
> > $ echo pg_dump -d postgres -t "\"Statuses\""
> > pg_dump -d postgres -t "Statuses"
>
[snip]

Crazy guess: Is pg_dump shadowed by a (maybe distro provided) script that eats
your quotes?

Thomas Kellerer <spam_eater@gmx.net> writes:
> Tom Lane schrieb am 15.12.2016 um 16:20:
>> Hmm.  It might shed some light if you put "echo" in front of that
>> to see what gets printed:
>> $ echo pg_dump -d postgres -t "\"Statuses\""
>> pg_dump -d postgres -t "Statuses"

> Same here:
>   -bash-4.1$ echo pg_dump -d postgres -t "\"Statuses\""
>   pg_dump -d postgres -t "Statuses"

So no light there ... next step would be to set log_statement = all
and see what pg_dump's last few queries before failing are.
I see this with correct quoting:

2016-12-15 12:06:15.211 EST [693] LOG:  statement: SELECT c.oid
    FROM pg_catalog.pg_class c
         LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
    WHERE c.relkind in ('r', 'S', 'v', 'm', 'f', 'P')
      AND c.relname ~ '^(Statuses)$'
      AND pg_catalog.pg_table_is_visible(c.oid)

and this if I omit quotes:

2016-12-15 12:07:19.735 EST [741] LOG:  statement: SELECT c.oid
    FROM pg_catalog.pg_class c
         LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
    WHERE c.relkind in ('r', 'S', 'v', 'm', 'f', 'P')
      AND c.relname ~ '^(statuses)$'
      AND pg_catalog.pg_table_is_visible(c.oid)

(This test is on HEAD; I suppose the 'P' case is pretty new...)

            regards, tom lane

________________________________________
Von: pgsql-general-owner@postgresql.org [pgsql-general-owner@postgresql.org]" im Auftrag von "Thomas Kellerer
[spam_eater@gmx.net]
Gesendet: Donnerstag, 15. Dezember 2016 17:15
An: pgsql-general@postgresql.org
Betreff: Re: [GENERAL] pg_dump and quoted identifiers

Tom Lane schrieb am 15.12.2016 um 16:20:
>> Still doesn't work:
>>   -bash-4.1$ pg_dump -d postgres -t "\"Statuses\""
>>   pg_dump: no matching tables were found
>
> Hmm.  It might shed some light if you put "echo" in front of that
> to see what gets printed:
>
> $ echo pg_dump -d postgres -t "\"Statuses\""
> pg_dump -d postgres -t "Statuses"

Hello,

Have you checked if the problem is limited to pg_dump ?
Maybe you're facing a trivial mistake, like a space in the table name at creation time or a wrong database...
psql -d postgres -c "select * from \"Statuses\" "

regards,
Marc Mamin