Re: [BUGS] BUG #3032: Commit hung for days
Thanks for the response Magnus. The server logs do not show anything unusual, other than the occurrence 5 minutes prior. I will still take your advice on the upgrade. 2007-02-15 19:54:13 LOG: autovacuum: processing database "MM" 2007-02-15 19:55:13 LOG: autovacuum: processing database "postgres" 2007-02-15 19:55:37 ERROR: could not read block 558 of relation 1663/16403/16599: Invalid argument 2007-02-15 19:55:37 ERROR: current transaction is aborted, commands ignored until end of transaction block 2007-02-15 19:56:38 LOG: autovacuum: processing database "MM" 2007-02-15 19:57:38 LOG: autovacuum: processing database "postgres" 2007-02-15 19:58:38 LOG: autovacuum: processing database "MM" 2007-02-15 19:59:38 LOG: autovacuum: processing database "postgres" 2007-02-15 20:00:38 LOG: autovacuum: processing database "MM" 2007-02-15 20:01:38 LOG: autovacuum: processing database "postgres" 2007-02-15 20:02:38 LOG: autovacuum: processing database "MM" 2007-02-15 20:03:38 LOG: autovacuum: processing database "postgres" 2007-02-15 20:04:38 LOG: autovacuum: processing database "MM" . > -Original Message- > From: Magnus Hagander [mailto:[EMAIL PROTECTED] > Sent: Friday, February 23, 2007 3:29 AM > To: Craig White > Cc: pgsql-bugs@postgresql.org > Subject: Re: [BUGS] BUG #3032: Commit hung for days > > Craig White wrote: > > The following bug has been logged online: > > > > Bug reference: 3032 > > Logged by: Craig White > > Email address: [EMAIL PROTECTED] > > PostgreSQL version: 8.1.5 > > Operating system: Windows XP > > Description:Commit hung for days > > Details: > > > > I'm not looking for quick answers or a fix, but I wanted to submit this > for > > your information. > > > > Setup: > > > > Java App using Hibernate 3.2.1, C3P0 connection pool, JDBC to PostgreSQL > > database. > > > > PostgreSQL setup is pretty much the defaults. > > > > Occurrence: > > > > During some load testing of my application (best characterized as a > > multi-threaded transaction processing system), I ran into a transaction > that > > appeared to get stuck in its Commit. Using pgAdminIII, the 'Server > Status' > > window shows the Commit Query from 4 days prior. Other connections > > periodically execute a statement quickly and successfully so there are > not 2 > > statements in deadlock. > > > > The commit would have been altering a small number of rows from a couple > > tables. > > > > The commit happened at 2007/02/15 20:02:10, the only odd occurrence in > my > > logs occurred 5 minutes earlier. I'm not sure if it is releated or not. > > There was a lot of successful activity between 19:57 and 20:02. I'm not > yet > > sure of the cause of this error, but the prior error log is as follows: > > The interesting thing would be to see the server logs, not the > application logs. Specifically, an issue that could look just like this > was fixed in 8.1.7, in which case you would see weird error messages > about permission denied or such in the *server* logs. None of that would > show up in the client logs. > > So I would suggest upgrading to the latest release in 8.1.x. > > //Magnus ---(end of broadcast)--- TIP 6: explain analyze is your friend
[BUGS] BUG #3059: psql to 'postgres' shortcut
The following bug has been logged online: Bug reference: 3059 Logged by: Raymond Naseef Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2 Operating system: Windows XP Description:psql to 'postgres' shortcut Details: The shortcut to run psql.exe added to the menus is a great think to do, but when the program stops the window closes with no pause or time delay. This is a serious issue if the login fails. Sometimes I forget to start the server, but I do not know that because I cannot read text in ~0.02 seconds. BTW, I bet some of your other .bat files have the same issue. Please run it by doing 1 or other: 1. Putting a "pause" at the end of the batch file. 2. cmd.exe /K "" (just note the "" for default user name will make this fail. Sorry I do not know how to fix that unless username has no bad-for-cmd characters in it, then quotes are not needed) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[BUGS] BUG #3064: In Stored Procedures (pgplgql
The following bug has been logged online: Bug reference: 3064 Logged by: Fridman Garri Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2.0 Operating system: WinXP-Prof Description:In Stored Procedures (pgplgql Details: A PSQL terminates with a Message: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. -- Example 1: no Crash select message from mytest(-1); -- Example 2: no Crash select message from mytest(0); -- Example 3: no Crash select message from mytest(2); -- Example 4: Crash !!! select message from mytest(5); -- Used Stored Procedure -- CREATE OR REPLACE FUNCTION mytest(IN amount "int4", OUT message "varchar") AS $BODY$ DECLARE myid integer; BEGIN message = 'no errors'; IF amount > 0 THEN SELECT id INTO myid FROM client WHERE id = 1; END IF; IF amount = 0 OR amount = 5 THEN RAISE EXCEPTION 'error: amount = %', amount; END IF; EXCEPTION WHEN OTHERS THEN message = SQLERRM; END; $BODY$ LANGUAGE 'plpgsql'; ALTER FUNCTION mytest(IN amount "int4", OUT message "varchar") OWNER TO garri ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[BUGS] Spelling error in 8.1.6-1 Windows Error Dialog typo
Not sure where to find the TODO list per the bug reporting guidelines, but this is pretty simple. It may be fixed in newer versions, but we are using this version until 8.2 is tested on our systems. Do with this what you will. When you create a superuser with a semi-colon or quotes the error dialog starts "Passwords may not conain quotes..." missing the "t" in contain. Keith
[BUGS] BUG #3065: Bug in stored procedure EXEPTION handling or in plpgsql ?
The following bug has been logged online: Bug reference: 3065 Logged by: Fridman Garri Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2.0 Operating system: WinXP-Prof SP2 Description:Bug in stored procedure EXEPTION handling or in plpgsql ? Details: If I call "select message from mytest(5);" from command line or from Admin III when a programm terminates with a Message: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. Stored Procedure "mytest" Created Successfully before running of examples. Examples of situation: -- Example 1: no Crash select message from mytest(-1); -- Example 2: no Crash select message from mytest(0); -- Example 3: no Crash select message from mytest(2); -- Example 4: Crash !!! select message from mytest(5); -- Used Stored Procedure -- CREATE OR REPLACE FUNCTION mytest(IN amount "int4", OUT message "varchar") AS $BODY$ DECLARE myid integer; BEGIN message = 'no errors'; IF amount > 0 THEN SELECT id INTO myid FROM client WHERE id = 1; END IF; IF amount = 0 OR amount = 5 THEN RAISE EXCEPTION 'error: amount = %', amount; END IF; EXCEPTION WHEN OTHERS THEN message = SQLERRM; END; $BODY$ LANGUAGE 'plpgsql'; ALTER FUNCTION mytest(IN amount "int4", OUT message "varchar") OWNER TO garri Best regards. Garri Fridman. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] BUG #3038: information_schema.constraint_column_usage has wrong information for foreign keys
On 2007-02-23, Tom Lane wrote: "Eli Green" <[EMAIL PROTECTED]> writes: The columns listed in constraint_column_usage in the SQL92 information schema are from the wrong "side" of the key. Are you certain this is wrong? The SQL99 spec is not exactly readable on the matter, but as best I can tell the behavior we have follows the spec. The portion of the spec's CONSTRAINT_COLUMN_USAGE view definition that's concerned with foreign keys is SELECT PK.TABLE_CATALOG, PK.TABLE_SCHEMA, PK.TABLE_NAME, PK.COLUMN_NAME, FK.CONSTRAINT_CATALOG, FK.CONSTRAINT_SCHEMA, FK.CONSTRAINT_NAME FROM DEFINITION_SCHEMA.REFERENTIAL_CONSTRAINTS AS FK JOIN DEFINITION_SCHEMA.KEY_COLUMN_USAGE AS PK ON ( FK.UNIQUE_CONSTRAINT_CATALOG, FK.UNIQUE_CONSTRAINT_SCHEMA, FK.UNIQUE_CONSTRAINT_NAME ) = ( PK.CONSTRAINT_CATALOG, PK.CONSTRAINT_SCHEMA, PK.CONSTRAINT_NAME ) and it sure looks to me like that ought to put out the column names of the columns associated with the referential constraint's underlying unique constraint. Which is what we do. I tend to agree that the other behavior might be more useful, but we're going to need more evidence that it's wrong to change it. Has anyone tried this example on Oracle or DB2 or SQL Server? regards, tom lane Sorry for not checking the spec first, I'm doing my testing at home without access to the internet. I've tried this on SQL Server 2000; the only other database I have access to which attempts to implement the entire information_schema. No Oracle or DB2. MySQL doesn't implement referential_constraints. It could be that I'm wrong and SQL Server has implemented it incorrectly. Initially I thought that this was the only place to get information about both sides of the foreign key but since they are both keys, I can get the list of columns (with ordinal_position to join against) from key_column_usage for the unique key and the non-unique key (the foreign key itself). Does that make any sense? In any case, sorry I said anything and curse Microsoft for implementing it wrong and making me doubt postgresql. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[BUGS] BUG #3067: Unnecessary lock blocks reindex
The following bug has been logged online: Bug reference: 3067 Logged by: Axel Noltemeier Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2.1 Operating system: linux: Ubuntu 6.10, Edgy Eft; Mandriva 9.2 Description:Unnecessary lock blocks reindex Details: Data: CREATE TABLE factory ( id smallint NOT NULL, type_id smallint, name character varying(50) NOT NULL, asdb_id integer ); INSERT INTO factory VALUES (2, 1, 'Hannover', 10418); INSERT INTO factory VALUES (3, 1, 'Bonn', 10218); ALTER TABLE ONLY factory ADD CONSTRAINT factory_asdb_id_ukey UNIQUE (asdb_id); ALTER TABLE ONLY factory ADD CONSTRAINT factory_pkey PRIMARY KEY (id); ALTER TABLE ONLY factory ADD CONSTRAINT factory_ukey UNIQUE (type_id, name, ip, asdb_id); CREATE TABLE machine ( id integer NOT NULL, factory_id smallint NOT NULL, name character varying(50) NOT NULL, factory_machine_id integer NOT NULL ); INSERT INTO machine VALUES (1, 3, 'Mach1', 10303); INSERT INTO machine VALUES (2, 3, 'Mach2', 10103); ALTER TABLE ONLY machine ADD CONSTRAINT machine_factory_id_ukey UNIQUE (factory_id, factory_machine_id); ALTER TABLE ONLY machine ADD CONSTRAINT machine_pkey PRIMARY KEY (id); ALTER TABLE ONLY machine ADD CONSTRAINT fk_machine_factory FOREIGN KEY (factory_id) REFERENCES factory(id); Query: BEGIN; -- This statement generates an unnecessary(?) lock -- on factory_pkey. That index is not used in the plan. EXPLAIN ANALYZE SELECT * FROM factory f Where f.id IN (select m.factory_id from machine m); -- Show the lock select now(), db.datname, c.relname, l.* from pg_locks l left outer join pg_class c on (l.relation = c.oid) left outer join pg_database db on (l.database = db.oid) where relname = 'factory_pkey' ; -- At this point of execution "reindex table factory;" called from -- another transaction is blocked COMMIT; ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] BUG #3048: pg_dump dumps intarray metadata incorrectly
Maybe possibly remove DEFAULT definition from the intarray initialization SQL and eliminate in the documentation: "if you want to use GIN with _int4, you have to specify the operator class explicitly and manually"? This at least does not break the standard pg_dump behaviour. We checked, if we remove DEFAULT keyword, a dump is restored correctly. On 2/23/07, Tom Lane <[EMAIL PROTECTED]> wrote: "Dmitry Koterov" <[EMAIL PROTECTED]> writes: > [ pg_restore fails with ] > ERROR: could not make operator class "gin__int_ops" be default for type > pg_catalog.int4[] > DETAIL: Operator class "_int4_ops" already is the default. Yeah. I'd say that intarray's attempt to override the default status of the built-in gin opclass is simply a bad idea and should be removed. It's not even documented that it does that (in fact I see no mention of GIN at all in README.intarray :-(, so we have a documentation lack here too). Comments? regards, tom lane
Re: [BUGS] BUG #2969: Inaccuracies in Solaris FAQ
On Fri, 23 Feb 2007, Zdenek Kotala wrote: There is Solaris FAQ update. Please, look on it and let me know any comments. Very minor grammatical nits aside, it looks fine to me. Thanks for updating the FAQ! Cheers, -- Rich Teer, SCSA, SCNA, SCSECA, OpenSolaris CAB member President, Rite Online Inc. Voice: +1 (250) 979-1638 URL: http://www.rite-group.com/richIndex: doc/FAQ_Solaris === RCS file: /projects/cvsroot/pgsql/doc/FAQ_Solaris,v retrieving revision 1.23 diff -c -r1.23 FAQ_Solaris *** doc/FAQ_Solaris 2 Dec 2006 09:29:51 - 1.23 --- doc/FAQ_Solaris 23 Feb 2007 16:12:19 - *** *** 16,21 --- 16,22 6) Where I can download prepared Solaris packages? 7) How can I tune PostgreSQL and Solaris for performance? 8) Can I use DTrace for tracing PostgreSQL? + 9) Can I compile PostgreSQL with Kerberos v5 support? 1) What tools do I need to build and install PostgreSQL on Solaris? *** *** 72,85 This is probably a case of the run-time linker being unable to find some library, probably libz, libreadline or some other non-standard library such as libssl. To point it to the right location, set the ! LD_LIBRARY_PATH environment variable, e.g., ! LD_LIBRARY_PATH=/usr/sfw/lib:/opt/sfw/lib:/usr/local/lib ! export LD_LIBRARY_PATH ! and restart configure. You will also have to keep this setting whenever ! you run any of the installed PostgreSQL programs. Alternatively, set ! the environment variable LD_RUN_PATH. See the ld(1) man page for more information. --- 73,84 This is probably a case of the run-time linker being unable to find some library, probably libz, libreadline or some other non-standard library such as libssl. To point it to the right location, set the ! LDFLAGS environment variable, e.g., ! LDFLAGS="-R /usr/sfw/lib:/opt/sfw/lib:/usr/local/lib" ! export LDFLAGS ! and restart configure. See the ld(1) man page for more information. *** *** 145,150 --- 144,153 Yes, see the chapter "Monitoring Database Activity" in the documentation for further information. + You can also find more information here: + + http://blogs.sun.com/robertlor/entry/user_level_dtrace_probes_in + If you see the linking of the postgres executable abort with an error message like *** *** 157,164 gmake: *** [postgres] Error 1 your DTrace installation is too old to handle probes in static ! functions. You need Solaris 10u3 or newer. - You can also find more information here: - - http://blogs.sun.com/robertlor/entry/user_level_dtrace_probes_in --- 160,176 gmake: *** [postgres] Error 1 your DTrace installation is too old to handle probes in static ! functions. You need Solaris 10u4 or newer. Workaround is remove static ! keyword from AbortTransaction and CommitTransaction functions declaration in ! src/backend/access/transam/xact.c. ! ! See http://sunsolve.sun.com/search/document.do?assetkey=1-1-2139224-1 ! (registration required). ! ! 9) Can I compile PostgreSQL with Kerberos v5 support? ! ! Kerberos is integrated in OpenSolaris and will be integrated in Solaris 10u4. GSS security ! mechanism contains internal Kerberos v5 library implementation which provide all necessary ! krb5 function. However, usage this library is only on own risk. It is private library ! and interface may change without notice. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #3064: In Stored Procedures (pgplgql
Fridman Garri wrote: > > The following bug has been logged online: > > Bug reference: 3064 > Logged by: Fridman Garri > Email address: [EMAIL PROTECTED] > PostgreSQL version: 8.2.0 > Operating system: WinXP-Prof > Description:In Stored Procedures (pgplgql > Details: Please update to 8.2.3 and retry. A bug was fixed in nearby code which may explain this issue. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [BUGS] BUG #3067: Unnecessary lock blocks reindex
"Axel Noltemeier" <[EMAIL PROTECTED]> writes: > -- This statement generates an unnecessary(?) lock > -- on factory_pkey. That index is not used in the plan. > EXPLAIN ANALYZE SELECT * > FROM factory f > Where f.id IN (select m.factory_id from machine m); That behavior was changed here: http://archives.postgresql.org/pgsql-committers/2006-07/msg00356.php I'm disinclined to consider reverting that change... It's possible that we could modify the planner to release locks at the end of planning on indexes it chooses not to use, but 99.9% of the time it'd be a waste of cycles to do that. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
