pg_dump and quoted identifiers

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
18 messages Options
Reply | Threaded
Open this post in threaded view
|

pg_dump and quoted identifiers

Thomas Kellerer
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?




--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|

Re: pg_dump and quoted identifiers

Pavel Stehule


2016-12-13 17:38 GMT+01:00 Thomas Kellerer <[hidden email]>:
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?

pg_dump -t '"Statuses"' postgres

Regards

Pavel




--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Reply | Threaded
Open this post in threaded view
|

Re: pg_dump and quoted identifiers

Tom Lane-2
In reply to this post by Thomas Kellerer
Thomas Kellerer <[hidden email]> 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


--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|

Re: pg_dump and quoted identifiers

David G Johnston
In reply to this post by Pavel Stehule
On Tue, Dec 13, 2016 at 9:43 AM, Pavel Stehule <[hidden email]> 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.

David J.
Reply | Threaded
Open this post in threaded view
|

Re: pg_dump and quoted identifiers

Thomas Kellerer
David G. Johnston schrieb am 13.12.2016 um 18:05:
> On Tue, Dec 13, 2016 at 9:43 AM, Pavel Stehule <[hidden email] <mailto:[hidden email]>>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
 



--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|

Re: pg_dump and quoted identifiers

Thomas Kellerer
In reply to this post by Tom Lane-2
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





--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|

Re: pg_dump and quoted identifiers

John McKown
In reply to this post by Thomas Kellerer
On Tue, Dec 13, 2016 at 11:24 AM, Thomas Kellerer <[hidden email]> wrote:
David G. Johnston schrieb am 13.12.2016 um 18:05:
On Tue, Dec 13, 2016 at 9:43 AM, Pavel Stehule <[hidden email] <mailto:[hidden email]>>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
 


--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

​pg_dump -t '"Statuses"' (that's " marks around the word Statuses and ' marks around that. On Fedora:

[tsh009@it-johnmckown-linux junk]$ pg_dump -t '"Status"'
--
-- PostgreSQL database dump
--

-- Dumped from database version 9.5.5
-- Dumped by pg_dump version 9.5.5

SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: Status; Type: TABLE; Schema: public; Owner: tsh009
--

CREATE TABLE "Status" (
    x integer
);


ALTER TABLE "Status" OWNER TO tsh009;

--
-- Data for Name: Status; Type: TABLE DATA; Schema: public; Owner: tsh009
--

COPY "Status" (x) FROM stdin;
1
\.


--
-- PostgreSQL database dump complete
--




--
Heisenberg may have been here.


Maranatha! <><
John McKown
Reply | Threaded
Open this post in threaded view
|

Re: pg_dump and quoted identifiers

Tom Lane-2
In reply to this post by Thomas Kellerer
Thomas Kellerer <[hidden email]> 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


--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|

Re: pg_dump and quoted identifiers

Thomas Kellerer
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)"





--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|

Re: pg_dump and quoted identifiers

Adrian Klaver-4
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
[hidden email]


--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|

Re: pg_dump and quoted identifiers

Thomas Kellerer
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 one of 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



--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|

Re: pg_dump and quoted identifiers

Adrian Klaver-4
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 one of 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
[hidden email]


--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|

Re: pg_dump and quoted identifiers

Tom Lane-2
In reply to this post by Thomas Kellerer
Thomas Kellerer <[hidden email]> 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


--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|

Re: pg_dump and quoted identifiers

Francisco Olarte
On Thu, Dec 15, 2016 at 4:20 PM, Tom Lane <[hidden email]> 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.


--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|

Re: pg_dump and quoted identifiers

Thomas Kellerer
In reply to this post by Tom Lane-2
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





--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|

Re: pg_dump and quoted identifiers

Jan de Visser-3
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?




--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|

Re: pg_dump and quoted identifiers

Tom Lane-2
In reply to this post by Thomas Kellerer
Thomas Kellerer <[hidden email]> 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


--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|

Re: pg_dump and quoted identifiers

Marc Mamin-2
In reply to this post by Thomas Kellerer

________________________________________
Von: [hidden email] [[hidden email]]&quot; im Auftrag von &quot;Thomas Kellerer [[hidden email]]
Gesendet: Donnerstag, 15. Dezember 2016 17:15
An: [hidden email]
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

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general