Thread: 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
> 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
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@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
>> ... 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 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 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 Tue, Nov 15, 2011 at 5:16 AM, Heikki Linnakangas <heikki.linnakangas@enterprisedb.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@gmail.com> writes:
> On Tue, Nov 15, 2011 at 5:16 AM, Heikki Linnakangas
> <heikki.linnakangas@enterprisedb.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@gmail.com> writes: >> On Tue, Nov 15, 2011 at 5:16 AM, Heikki Linnakangas >> <heikki.linnakangas@enterprisedb.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@gf.microolap.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@gmail.com> writes: > > On Tue, Nov 15, 2011 at 5:16 AM, Heikki Linnakangas > > <heikki.linnakangas@enterprisedb.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@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +