Re: [BUGS] fsync and semctl errors with 8.1.5/win32
Last night I received another filesystem-related problem. Shortly after this problem occurred, I had a connection hang indefinitely, causing my software to go down all night. The log output that occurred shortly before the problem is below. After that, there was no log output by PostgreSQL until I came in this morning and killed the offending process. Any ideas? Could the pg_xlog error below possibly result in a transaction hanging indefinitely? If so, would the solution Tom proposed possibly fix the error? Any update on getting a patched exe for Thomas and I to test? 2006-11-29 20:11:35 ERROR: tuple concurrently updated 2006-11-29 20:11:35 ERROR: tuple concurrently updated 2006-11-29 20:11:35 ERROR: tuple concurrently updated 2006-11-29 20:11:36 LOG: transaction ID wrap limit is 1090292093, limited by database incommDashboard 2006-11-29 20:11:36 LOG: transaction ID wrap limit is 1090292093, limited by database incommDashboard 2006-11-29 21:21:38 LOG: transaction ID wrap limit is 1090522044, limited by database incommDashboard 2006-11-29 21:21:38 LOG: transaction ID wrap limit is 1090522044, limited by database incommDashboard 2006-11-29 22:22:52 LOG: transaction ID wrap limit is 1090579373, limited by database incommDashboard 2006-11-29 22:22:52 LOG: transaction ID wrap limit is 1090579373, limited by database incommDashboard 2006-11-29 23:38:47 LOG: transaction ID wrap limit is 1090633937, limited by database incommDashboard 2006-11-29 23:38:47 LOG: transaction ID wrap limit is 1090633937, limited by database incommDashboard 2006-11-29 23:57:52 LOG: could not rename file pg_xlog/00010019005E to pg_xlog/00010019007F, continuing to try On Tue, 28 Nov 2006 17:49:22 -0500, Jeremy Haile [EMAIL PROTECTED] said: I am currently running 8.1.5, but I'm willing to upgrade to whatever version, use a patched exe, etc. Just let me know what I need to do. On Tue, 28 Nov 2006 23:39:00 +0100, Thomas H. [EMAIL PROTECTED] said: Perhaps this should be #ifdef WIN32, although there's probably no harm in doing it on Unixen too. Can someone test this idea? if magnus/dave could provide me a patched rc1 exe, i could run it in our semi-productive environment for some tests. - thomas ---(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 ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [BUGS] fsync and semctl errors with 8.1.5/win32
2006-11-29 23:57:52 LOG: could not rename file pg_xlog/00010019005E to pg_xlog/00010019007F, continuing to try i had this one as well. good news is: this bug is fixed in 8.2 - thomas ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] fsync and semctl errors with 8.1.5/win32
Really? That's great news. Maybe I should start testing with 8.2 today. Did you run into problems where transactions would hang? If so, did those disappear in 8.2? On Thu, 30 Nov 2006 15:14:46 +0100, Thomas H. [EMAIL PROTECTED] said: 2006-11-29 23:57:52 LOG: could not rename file pg_xlog/00010019005E to pg_xlog/00010019007F, continuing to try i had this one as well. good news is: this bug is fixed in 8.2 - thomas ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] fsync and semctl errors with 8.1.5/win32
Did you run into problems where transactions would hang? If so, did those disappear in 8.2? well, i wasn't really able to exactly determine under what conditions that xlog bug appeared in our case. tho it always was when lots of data is imported at once within one transaction. under normal load i've never seen the xlog bug. as far as i know it was some sort of lifelock: as with the other error messages, another postgres.exe kept a lock of the xlog file, which the bgwriter-process wanted to rename which lead to the complete halt of the db system, due to the importance of xlog/bgwriter. you can force an unload of the locked xlog file handle in processmon, and postgresql will resume normally. i had a transaction lately that created 7gb of xlog-files (vacuum full of a mid-sized table) without any xlog-lockup, so i guess this problem is really fixed in the latest 8.2 build :-) if you have hanging transactions but other db activity works well, i would rather guess its a side effect of the other file problems with the relation-files that can't be renamed. i've never been able to see any impact of that error message. even when it appears 10 times a second everything seems ok. but on the other side, in our case, we use the database as a web backend and have always around 20-30 concurrent connections, so its hard to debug. - thomas ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] fsync and semctl errors with 8.1.5/win32
We were also running it on Windows Server 2003. We ended up rolling back service pack 1 and it seems to have taken care of the hanging transactions and we haven't seen a semctl error in awhile. interesting. we're using sp1 pgsql since day 1 and the problem only started when testing 8.2b1. but on the other hand, it might be that a hotfix is the cause for this error, as i haven't seen it before aug/sept 06. i sure would have noticed... - thomas ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] fsync and semctl errors with 8.1.5/win32
Jeremy, My company runs a 200 gig data warehouse. We are running 8.1.2. We were seeing hanging transactions and occasional semctl errors. We were also running it on Windows Server 2003. We ended up rolling back service pack 1 and it seems to have taken care of the hanging transactions and we haven't seen a semctl error in a while. Worth a shot if it applies to you. Brad Russell Programmer Analyst NPC International -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Thomas H. Sent: Thursday, November 30, 2006 9:11 AM To: Jeremy Haile; pgsql-bugs@postgresql.org Subject: Re: [BUGS] fsync and semctl errors with 8.1.5/win32 Did you run into problems where transactions would hang? If so, did those disappear in 8.2? well, i wasn't really able to exactly determine under what conditions that xlog bug appeared in our case. tho it always was when lots of data is imported at once within one transaction. under normal load i've never seen the xlog bug. as far as i know it was some sort of lifelock: as with the other error messages, another postgres.exe kept a lock of the xlog file, which the bgwriter-process wanted to rename which lead to the complete halt of the db system, due to the importance of xlog/bgwriter. you can force an unload of the locked xlog file handle in processmon, and postgresql will resume normally. i had a transaction lately that created 7gb of xlog-files (vacuum full of a mid-sized table) without any xlog-lockup, so i guess this problem is really fixed in the latest 8.2 build :-) if you have hanging transactions but other db activity works well, i would rather guess its a side effect of the other file problems with the relation-files that can't be renamed. i've never been able to see any impact of that error message. even when it appears 10 times a second everything seems ok. but on the other side, in our case, we use the database as a web backend and have always around 20-30 concurrent connections, so its hard to debug. - thomas ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(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] fsync and semctl errors with 8.1.5/win32
In our case the hanging transactions was killing our in house merge process that merges the data from our stores to the warehouse. When we rolled back sp1 no hanging transactions. When we applied sp1 the hanging started again. We took sp1 off and on a couple times and it happened every time. -Original Message- From: Thomas H. [mailto:[EMAIL PROTECTED] Sent: Thursday, November 30, 2006 10:04 AM To: Bradley Russell Cc: pgsql-bugs@postgresql.org Subject: Re: [BUGS] fsync and semctl errors with 8.1.5/win32 We were also running it on Windows Server 2003. We ended up rolling back service pack 1 and it seems to have taken care of the hanging transactions and we haven't seen a semctl error in awhile. interesting. we're using sp1 pgsql since day 1 and the problem only started when testing 8.2b1. but on the other hand, it might be that a hotfix is the cause for this error, as i haven't seen it before aug/sept 06. i sure would have noticed... - thomas ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[BUGS] BUG #2793: Cannot Install (Problem with permitions)
The following bug has been logged online: Bug reference: 2793 Logged by: Thiago dos Santos Alves Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2rc1 Operating system: Windows Description:Cannot Install (Problem with permitions) Details: When I install PostgreSQL 8.2rc1 on my windows machine, it goes fine but at the end of installation the installer gives me an error saying that it cannot set permitions to files. After that the directory where I try to install PostgreSQL is not accessible anymore, even I'm being one of the administrators of the machine! ---(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 #2795: can't start service postgre
The following bug has been logged online: Bug reference: 2795 Logged by: Ilia Email address: [EMAIL PROTECTED] PostgreSQL version: postgreSQL 8.1 Operating system: windows server 2003 standart edition Description:can't start service postgre Details: I successfully shut down PostgreSQL, but I can't start it. Error message: Service Postgre SQL Database Server 8.1 on localhost was started but it was stopped soon. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[BUGS] BUG #2794: corrupted table pk index
The following bug has been logged online: Bug reference: 2794 Logged by: Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1.3 Operating system: macosx Description:corrupted table pk index Details: when i try to reconstruct a table index reindex table public.itemfactura force; get ERROR: could not create unique index Detail: Table contains duplicated values. when select oid,count(*) from itemfactura group by oid having count(*)1 return oid count --- 8485900 2 8486062 2 8486063 2 8486064 2 8486065 2 8486066 2 8486067 2 8486068 2 8486084 2 8486094 2 8486108 2 8486109 2 8486110 2 8486128 2 8486129 2 8486130 2 8486131 2 17 record(s) selected [Fetch MetaData: 6/ms] [Fetch Data: 3/ms] the table PK is (id_sucursal,id_factura ,id_item) when select ctid,oid,* from itemfactura where oid=8486110 --i.e. a bad tuple get ctidoid id_sucursal id_factura id_item id_area id_grupo id_categoria id_subcategoria id_segmento id_farmacity costo costo_promedio cantidad precio descuento iva_generado id_tipo_iva iva id_acuerdo precio_acuerdo id_plan iva_generado_acuerdo id_descuento -- --- -- - -- -- --- --- -- -- --- - --- - --- -- -- - - -- --- --- (11899,24) 8486110 108 263341 2 2 1730 1734 175517801017946 720 72011200 0 166 4 0.160 0 0 00 (11912,13) 8486110 108 263341 2 2 1730 1734 175517801017946 720 72011200 0 166 4 0.160 0 0 00 this is a global database problem and pg_dump does not help how can i fix the database tks ---(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 #2795: can't start service postgre
I doubt this is really a bug. This usually just means that the server failed to start for some reason (port already in use, bad configuration, etc.) Any startup problem with the database will result in this behavior. However - what might could be addressed is that it is pretty difficult to debug what is actually causing Postgres to not start. Is it logged anywhere? Typically I debug this by switching to the postgres user and trying to start pg_ctl manually, which will print out the problem. But there's got to be a better way. On Thu, 30 Nov 2006 13:33:34 GMT, Ilia [EMAIL PROTECTED] said: The following bug has been logged online: Bug reference: 2795 Logged by: Ilia Email address: [EMAIL PROTECTED] PostgreSQL version: postgreSQL 8.1 Operating system: windows server 2003 standart edition Description:can't start service postgre Details: I successfully shut down PostgreSQL, but I can't start it. Error message: Service Postgre SQL Database Server 8.1 on localhost was started but it was stopped soon. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] fsync and semctl errors with 8.1.5/win32
I've upgraded to 8.2rc1. I still receive fsync errors all the time, but I haven't had it freeze yet. With 8.1.5 I was able to reproduce the freeze very quickly, so this is a good sign! Only time will tell if it's truly resolved - but I'll post updates to this list either way. I'm still open to testing the fsync fix that Tom proposed if I can get a patched exe. On Thu, 30 Nov 2006 10:15:50 -0600, Bradley Russell [EMAIL PROTECTED] said: In our case the hanging transactions was killing our in house merge process that merges the data from our stores to the warehouse. When we rolled back sp1 no hanging transactions. When we applied sp1 the hanging started again. We took sp1 off and on a couple times and it happened every time. -Original Message- From: Thomas H. [mailto:[EMAIL PROTECTED] Sent: Thursday, November 30, 2006 10:04 AM To: Bradley Russell Cc: pgsql-bugs@postgresql.org Subject: Re: [BUGS] fsync and semctl errors with 8.1.5/win32 We were also running it on Windows Server 2003. We ended up rolling back service pack 1 and it seems to have taken care of the hanging transactions and we haven't seen a semctl error in awhile. interesting. we're using sp1 pgsql since day 1 and the problem only started when testing 8.2b1. but on the other hand, it might be that a hotfix is the cause for this error, as i haven't seen it before aug/sept 06. i sure would have noticed... - thomas ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 6: explain analyze is your friend