Cannot dump 8.4.8 database using later versions

Severity: normal

Versions tested: 9.0.4 and 9.1.1
Installed from packages on Ubuntu

Reproduceability: 100% on this server
have not tried a test case yet

Steps:

1. large running 8.4.8 PostgreSQL database

2. /usr/lib/postgresql/9.1/bin/pg_dump -Fc -v -f test.dump dbname

3. dumps all table etc definitions correctly. then:

pg_dump: saving database definition
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR: NEW used in query that is
not in a rule
LINE 1: ...END AS min_value, cache_value, is_cycled, is_called from new
^
pg_dump: The command was: SELECT sequence_name, start_value, last_value,
increment_by, CASE WHEN increment_by > 0 AND max_value =
9223372036854775807 THEN NULL WHEN increment_by < 0 AND max_value =
-1 THEN NULL ELSE max_value END AS max_value, CASE WHEN
increment_by > 0 AND min_value = 1 THEN NULL WHEN increment_by < 0
AND min_value = -9223372036854775807 THEN NULL ELSE min_value END
AS min_value, cache_value, is_cycled, is_called from new

(yes, that is the entire query, I checked the logs)

... somehow the query to dump the sequences is getting mangled. Any
clue how?

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

Josh Berkus wrote:
> Severity: normal
>
> Versions tested: 9.0.4 and 9.1.1
> Installed from packages on Ubuntu
>
> Reproduceability: 100% on this server
> have not tried a test case yet
>
> Steps:
>
> 1. large running 8.4.8 PostgreSQL database
>
> 2. /usr/lib/postgresql/9.1/bin/pg_dump -Fc -v -f test.dump dbname
>
> 3. dumps all table etc definitions correctly. then:
>
> pg_dump: saving database definition
> pg_dump: SQL command failed
> pg_dump: Error message from server: ERROR: NEW used in query that is
> not in a rule
> LINE 1: ...END AS min_value, cache_value, is_cycled, is_called from new
> ^
> pg_dump: The command was: SELECT sequence_name, start_value, last_value,
> increment_by, CASE WHEN increment_by > 0 AND max_value =
> 9223372036854775807 THEN NULL WHEN increment_by < 0 AND max_value =
> -1 THEN NULL ELSE max_value END AS max_value, CASE WHEN
> increment_by > 0 AND min_value = 1 THEN NULL WHEN increment_by < 0
> AND min_value = -9223372036854775807 THEN NULL ELSE min_value END
> AS min_value, cache_value, is_cycled, is_called from new
>
> (yes, that is the entire query, I checked the logs)
>
> ... somehow the query to dump the sequences is getting mangled. Any
> clue how?

Seems you have a sequence called "new"; seems we don't handle that
well.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

> Seems you have a sequence called "new"; seems we don't handle that
> well.

Hmmm ... yes, you're correct. Idiot users.

Interestingly, the sequence is no problem until 9.0. 8.4 handled it
fine. I'd guess this is another example of where merging in plpgsql
broke something.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

>> ... somehow the query to dump the sequences is getting mangled. Any
>> clue how?
>
> Seems you have a sequence called "new"; seems we don't handle that
> well.

So, tested this some more. For some reason, 8.4's pg_dump would
recognize NEW as a reserved word and quote it before dumping. 9.0 and
later pg_dump does not. Any ideas?

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On 2011-11-15 v46 01.43, Josh Berkus wrote:
>>> ... somehow the query to dump the sequences is getting mangled. Any
>>> clue how?
>> Seems you have a sequence called "new"; seems we don't handle that
>> well.
> So, tested this some more. For some reason, 8.4's pg_dump would
> recognize NEW as a reserved word and quote it before dumping. 9.0 and
> later pg_dump does not. Any ideas?
>
Funny, I was about to write a bug report regarding this...

'new' was reserved in 8.4, but not in 9.0/9.1. It's reserved in SQL
1999, 2003 & 2008.
(http://www.postgresql.org/docs/9.1/interactive/sql-keywords-appendix.html)

You can have a 8.4 DB with a 'new' column and dump it with 8.4 tools.
You can have a 9.x DB with a 'new' column and dump it with 9.x tools.

However, it fails when you use 9.x pg_dump against 8.4 with a 'new' column.

//Andreas

On 15.11.2011 02:43, Josh Berkus wrote:
>
>>> ... somehow the query to dump the sequences is getting mangled. Any
>>> clue how?
>>
>> Seems you have a sequence called "new"; seems we don't handle that
>> well.
>
> So, tested this some more. For some reason, 8.4's pg_dump would
> recognize NEW as a reserved word and quote it before dumping. 9.0 and
> later pg_dump does not. Any ideas?

NEW used to be a reserved keyword, but it's not so in 9.0 anymore. So
9.0 pg_dump thinks it doesn't need to be quoted.

This is a problem with any keyword that was reserved (or
col_name_keyword or type_func_name_keyword) in an earlier release, but
has since been made unreserved. Looking at the history of our keyword
lists, that has happened to four keywords:

OFF (was reserved <= 8.4)
NEW (was reserved <= 8.4)
OLD (was reserved <= 8.4)
PUBLIC (was func_name_keyword <= 7.2)

There's also CONVERT, which used to be a col_name_keyword in versions <=
8.2, but that doesn't seem to cause trouble with pg_dump.

I'm thinking that we should add a list of these used-to-be keywords
somewhere in pg_dump (like in keywords.c), and check that list in
addition to the regular keyword list in fmtId().

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On Tue, Nov 15, 2011 at 5:16 AM, Heikki Linnakangas
<heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
> NEW used to be a reserved keyword, but it's not so in 9.0 anymore. So 9.0
> pg_dump thinks it doesn't need to be quoted.

Why isn't it correct?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Tue, Nov 15, 2011 at 5:16 AM, Heikki Linnakangas
> <heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
>> NEW used to be a reserved keyword, but it's not so in 9.0 anymore. So 9.0
>> pg_dump thinks it doesn't need to be quoted.

> Why isn't it correct?

It's correct to not quote it in pg_dump's output (since we make no
promises that such output would load into a pre-9.0 server anyway).
The problem is that it needs to be quoted in commands that pg_dump
sends back to the 8.4 server. Example:

psql (8.4.9)
You are now connected to database "db84".
db84=# create table "new"( f1 int, "new" text);

... pg_dump with newer pg_dump ...

pg_dump: SQL command failed
pg_dump: Error message from server: ERROR: syntax error at or near "new"
LINE 1: COPY public.new (f1, new) TO stdout;
^
pg_dump: The command was: COPY public.new (f1, new) TO stdout;

The least painful solution might be to always quote *every* identifier
in commands sent to the source server, since we don't especially care
how nice-looking those are.

regards, tom lane

Hello, Tom.

You wrote:

TL> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Tue, Nov 15, 2011 at 5:16 AM, Heikki Linnakangas
>> <heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
>>> NEW used to be a reserved keyword, but it's not so in 9.0 anymore. So 9.0
>>> pg_dump thinks it doesn't need to be quoted.

>> Why isn't it correct?

TL> It's correct to not quote it in pg_dump's output (since we make no
TL> promises that such output would load into a pre-9.0 server anyway).
TL> The problem is that it needs to be quoted in commands that pg_dump
TL> sends back to the 8.4 server. Example:

TL> psql (8.4.9)
TL> You are now connected to database "db84".
TL> db84=# create table "new"( f1 int, "new" text);

TL> ... pg_dump with newer pg_dump ...

TL> pg_dump: SQL command failed
TL> pg_dump: Error message from server: ERROR: syntax error at or near "new"
TL> LINE 1: COPY public.new (f1, new) TO stdout;
TL> ^
TL> pg_dump: The command was: COPY public.new (f1, new) TO stdout;

TL> The least painful solution might be to always quote *every* identifier
TL> in commands sent to the source server, since we don't especially care
TL> how nice-looking those are.

+1 for this. This will prevent such situations in the future.

TL> regards, tom lane

--
With best wishes,
Pavel mailto:pavel(at)gf(dot)microolap(dot)com

> The least painful solution might be to always quote *every* identifier
> in commands sent to the source server, since we don't especially care
> how nice-looking those are.

I've never been clear on why we don't do that in the first place.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

I don't think we ever addressed this, but since we have had minimal
complaints about it, I guess we are OK.

---------------------------------------------------------------------------

On Tue, Nov 15, 2011 at 10:04:57PM -0500, Tom Lane wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> > On Tue, Nov 15, 2011 at 5:16 AM, Heikki Linnakangas
> > <heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
> >> NEW used to be a reserved keyword, but it's not so in 9.0 anymore. So 9.0
> >> pg_dump thinks it doesn't need to be quoted.
>
> > Why isn't it correct?
>
> It's correct to not quote it in pg_dump's output (since we make no
> promises that such output would load into a pre-9.0 server anyway).
> The problem is that it needs to be quoted in commands that pg_dump
> sends back to the 8.4 server. Example:
>
> psql (8.4.9)
> You are now connected to database "db84".
> db84=# create table "new"( f1 int, "new" text);
>
> ... pg_dump with newer pg_dump ...
>
> pg_dump: SQL command failed
> pg_dump: Error message from server: ERROR: syntax error at or near "new"
> LINE 1: COPY public.new (f1, new) TO stdout;
> ^
> pg_dump: The command was: COPY public.new (f1, new) TO stdout;
>
> The least painful solution might be to always quote *every* identifier
> in commands sent to the source server, since we don't especially care
> how nice-looking those are.
>
> regards, tom lane

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +