Re: [BUGS] BUG #8128: pg_dump (>= 9.1) failed while dumping a scheme named "old" from PostgreSQL 8.4
adrian.vondendrie...@credativ.de writes: > [ recent pg_dump fails against an 8.4 server if "old" is used as a name ] Yeah. The reason for this is that "old" was considered a reserved word in 8.4 and before, but since 9.0 it is not reserved (indeed it isn't a keyword at all anymore), so 9.0 and later pg_dump don't think they need to quote it in commands. De-reserving a keyword happens sufficiently rarely that it doesn't really seem worth teaching pg_dump about such cases. There is a workaround, which is to use the --quote-all-identifiers switch when dumping from a server with an incompatible idea of the set of reserved keywords. For the archives' sake, it might be worth noting that --quote-all-identifiers was added in 9.1, which means that 9.0 pg_dump is vulnerable to this problem and has no workaround. That's a bit annoying, but I rather doubt we'll take the trouble to back-port --quote-all-identifiers into 9.0 at this point. 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] ISSUE after upgrading to POSTGRES 8.4.8
On 4/30/2013 2:55 AM, Bansal, Pradeep wrote: Also with the debug5 mode the logs are:- well, I don't see any backend crashes there. -- john r pierce 37N 122W somewhere on the middle of the left coast
Re: [BUGS] BUG #8128: pg_dump (>= 9.1) failed while dumping a scheme named "old" from PostgreSQL 8.4
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 > Hi, > > while browsing on bugs.debian.org I saw the following bug: > 'pg_upgradecluster fails with "OLD used in query that is not in a > rule"' (http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=688960). > > Because I don't found any reference to that report in pgsql-bugs I > report it by my self. > > Since I have tested it on my own I can confirm it is reproducible: > > 1. In a 8.4 cluster create a scheme called "old": >> CREATE SCHEME "old"; > > 2. Create a test table: >> CREATE TABLE "old".test(id int); > > 3. insert some test data: >> INSERT INTO "old".test VALUES (1),(2),(3); > > 4. dump it with pg_dumpall (9.1): > /usr/lib/postgresql/9.1/bin/pg_dumpall -s > foo > > This will produce the following error: >> pg_dump: SQL command failed pg_dump: Error message from server: >> ERROR: OLD used in query that is not > in a rule >> LINE 1: LOCK TABLE old.test IN ACCESS SHARE MODE ^ pg_dump: The >> command was: LOCK TABLE old.test IN ACCESS SHARE MODE pg_dumpall: >> pg_dump failed on database "postgres", exiting > > If pg_dumpall version 8.4 is used, the dump runs smoothly. This is > because pg_dump(all) in version 8.x uses quotes. (2013-04-30 > 16:24:49 CEST LOG: statement: LOCK TABLE "old".test IN ACCESS SHARE > MODE). The error also happened with pg_dump in 9.3. > > I think it's a bad idea to name a scheme 'old', but it's possible, > so it should be supported. I saw no clue to resolve that issue in > the pg_dump documentation. > > I'm running Debian Wheezy using the apt.postgresql.org repository. > > Regards > > - Adrian > > > -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.12 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ iQEcBAEBAgAGBQJRf+jBAAoJEJg+KbLX08eJV5YH/RLriFo3r9eDrbeO6UKwBhBq RMYcs0GvqoDz2QtDRFiyjwl6s12rarTP//dK70oA587XBjWX3FN3k1BkHDrkrEFQ XxP8hwtUKIYDYQgMx5OX8FrrLfmXcoQtkM7mISS3DxPb436Cv4+JVukZWDHVGmJj nVKXwlB/spTSKMVcyi5Be8Gjf6b+7ArYfP6334nSOEznWrdbs+f9lWokTCOkSRW6 DxWC1ELbXkOTXK03qLXtieVGIrs8r6tUnAu/PeIDb3L5nsNZzs6FLfxkq6FckMDI hVPnksW633eUQM/O1Jibr5o82rzvda2b/R/Soo1HjaChSGnnFH3HvodefS5cgTU= =EIwd -END PGP SIGNATURE- -- 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 #8128: pg_dump (>= 9.1) failed while dumping a scheme named "old" from PostgreSQL 8.4
The following bug has been logged on the website: Bug reference: 8128 Logged by: Adrian Vondendriesch Email address: adrian.vondendrie...@credativ.de PostgreSQL version: 9.1.9 Operating system: Debian GNU/Linux 7.0 Description: Hi, while browsing on bugs.debian.org I saw the following bug: 'pg_upgradecluster fails with "OLD used in query that is not in a rule"' (http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=688960). Because I don't found any reference to that report in pgsql-bugs I report it by my self. Since I have tested it on my own I can confirm it is reproducible: 1. In a 8.4 cluster create a scheme called "old": > CREATE SCHEME "old"; 2. Create a test table: > CREATE TABLE "old".test(id int); 3. insert some test data: > INSERT INTO "old".test VALUES (1),(2),(3); 4. dump it with pg_dumpall (9.1): /usr/lib/postgresql/9.1/bin/pg_dumpall -s > foo This will produce the following error: > pg_dump: SQL command failed > pg_dump: Error message from server: ERROR: OLD used in query that is not in a rule > LINE 1: LOCK TABLE old.test IN ACCESS SHARE MODE >^ > pg_dump: The command was: LOCK TABLE old.test IN ACCESS SHARE MODE > pg_dumpall: pg_dump failed on database "postgres", exiting If pg_dumpall version 8.4 is used, the dump runs smoothly. This is because pg_dump(all) in version 8.x uses quotes. (2013-04-30 16:24:49 CEST LOG: statement: LOCK TABLE "old".test IN ACCESS SHARE MODE). I think it's a bad idea to name a scheme 'old', but it's possible, so it should be supported. I saw no clue to resolve that issue in the pg_dump documentation. I'm running Debian Wheezy using the apt.postgresql.org repository. Regards - Adrian -- 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] ISSUE after upgrading to POSTGRES 8.4.8
Hi John, Thanks for the reply. If I execute the query directly in the db(not through my program) then this scenario is not at all reproducible. Also with the debug5 mode the logs are:- = Removing lock file (/var/opt/psb/data/STANDALONE_LOCK) DEBUG: received password packet DEBUG: postmaster child[4703]: starting with ( DEBUG: postgres DEBUG: -v196608 DEBUG: -y DEBUG: RAIDSAPROVDB DEBUG: ) DEBUG: InitPostgres DEBUG: my backend id is 1 DEBUG: StartTransaction DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children: <%2013-04-30 15:05:55 IST>DEBUG: forked new backend, pid=4703 socket=7 DEBUG: CommitTransaction DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children: DEBUG: parse : SELECT nextval('"serial1"') DEBUG: StartTransactionCommand DEBUG: StartTransaction DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children: LOG: duration: 25.215 ms parse : SELECT nextval('"serial1"') DEBUG: bind to LOG: duration: 0.089 ms bind : SELECT nextval('"serial1"') LOG: duration: 4.061 ms execute : SELECT nextval('"serial1"') DEBUG: CommitTransactionCommand DEBUG: CommitTransaction DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children: DEBUG: parse : INSERT INTO RAWLOG (boottime, hidden, id, rawdata, source, version) VALUES ($1, $2, $3, $4, $5, $6) DEBUG: StartTransactionCommand DEBUG: StartTransaction DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children: LOG: duration: 30.224 ms parse : INSERT INTO RAWLOG (boottime, hidden, id, rawdata, source, version) VALUES ($1, $2 , $3, $4, $5, $6) Please let me know your thoughts. Regards Pradeep B From: John R Pierce [mailto:pie...@hogranch.com] Sent: Tuesday, April 30, 2013 1:24 PM To: Bansal, Pradeep; pgsql-bugs@postgresql.org Subject: Re: [BUGS] ISSUE after upgrading to POSTGRES 8.4.8 On 4/29/2013 11:40 PM, Bansal, Pradeep wrote: PQdb name : RAIDSAPROVDB : server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. you need to look at the server log after an error like this, and see WHY the server terminated but I concurr with Heikki, upgrade to 8.4.latest (currently 8.4.17) and if this still occurs then figure out why. -- john r pierce 37N 122W somewhere on the middle of the left coast
Re: [BUGS] ISSUE after upgrading to POSTGRES 8.4.8
On 4/29/2013 11:40 PM, Bansal, Pradeep wrote: PQdb name : RAIDSAPROVDB : server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. you need to look at the server log after an error like this, and see WHY the server terminated but I concurr with Heikki, upgrade to 8.4.latest (currently 8.4.17) and if this still occurs then figure out why. -- john r pierce 37N 122W somewhere on the middle of the left coast
Re: [BUGS] ISSUE after upgrading to POSTGRES 8.4.8
On 30.04.2013 09:40, Bansal, Pradeep wrote: I have observed the below issue after upgrading to the above mentioned version of postgress. Any help is very much appreciated:- ISSUE:- === SEQUENCE STATEMENTS = SELECT nextval('"serial1"') PQstatus(m_connection) :- CONNECTION OK PQdb name : RAIDSAPROVDB : server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. = The above error is seen after postgress restart/fresh installation couple of times. While executing the same query thrice and so on it runs as expected. I am not able to understand that why it is getting failed initially. kindly let me know if this is an existing bug in the postgress or any fix available for the same. You didn't mention what version you upgraded from, but in any case, you should at least upgrade to the latest 8.4.x minor release, which is 8.4.17. There have been a lot of bugs fixed between 8.4.8 and 8.4.17. I don't know if any of them explain the issue you're seeing, but there's not much point debugging it further before you upgrade. - Heikki -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] ISSUE after upgrading to POSTGRES 8.4.8
Hello, I have observed the below issue after upgrading to the above mentioned version of postgress. Any help is very much appreciated:- ISSUE:- === SEQUENCE STATEMENTS = SELECT nextval('"serial1"') PQstatus(m_connection) :- CONNECTION OK PQdb name : RAIDSAPROVDB : server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. = The above error is seen after postgress restart/fresh installation couple of times. While executing the same query thrice and so on it runs as expected. I am not able to understand that why it is getting failed initially. kindly let me know if this is an existing bug in the postgress or any fix available for the same. Looking forward to your reply. Regards Pradeep Bansal