Re: BUG #4901: Column name "window" breaks pg_dump/pg_restore

The following bug has been logged online:

Bug reference: 4901
Logged by: Steve Purcell
Email address: steve(at)sanityinc(dot)com
PostgreSQL version: 8.4.0
Operating system: Linux (Debian Unstable)
Description: Column name "window" breaks pg_dump/pg_restore
Details:

The DB schema for openx (openx.org) contains a table with a column called
"window", which now causes a syntax error if unquoted. pg_dump doesn't
quote the column, so pg_restore/psql fail on the pg_dump output. Here's
what pg_dump spits out for the table:

CREATE TABLE ox_data_bkt_a (
server_conv_id bigint NOT NULL,
server_ip character varying(16) DEFAULT ''::character varying NOT NULL,
tracker_id integer NOT NULL,
date_time timestamp without time zone,
action_date_time timestamp without time zone,
creative_id integer NOT NULL,
zone_id integer NOT NULL,
ip_address character varying(16) DEFAULT ''::character varying NOT
NULL,
action integer,
window integer,
status integer
);

....

COPY ox_data_bkt_a (server_conv_id, server_ip, tracker_id, date_time,
action_date_time, creative_id, zone_id, ip_a
ddress, action, window, status) FROM stdin;

"Steve Purcell" <steve(at)sanityinc(dot)com> writes:
> The DB schema for openx (openx.org) contains a table with a column called
> "window", which now causes a syntax error if unquoted. pg_dump doesn't
> quote the column, so pg_restore/psql fail on the pg_dump output.

This is one of the reasons why it's recommended that you use the later
version's pg_dump to perform a cross-version dump and restore. 8.4's
pg_dump knows that "window" is a reserved word, but 8.3's could hardly
be expected to.

regards, tom lane

Thanks for the quick reply, Tom. That makes sense, and yes, that does
appear to be the problem.

I thought that I *was* using the newer pg_dump, but I'm doing this on
Debian:

# su - postgres -c "env PGCLUSTER=8.3/main pg_dump openx|env
PGCLUSTER=8.4/main pg_restore -d template1 -C"

And the debian pg_wrapper script ends up selecting the 8.3 pg_dump
binary in the first case. I'll have to figure out if there's even a
way to execute the newer pg_dump against the old database, which I
expect to be fiddly.

Thanks again, and sorry for the noise.

-Steve

On 5 Jul 2009, at 17:55, Tom Lane wrote:

> "Steve Purcell" <steve(at)sanityinc(dot)com> writes:
>> The DB schema for openx (openx.org) contains a table with a column
>> called
>> "window", which now causes a syntax error if unquoted. pg_dump
>> doesn't
>> quote the column, so pg_restore/psql fail on the pg_dump output.
>
> This is one of the reasons why it's recommended that you use the later
> version's pg_dump to perform a cross-version dump and restore. 8.4's
> pg_dump knows that "window" is a reserved word, but 8.3's could hardly
> be expected to.
>
> regards, tom lane
>

Steve Purcell <steve(at)sanityinc(dot)com> writes:

> # su - postgres -c "env PGCLUSTER=8.3/main pg_dump openx|env
> PGCLUSTER=8.4/main pg_restore -d template1 -C"
>
> And the debian pg_wrapper script ends up selecting the 8.3 pg_dump binary in
> the first case. I'll have to figure out if there's even a way to execute
> the newer pg_dump against the old database, which I expect to be
> fiddly.

I guess you could trick the binary choice then force another server
choice :
pg_dump --cluster 8.4/main -p <port of 8.3> ...

But I'd simply go with:
/usr/lib/postgresql/8.4/bin/pg_dump <options to target 8.3>

For people unaware of debian way of supporting more than one major
version and cluster, all PostgreSQL user binaries in /usr/bin are a link
to their pg_wrapper, which support the --cluster 8.X/clustername option
and will choose the right versionned binary and port number etc.

Regards,
--
dim

On 2009-07-05, Steve Purcell <steve(at)sanityinc(dot)com> wrote:
> Thanks for the quick reply, Tom. That makes sense, and yes, that does
> appear to be the problem.
>
> I thought that I *was* using the newer pg_dump, but I'm doing this on
> Debian:
>
> # su - postgres -c "env PGCLUSTER=8.3/main pg_dump openx|env
> PGCLUSTER=8.4/main pg_restore -d template1 -C"
>
> And the debian pg_wrapper script ends up selecting the 8.3 pg_dump
> binary in the first case. I'll have to figure out if there's even a
> way to execute the newer pg_dump against the old database, which I
> expect to be fiddly.

easiest is to bypass pg_wrapper

/usr/lib/postgresql/8.4/bin/pg_dump -p 5432

I have raised this with debian.

Terrific, thanks for that. I'd figured out the first trick you
suggested, but the second is much clearer.

-Steve

On 6 Jul 2009, at 12:28, Dimitri Fontaine wrote:

> Steve Purcell <steve(at)sanityinc(dot)com> writes:
>
>> # su - postgres -c "env PGCLUSTER=8.3/main pg_dump openx|env
>> PGCLUSTER=8.4/main pg_restore -d template1 -C"
>>
>> And the debian pg_wrapper script ends up selecting the 8.3 pg_dump
>> binary in
>> the first case. I'll have to figure out if there's even a way to
>> execute
>> the newer pg_dump against the old database, which I expect to be
>> fiddly.
>
> I guess you could trick the binary choice then force another server
> choice :
> pg_dump --cluster 8.4/main -p <port of 8.3> ...
>
> But I'd simply go with:
> /usr/lib/postgresql/8.4/bin/pg_dump <options to target 8.3>
>
> For people unaware of debian way of supporting more than one major
> version and cluster, all PostgreSQL user binaries in /usr/bin are a
> link
> to their pg_wrapper, which support the --cluster 8.X/clustername
> option
> and will choose the right versionned binary and port number etc.
>
> Regards,
> --
> dim
>