Re: [BUGS] Cannot dump 8.4.8 database using later versions
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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #6293: JDBC driver performance
The following bug has been logged online: Bug reference: 6293 Logged by: Teun Hoogendoorn Email address: t...@atsc.nl PostgreSQL version: 9.1 Operating system: Linux Description:JDBC driver performance Details: Using the postgresql-9.1-901.jdbc3.jar driver instead of postgresql-9.0-801.jdbc3.jar drops performance dramatically. I think it has something to do with using ResultSetMetaData in Java. The postgres log shows me hundreds of identical query's when retrieving the ResultSetMetaData for a single query. I'm not using an ORM framework, just simple JDBC calls. Example: LOG: execute : SELECT c.oid, a.attnum, a.attname, c.relname, n.nspname, a.attnotnull OR (t.typtype = 'd' AND t.typnotnull), pg_catalog.pg_get_expr(d.adbin, d.adrelid) LIKE '%nextval(%' FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON (c.relnamespace = n.oid) JOIN pg_catalog.pg_attribute a ON (c.oid = a.attrelid) JOIN pg_catalog.pg_type t ON (a.atttypid = t.oid) LEFT JOIN pg_catalog.pg_attrdef d ON (d.adrelid = a.attrelid AND d.adnum = a.attnum) JOIN (SELECT 53886 AS oid , 1 AS attnum UNION ALL SELECT 53886, 2 UNION ALL SELECT 53886, 3 UNION ALL SELECT 53886, 4 UNION ALL SELECT 53886, 5 UNION ALL SELECT 53886, 6 UNION ALL SELECT 53886, 7 UNION ALL SELECT 53886, 8 UNION ALL SELECT 53886, 9 UNION ALL SELECT 53886, 10 UNION ALL SELECT 53886, 11 UNION ALL SELECT 53886, 12 UNION ALL SELECT 53886, 13 UNION ALL SELECT 53886, 14 UNION ALL SELECT 53886, 15 UNION ALL SELECT 53886, 16 UNION ALL SELECT 53886, 17 UNION ALL SELECT 53886, 18 UNION ALL SELECT 53886, 19 UNION ALL SELECT 53886, 20 UNION ALL SELECT 53886, 21 UNION ALL SELECT 53886, 22 UNION ALL SELECT 53886, 23 UNION ALL SELECT 53886, 24 UNION ALL SELECT 53886, 25 UNION ALL SELECT 53886, 26 UNION ALL SELECT 53886, 27 UNION ALL SELECT 53886, 28 UNION ALL SELECT 53886, 29 UNION ALL SELECT 53886, 30 UNION ALL SELECT 53886, 31 UNION ALL SELECT 53886, 32 UNION ALL SELECT 53886, 33 UNION ALL SELECT 53886, 34 UNION ALL SELECT 53886, 35 UNION ALL SELECT 53886, 36 UNION ALL SELECT 53886, 37 UNION ALL SELECT 53886, 38 UNION ALL SELECT 53886, 39 UNION ALL SELECT 53886, 40 UNION ALL SELECT 53886, 41 UNION ALL SELECT 53916, 1 UNION ALL SELECT 53916, 2 UNION ALL SELECT 53916, 3 UNION ALL SELECT 53916, 4 UNION ALL SELECT 53916, 5 UNION ALL SELECT 53916, 6 UNION ALL SELECT 53916, 7 UNION ALL SELECT 53916, 8 UNION ALL SELECT 53916, 9 UNION ALL SELECT 53916, 10 UNION ALL SELECT 53916, 11 UNION ALL SELECT 53916, 12 UNION ALL SELECT 53916, 13 UNION ALL SELECT 53916, 14 UNION ALL SELECT 53916, 15 UNION ALL SELECT 53916, 16 UNION ALL SELECT 53916, 17 UNION ALL SELECT 53916, 18 UNION ALL SELECT 53916, 19 UNION ALL SELECT 53916, 20) vals ON (c.oid = vals.oid AND a.attnum = vals.attnum) -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Cannot dump 8.4.8 database using later versions
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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Cannot dump 8.4.8 database using later versions
On Tue, Nov 15, 2011 at 5:16 AM, Heikki Linnakangas 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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #6294: character 0xefbd9e of encoding "UTF8" has no equivalent in "BIG5"
The following bug has been logged online: Bug reference: 6294 Logged by: CN Liu Email address: cnli...@fastmail.fm PostgreSQL version: 9.1.1 Operating system: Debian Squeeze Description:character 0xefbd9e of encoding "UTF8" has no equivalent in "BIG5" Details: However, the following command works: iconv -f utf8 -t big5 Thank you in advance! -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Cannot dump 8.4.8 database using later versions
Robert Haas writes: > On Tue, Nov 15, 2011 at 5:16 AM, Heikki Linnakangas > 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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Cannot dump 8.4.8 database using later versions
Hello, Tom. You wrote: TL> Robert Haas writes: >> On Tue, Nov 15, 2011 at 5:16 AM, Heikki Linnakangas >> 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:pa...@gf.microolap.com -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
