BUG #3795: pg_dump is mis-dumping columns containing line breaks
The following bug has been logged online:
Bug reference: 3795
Logged by: Lou Duchez
Email address: lou@paprikash.com
PostgreSQL version: 8.3 beta 3
Operating system: Windows 2003
Description: pg_dump is mis-dumping columns containing line breaks
Details:
Hello,
My copy of PostgreSQL has standard_conforming_strings set to "on", and when
I attempt to pg_dump tables that have strings containing CR/LFs, the data
dumps in a format that psql cannot then reload. The CR/LFs are preceded by
a backslash ("\") in the dump but are not themselves "escaped" -- so instead
of looking like this:
line 1\nline2\nline3
it looks like this:
line 1\
line 2\
line 3
And psql seems to be unable to handle data in that format.
"Lou Duchez" <lou@paprikash.com> writes:
My copy of PostgreSQL has standard_conforming_strings set to "on", and when
I attempt to pg_dump tables that have strings containing CR/LFs, the data
dumps in a format that psql cannot then reload.
It works fine here. Please provide a *complete* example of what you
are doing.
regards, tom lane
"Lou Duchez" <lou@paprikash.com> writes:
My copy of PostgreSQL has standard_conforming_strings set to "on", and when
I attempt to pg_dump tables that have strings containing CR/LFs, the data
dumps in a format that psql cannot then reload.It works fine here. Please provide a *complete* example of what you
are doing.regards, tom lane
Hello,
Here is a complete example. I have two Windows servers, one with PostgreSQL 8.2.5, the
other with 8.3 beta 3. Both have standard_conforming_strings set to "on". My "test"
database is UTF8 on each server. I perform the following steps on each server:
1) Using pgAdmin III version 1.8, I create a table in my "test" database:
CREATE TABLE testtable
(
column1 character varying(200) DEFAULT '',
PRIMARY KEY (column1)
) WITH (OIDS=FALSE);
2) Again using pgAdmin, I add two rows to the table:
insert into testtable (column1) values ('This
is
a
multi
row
string'),
('This
is
another
multi
row
string');
3) Then I run the following pg_dump command:
pg_dump -U postgres -E LATIN1 -t testtable testdatabase
The two versions of PostgreSQL produce slightly different results. 8.2.5 generates the
following "COPY" command:
COPY testtable (column1) FROM stdin;
This\nis\na\nmulti\nrow\nstring
This\nis\nanother\nmulti\nrow\nstring
\.
8.3 beta 3 generates the following "COPY" command:
COPY testtable (column1) FROM stdin;
This\
is\
a\
multi\
row\
string
This\
is\
another\
multi\
row\
string
\.
The 8.3 version, with the unescaped line breaks, confuses the heck out of psql.
lou@paprikash.com writes:
The two versions of PostgreSQL produce slightly different results.
Yeah, that's an intentional change.
The 8.3 version, with the unescaped line breaks, confuses the heck out
of psql.
Works fine for me --- I can load either dump file into either version of
psql.
Maybe your test case involves letting Windows mangle the line endings?
regards, tom lane
Em Sunday 02 December 2007 21:24:09 Tom Lane escreveu:
lou@paprikash.com writes:
The two versions of PostgreSQL produce slightly different results.
Yeah, that's an intentional change.
The 8.3 version, with the unescaped line breaks, confuses the heck out
of psql.Works fine for me --- I can load either dump file into either version of
psql.Maybe your test case involves letting Windows mangle the line endings?
Another things to pay attention: if the database is UTF-8, why is he
specifying LATIN1 on the command line?
--
Jorge Godoy <jgodoy@gmail.com>
I wrote:
Maybe your test case involves letting Windows mangle the line endings?
On reflection, I'm sure that's exactly what's happening. I now remember
that one of the reasons for the old dump behavior was to make COPY data
a bit safer against line-ending mangling. I've reverted this rather
ill-advised change in behavior, and added a note in the code to
hopefully prevent anyone from making the same mistake again.
regards, tom lane