Re: 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(at)paprikash(dot)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(at)paprikash(dot)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(at)paprikash(dot)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(at)paprikash(dot)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(at)paprikash(dot)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(at)gmail(dot)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