Thread: pg_dump again

pg_dump again

From
Mikhail Terekhov
Date:
Hi,

It seems that pg_dump (in 6.4.2) still has a problem when quoting
\ and ' when these characters appears together.
Consider the following example:


terekhov=> create table test (i int, t text);
CREATE
terekhov=> insert into test values (1,'');
INSERT 19066 1
terekhov=> insert into test values (2,'abcd');
INSERT 19067 1
terekhov=> insert into test values (3,'\\');
INSERT 19068 1
terekhov=> insert into test values (4,'\'');
INSERT 19069 1
terekhov=> insert into test values (5,'\\\'');
INSERT 19070 1
terekhov=> select * from test;
i|t   
-+----
1|    
2|abcd
3|\   
4|'   
5|\'  
(5 rows)

terekhov=> \q
terekhov(575)~>pg_dump -d -D -f ter.db terekhov
terekhov(576)~>cat ter.db
CREATE TABLE "test" ("i" int4,"t" text);
INSERT INTO "test" ("i","t") values (1,'');
INSERT INTO "test" ("i","t") values (2,'abcd');
INSERT INTO "test" ("i","t") values (3,'\');
INSERT INTO "test" ("i","t") values (4,'''');
INSERT INTO "test" ("i","t") values (5,'\''');                                         ^                 wrong quoting
here-----|
 

terekhov(577)~>psql 
Welcome to the POSTGRESQL interactive sql monitor: Please read the file COPYRIGHT for copyright terms of POSTGRESQL
  type \? for help on slash commands  type \q to quit  type \g or terminate with semicolon to execute queryYou are
currentlyconnected to the database: terekhov
 

terekhov=> alter table test rename to test0;
RENAME
terekhov=> \q
terekhov(578)~>psql -e <ter.db
CREATE TABLE "test" ("i" int4,"t" text);
QUERY: CREATE TABLE "test" ("i" int4,"t" text);
CREATE
INSERT INTO "test" ("i","t") values (1,'');
QUERY: INSERT INTO "test" ("i","t") values (1,'');
INSERT 19082 1
INSERT INTO "test" ("i","t") values (2,'abcd');
QUERY: INSERT INTO "test" ("i","t") values (2,'abcd');
INSERT 19083 1
INSERT INTO "test" ("i","t") values (3,'\');
INSERT INTO "test" ("i","t") values (4,'''');
INSERT INTO "test" ("i","t") values (5,'\''');\?           -- help\a           -- toggle field-alignment (currently
on)\C[<captn>] -- set html3 caption (currently '')\connect <dbname|-> <user> -- connect to new database (currently
'terekhov')\copytable {from | to} <fname>\d [<table>] -- list tables and indices, columns in <table>, or * for all\da
      -- list aggregates\dd [<object>]- list comment for table, field, type, function, or operator.\df          -- list
functions\di         -- list only indices\do          -- list operators\ds          -- list only sequences\dS
--list system tables and indexes\dt          -- list only tables\dT          -- list types\e [<fname>] -- edit the
currentquery buffer or <fname>\E [<fname>] -- edit the current query buffer or <fname>, and execute\f [<sep>]   --
changefield separater (currently '|')\g [<fname>] [|<cmd>] -- send query to backend [and results in <fname> or pipe]\h
[<cmd>]  -- help on syntax of sql commands, * for all commands\H           -- toggle html3 output (currently off)\i
<fname>  -- read and execute queries from filename\l           -- list all databases\m           -- toggle monitor-like
tabledisplay (currently off)\o [<fname>] [|<cmd>] -- send all query results to stdout, <fname>, or pipe\p           --
printthe current query buffer\q           -- quit\r           -- reset(clear) the query buffer\s [<fname>] -- print
historyor save it in <fname>\t           -- toggle table headings and row count (currently on)\T [<html>]  -- set
html3.0<table ...> options (currently '')\x           -- toggle expanded output (currently off)\w <fname>   -- output
currentbuffer to a file\z           -- list current grant/revoke permissions\! [<cmd>]   -- shell escape or command
 
EOF

Regards,
Mikhail Terekhov


Re: [HACKERS] pg_dump again

From
Tom Lane
Date:
Mikhail Terekhov <terekhov@emc.com> writes:
> It seems that pg_dump (in 6.4.2) still has a problem when quoting
> \ and ' when these characters appears together.
> Consider the following example:
> ... snip ...
> terekhov(575)~>pg_dump -d -D -f ter.db terekhov

Ah, I see the problem: pg_dump's -d/-D option doesn't do quoting of
special characters properly --- in fact, the only thing it does do
is double-up single quote marks.  My testing was without that option ---
without -d or -D, pg_dump uses COPY, which quotes everything properly.
This is probably why most people weren't seeing a problem.

I'll put this on my to-do list for 6.5, but in the meantime, I'd suggest
not using -d or -D... a dump script with one INSERT per table row is
horribly slow compared to COPY anyway...
        regards, tom lane


Re: [HACKERS] pg_dump again

From
Bruce Momjian
Date:
Tom, is this fixed?



> Mikhail Terekhov <terekhov@emc.com> writes:
> > It seems that pg_dump (in 6.4.2) still has a problem when quoting
> > \ and ' when these characters appears together.
> > Consider the following example:
> > ... snip ...
> > terekhov(575)~>pg_dump -d -D -f ter.db terekhov
> 
> Ah, I see the problem: pg_dump's -d/-D option doesn't do quoting of
> special characters properly --- in fact, the only thing it does do
> is double-up single quote marks.  My testing was without that option ---
> without -d or -D, pg_dump uses COPY, which quotes everything properly.
> This is probably why most people weren't seeing a problem.
> 
> I'll put this on my to-do list for 6.5, but in the meantime, I'd suggest
> not using -d or -D... a dump script with one INSERT per table row is
> horribly slow compared to COPY anyway...
> 
>             regards, tom lane
> 
> 


--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


Re: [HACKERS] pg_dump again

From
Tom Lane
Date:
Bruce Momjian <maillist@candle.pha.pa.us> writes:
> Tom, is this fixed?
>> Mikhail Terekhov <terekhov@emc.com> writes:
>>>> It seems that pg_dump (in 6.4.2) still has a problem when quoting
>>>> \ and ' when these characters appears together.

Yes, I fixed that.
        regards, tom lane