Re: [BUGS] fsync and semctl errors with 8.1.5/win32
Here's a few seconds of the log output (this has been going on for 10 mins as of this e-mail being sent): 2006-11-28 16:16:10 LOG: could not fsync segment 0 of relation 1663/16404/30267: Permission denied 2006-11-28 16:16:10 ERROR: storage sync failed on magnetic disk: Permission denied Here's the FileMon output from the same seconds: 4:16:10 PM postgres.exe:3168 OPENC:\Program Files\PostgreSQL\8.1\data\base\16404\30267 DELETE PEND Options: Open Access: 0012019F I still don't want to make mdsync() treat EACCES as an ignorable error. However, in this situation we've got an infinite loop because the checkpoint will never succeed and thus the bgwriter will never reach smgrcloseall(), which seems to be what's needed to allow the deleted file to die the real death. Perhaps a suitable workaround would be to make the bgwriter do smgrcloseall in its error recovery path? That is /* * Sleep at least 1 second after any error. A write error is likely * to be repeated, and we don't want to be filling the error logs as * fast as we can. */ pg_usleep(100L); + + /* Drop open files to allow deleted files to really go away */ + smgrcloseall(); } /* We can now handle ereport(ERROR) */ PG_exception_stack = local_sigjmp_buf; Perhaps this should be #ifdef WIN32, although there's probably no harm in doing it on Unixen too. Can someone test this idea? in 8.2.0 the error messages changed a bit: 2006-12-05 03:47:12 [736] LOG: could not fsync segment 0 of relation 1663/16692/2361629: Permission denied 2006-12-05 03:47:12 [736] ERROR: storage sync failed on magnetic disk: Permission denied 2006-12-05 03:47:13 [736] ERROR: could not open relation 1663/16692/2361629: Permission denied 2006-12-05 03:47:14 [736] ERROR: could not open relation 1663/16692/2361629: Permission denied 2006-12-05 03:47:15 [736] ERROR: could not open relation 1663/16692/2361629: Permission denied 2006-12-05 03:47:16 [736] ERROR: could not open relation 1663/16692/2361629: Permission denied 2006-12-05 03:47:17 [736] ERROR: could not open relation 1663/16692/2361629: Permission denied 2006-12-05 03:47:18 [736] ERROR: could not open relation 1663/16692/2361629: Permission denied 2006-12-05 03:47:19 [736] ERROR: could not open relation 1663/16692/2361629: Permission denied 2006-12-05 03:47:20 [736] ERROR: could not open relation 1663/16692/2361629: Permission denied 2006-12-05 03:47:21 [736] ERROR: could not open relation 1663/16692/2361629: Permission denied 2006-12-05 03:47:22 [736] ERROR: could not open relation 1663/16692/2361629: Permission denied 2006-12-05 03:47:23 [736] ERROR: could not open relation 1663/16692/2361629: Permission denied 2006-12-05 03:47:24 [736] ERROR: could not open relation 1663/16692/2361629: Permission denied 2006-12-05 03:47:25 [736] ERROR: could not open relation 1663/16692/2361629: Permission denied 2006-12-05 03:47:26 [736] ERROR: could not open relation 1663/16692/2361629: Permission denied 2006-12-05 03:47:27 [736] ERROR: could not open relation 1663/16692/2361629: Permission denied 2006-12-05 03:47:28 [736] ERROR: could not open relation 1663/16692/2361629: Permission denied 2006-12-05 03:47:29 [736] ERROR: could not open relation 1663/16692/2361629: Permission denied 2006-12-05 03:47:30 [736] ERROR: could not open relation 1663/16692/2361629: Permission denied 2006-12-05 03:47:31 [736] ERROR: could not open relation 1663/16692/2361629: Permission denied 2006-12-05 03:47:32 [736] ERROR: could not open relation 1663/16692/2361629: Permission denied 2006-12-05 03:47:33 [736] ERROR: could not open relation 1663/16692/2361629: Permission denied 2006-12-05 03:52:34 [736] LOG: could not fsync segment 0 of relation 1663/16692/2361668: Permission denied 2006-12-05 03:52:34 [736] ERROR: storage sync failed on magnetic disk: Permission denied 2006-12-05 03:52:35 [736] ERROR: could not open relation 1663/16692/2361668: Permission denied 2006-12-05 03:52:36 [736] ERROR: could not open relation 1663/16692/2361668: Permission denied 2006-12-05 03:52:37 [736] ERROR: could not open relation 1663/16692/2361668: Permission denied 2006-12-05 03:52:38 [736] ERROR: could not open relation 1663/16692/2361668: Permission denied 2006-12-05 03:52:39 [736] ERROR: could not open relation 1663/16692/2361668: Permission denied 2006-12-05 03:52:40 [736] ERROR: could not open relation 1663/16692/2361668: Permission denied 2006-12-05 03:52:41 [736] ERROR: could not open relation 1663/16692/2361668: Permission denied ... and so on. - thomas ---(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
Thomas H. [EMAIL PROTECTED] writes: in 8.2.0 the error messages changed a bit: 2006-12-05 03:47:12 [736] LOG: could not fsync segment 0 of relation 1663/16692/2361629: Permission denied 2006-12-05 03:47:12 [736] ERROR: storage sync failed on magnetic disk: Permission denied 2006-12-05 03:47:13 [736] ERROR: could not open relation 1663/16692/2361629: Permission denied 2006-12-05 03:47:14 [736] ERROR: could not open relation 1663/16692/2361629: Permission denied So what's holding the file open now? It's evidently not the bgwriter. regards, tom lane ---(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 8.2.0 the error messages changed a bit: 2006-12-05 03:47:12 [736] LOG: could not fsync segment 0 of relation 1663/16692/2361629: Permission denied 2006-12-05 03:47:12 [736] ERROR: storage sync failed on magnetic disk: Permission denied 2006-12-05 03:47:13 [736] ERROR: could not open relation 1663/16692/2361629: Permission denied 2006-12-05 03:47:14 [736] ERROR: could not open relation 1663/16692/2361629: Permission denied So what's holding the file open now? It's evidently not the bgwriter. one of the unnamed postgresql.exe processes from the connection pool: postgres: db_outnow outnow 127.0.0.1(3384) idle might be related: in addition to the above messages, the log is now also flooded by: 2006-12-05 04:16:29 [5196] LOG: could not rename temporary statistics file global/pgstat.tmp to global/pgstat.stat: A blocking operation was interrupted by a call to WSACancelBlockingCall. there is no pgstat.tmp file in global... - 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
2006-12-05 03:47:12 [736] LOG: could not fsync segment 0 of relation 1663/16692/2361629: Permission denied 2006-12-05 03:47:12 [736] ERROR: storage sync failed on magnetic disk: Permission denied 2006-12-05 03:47:13 [736] ERROR: could not open relation 1663/16692/2361629: Permission denied 2006-12-05 03:47:14 [736] ERROR: could not open relation 1663/16692/2361629: Permission denied So what's holding the file open now? It's evidently not the bgwriter. btw: FileMon reports every few seconds: 04:24:28 postgres.exe:736 OPEN D:\DB\postgreSQL.82\data\base\16692\2361629 DELETE PEND Options: Open Access: 0012019F the time corresponds to the could not open relation logentries i would interpret this as: postgresql pid 736 (bgwriter) is trying to open the file 2361629 which fails because it is marked as to be deleted. the file system operation is pending because another process (from the pgsql connection pool) is still keeping a handle open. as it is a connection pool process, it will be recycled after a while and release open handles: everytime the error messages disappear after some minutes... - 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
Thomas H. [EMAIL PROTECTED] writes: So what's holding the file open now? It's evidently not the bgwriter. one of the unnamed postgresql.exe processes from the connection pool: postgres: db_outnow outnow 127.0.0.1(3384) idle Hm. I would imagine that as soon as this process does something, the messages stop? (It should close its file handle in response to a relcache flush that it will read as soon as it becomes active.) regards, tom lane ---(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
Thomas H. [EMAIL PROTECTED] writes: ... in addition to the above messages, the log is now also flooded by: 2006-12-05 04:16:29 [5196] LOG: could not rename temporary statistics file global/pgstat.tmp to global/pgstat.stat: A blocking operation was interrupted by a call to WSACancelBlockingCall. Hm ... there simply isn't anything that holds pgstat.stat open for long, so this behavior seems independent of any other issues we might have. Can you find any evidence about what's wrong here? regards, tom lane ---(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
... in addition to the above messages, the log is now also flooded by: 2006-12-05 04:16:29 [5196] LOG: could not rename temporary statistics file global/pgstat.tmp to global/pgstat.stat: A blocking operation was interrupted by a call to WSACancelBlockingCall. Hm ... there simply isn't anything that holds pgstat.stat open for long, so this behavior seems independent of any other issues we might have. Can you find any evidence about what's wrong here? hope this helps: 05:33:14 postgres.exe:5196 CREATE D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Options: OverwriteIf Access: 00120196 05:33:14 postgres.exe:5196 WRITE D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Offset: 0 Length: 4096 05:33:14 postgres.exe:5196 WRITE D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Offset: 4096 Length: 4096 05:33:14 postgres.exe:5196 WRITE D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Offset: 8192 Length: 4096 05:33:14 postgres.exe:5196 WRITE D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Offset: 12288 Length: 4096 05:33:14 postgres.exe:5196 WRITE D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Offset: 16384 Length: 4096 05:33:14 postgres.exe:5196 WRITE D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Offset: 20480 Length: 4096 05:33:14 postgres.exe:5196 WRITE D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Offset: 24576 Length: 4096 05:33:14 postgres.exe:5196 WRITE D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Offset: 28672 Length: 4096 05:33:14 postgres.exe:5196 WRITE D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Offset: 32768 Length: 4096 05:33:14 postgres.exe:5196 WRITE D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Offset: 36864 Length: 4096 05:33:14 postgres.exe:5196 WRITE D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Offset: 40960 Length: 4096 05:33:14 postgres.exe:5196 WRITE D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Offset: 45056 Length: 4096 05:33:14 postgres.exe:5196 WRITE D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Offset: 49152 Length: 1650 05:33:14 postgres.exe:5196 CLOSE D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS 05:33:14 postgres.exe:5196 OPEN D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Options: Open Access: 00110080 05:33:14 postgres.exe:5196 QUERY INFORMATION D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS FileAttributeTagInformation 05:33:14 postgres.exe:5196 QUERY INFORMATION D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Attributes: A 05:33:14 postgres.exe:5196 OPEN D:\DB\postgreSQL.82\data\global\pgstat.stat SUCCESS Options: Open Access: 0012 05:33:14 postgres.exe:5196 SET INFORMATION D:\DB\postgreSQL.82\data\global\pgstat.tmp * 0xC123 FileRenameInformation 05:33:14 postgres.exe:5196 CLOSE D:\DB\postgreSQL.82\data\global\pgstat.stat SUCCESS 05:33:14 postgres.exe:5196 OPEN D:\DB\postgreSQL.82\data\global\pgstat.tmp NOT FOUND Options: Open Access: 00110080 05:33:14 postgres.exe:5196 OPEN D:\DB\postgreSQL.82\data\global\pgstat.tmp NOT FOUND Options: Open Access: 00010080 05:33:14 postgres.exe:5196 CREATE D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Options: OverwriteIf Access: 00120196 05:33:14 postgres.exe:5196 WRITE D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Offset: 0 Length: 4096 05:33:14 postgres.exe:5196 WRITE D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Offset: 4096 Length: 4096 05:33:14 postgres.exe:5196 WRITE D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Offset: 8192 Length: 4096 05:33:14 postgres.exe:5196 WRITE D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Offset: 12288 Length: 4096 05:33:14 postgres.exe:5196 WRITE D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Offset: 16384 Length: 4096 05:33:14 postgres.exe:5196 WRITE D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Offset: 20480 Length: 4096 05:33:14 postgres.exe:5196 WRITE D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Offset: 24576 Length: 4096 05:33:14 postgres.exe:5196 WRITE D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Offset: 28672 Length: 4096 05:33:14 postgres.exe:5196 WRITE D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Offset: 32768 Length: 4096 05:33:14 postgres.exe:5196 WRITE D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Offset: 36864 Length: 4096 05:33:14 postgres.exe:5196 WRITE D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Offset: 40960 Length: 4096 05:33:14 postgres.exe:5196 WRITE D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Offset: 45056 Length: 4096 05:33:14 postgres.exe:5196 WRITE D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Offset: 49152 Length: 1650 05:33:14 postgres.exe:5196 CLOSE D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS 05:33:14 postgres.exe:5196 OPEN D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Options: Open Access: 00110080 05:33:14 postgres.exe:5196 QUERY INFORMATION D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS FileAttributeTagInformation 05:33:14
Re: [BUGS] fsync and semctl errors with 8.1.5/win32
So what's holding the file open now? It's evidently not the bgwriter. one of the unnamed postgresql.exe processes from the connection pool: postgres: db_outnow outnow 127.0.0.1(3384) idle Hm. I would imagine that as soon as this process does something, the messages stop? (It should close its file handle in response to a relcache flush that it will read as soon as it becomes active.) from what i observe i would say the process dies (timeouts?) and then bgwriter is happy again: here's *all* more information i got from filemon when filtering for one of the relation that produced the error: http://rafb.net/paste/results/3uozHD77.html its pid 3772 that still has a handle open, while all the others have closed it properly after pid 2780 issued a DELETE. the process itself has 3 threads that are in: - postgres.exe+0x1220 - postgres.exe!pg_queue_signal+0x120 - postgres.exe!shmctl+0x80 (i can get stacktraces for all of them if usefull) pid 3772 died at 05:55:22 (~20min after its last access to the file), and bgwriter could finally write, and the error messages are gone. - thomas ---(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
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
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
Re: [BUGS] fsync and semctl errors with 8.1.5/win32
OK - after uninstalling the virus scanner (McAfee), I still get the same disk access errors. Here's a few seconds of the log output (this has been going on for 10 mins as of this e-mail being sent): 2006-11-28 16:16:10 LOG: could not fsync segment 0 of relation 1663/16404/30267: Permission denied 2006-11-28 16:16:10 ERROR: storage sync failed on magnetic disk: Permission denied 2006-11-28 16:16:11 LOG: could not fsync segment 0 of relation 1663/16404/30267: Permission denied 2006-11-28 16:16:11 ERROR: storage sync failed on magnetic disk: Permission denied 2006-11-28 16:16:12 LOG: could not fsync segment 0 of relation 1663/16404/30267: Permission denied 2006-11-28 16:16:12 ERROR: storage sync failed on magnetic disk: Permission denied 2006-11-28 16:16:13 LOG: could not fsync segment 0 of relation 1663/16404/30267: Permission denied 2006-11-28 16:16:13 ERROR: storage sync failed on magnetic disk: Permission denied Here's the FileMon output from the same seconds: 4:16:10 PM postgres.exe:3168 OPENC:\Program Files\PostgreSQL\8.1\data\base\16404\30267 DELETE PEND Options: Open Access: 0012019F 4:16:11 PM postgres.exe:3168 OPENC:\Program Files\PostgreSQL\8.1\data\base\16404\30267 DELETE PEND Options: Open Access: 0012019F 4:16:12 PM postgres.exe:3168 OPENC:\Program Files\PostgreSQL\8.1\data\base\16404\30267 DELETE PEND Options: Open Access: 0012019F 4:16:13 PM postgres.exe:3168 OPENC:\Program Files\PostgreSQL\8.1\data\base\16404\30267 DELETE PEND Options: Open Access: 0012019F This is an incredibly bad problem for me. I'd really appreciate any help! Jeremy On Mon, 27 Nov 2006 12:14:00 -0500, Jeremy Haile [EMAIL PROTECTED] said: Thanks for the feedback. If you don't mind, what version of PostgreSQL are you running? I'm trying to bring PostgreSQL into this company - they are primarily a Windows/SQL Server shop (although Java software development) I've already gotten comments similar to Why don't you just switch to SQL Server? - so I'm hoping to find a workaround before I get forced to switch DB platforms. As it is, my application seems unreliable because I haven't been able to resolve the PostgreSQL hanging problems in Windows. If I had my way, I'd switch the server to Linux - but alas, that hasn't been an option so far. I know this may be the wrong list to ask this question on - but as I'm an outspoken PostgreSQL advocate, I'd like your opinions. If I am unable to resolve these PostgreSQL issues given my constraints, will I likely have less problems running MySQL/InnoDB on Windows? (since it has had a native Windows build for much longer) On Mon, 27 Nov 2006 16:40:57 +, Dave Page [EMAIL PROTECTED] said: Jeremy Haile wrote: I've gotten pushback from my organization on removing antivirus from the servers completely. Are there any antiviruses that are known to be compatible with PostgreSQL/win32? All my boxes (2 build farm members, 1 production server, and the laptop on which the official releases are built and tested) run Sophos Anti Virus (http://www.sophos.com/products/es/endpoint/sav.html), with no problems. Regards, Dave ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(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
I forgot to mention - this problem is occurring on multiple Windows machines. One of them is running Windows XP Professional. The other is running Windows Server 2003. I have disabled indexing, virus scanning, and all non-essential services on both of them. The problem continues to show up even when no queries are being run (although it might always start while queries are running) On Tue, 28 Nov 2006 16:18:56 -0500, Jeremy Haile [EMAIL PROTECTED] said: OK - after uninstalling the virus scanner (McAfee), I still get the same disk access errors. Here's a few seconds of the log output (this has been going on for 10 mins as of this e-mail being sent): 2006-11-28 16:16:10 LOG: could not fsync segment 0 of relation 1663/16404/30267: Permission denied 2006-11-28 16:16:10 ERROR: storage sync failed on magnetic disk: Permission denied 2006-11-28 16:16:11 LOG: could not fsync segment 0 of relation 1663/16404/30267: Permission denied 2006-11-28 16:16:11 ERROR: storage sync failed on magnetic disk: Permission denied 2006-11-28 16:16:12 LOG: could not fsync segment 0 of relation 1663/16404/30267: Permission denied 2006-11-28 16:16:12 ERROR: storage sync failed on magnetic disk: Permission denied 2006-11-28 16:16:13 LOG: could not fsync segment 0 of relation 1663/16404/30267: Permission denied 2006-11-28 16:16:13 ERROR: storage sync failed on magnetic disk: Permission denied Here's the FileMon output from the same seconds: 4:16:10 PM postgres.exe:3168 OPENC:\Program Files\PostgreSQL\8.1\data\base\16404\30267 DELETE PEND Options: Open Access: 0012019F 4:16:11 PM postgres.exe:3168 OPENC:\Program Files\PostgreSQL\8.1\data\base\16404\30267 DELETE PEND Options: Open Access: 0012019F 4:16:12 PM postgres.exe:3168 OPENC:\Program Files\PostgreSQL\8.1\data\base\16404\30267 DELETE PEND Options: Open Access: 0012019F 4:16:13 PM postgres.exe:3168 OPENC:\Program Files\PostgreSQL\8.1\data\base\16404\30267 DELETE PEND Options: Open Access: 0012019F This is an incredibly bad problem for me. I'd really appreciate any help! Jeremy On Mon, 27 Nov 2006 12:14:00 -0500, Jeremy Haile [EMAIL PROTECTED] said: Thanks for the feedback. If you don't mind, what version of PostgreSQL are you running? I'm trying to bring PostgreSQL into this company - they are primarily a Windows/SQL Server shop (although Java software development) I've already gotten comments similar to Why don't you just switch to SQL Server? - so I'm hoping to find a workaround before I get forced to switch DB platforms. As it is, my application seems unreliable because I haven't been able to resolve the PostgreSQL hanging problems in Windows. If I had my way, I'd switch the server to Linux - but alas, that hasn't been an option so far. I know this may be the wrong list to ask this question on - but as I'm an outspoken PostgreSQL advocate, I'd like your opinions. If I am unable to resolve these PostgreSQL issues given my constraints, will I likely have less problems running MySQL/InnoDB on Windows? (since it has had a native Windows build for much longer) On Mon, 27 Nov 2006 16:40:57 +, Dave Page [EMAIL PROTECTED] said: Jeremy Haile wrote: I've gotten pushback from my organization on removing antivirus from the servers completely. Are there any antiviruses that are known to be compatible with PostgreSQL/win32? All my boxes (2 build farm members, 1 production server, and the laptop on which the official releases are built and tested) run Sophos Anti Virus (http://www.sophos.com/products/es/endpoint/sav.html), with no problems. Regards, Dave ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] fsync and semctl errors with 8.1.5/win32
I forgot to mention - this problem is occurring on multiple Windows machines. One of them is running Windows XP Professional. The other is running Windows Server 2003. I have disabled indexing, virus scanning, and all non-essential services on both of them. The problem continues to show up even when no queries are being run (although it might always start while queries are running) seems exactly what i'm noticing since 8.2x on windows 2003 as well - no disk services (backup, virus, ...) are running that would block files, and processmon/filemon always show that the files in question are locked by pgsql processes... under higher insert/update load, the errors appear more often here, do you experience the same finding when loading bulk data? - thomas ---(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
Jeremy Haile [EMAIL PROTECTED] writes: Here's a few seconds of the log output (this has been going on for 10 mins as of this e-mail being sent): 2006-11-28 16:16:10 LOG: could not fsync segment 0 of relation 1663/16404/30267: Permission denied 2006-11-28 16:16:10 ERROR: storage sync failed on magnetic disk: Permission denied Here's the FileMon output from the same seconds: 4:16:10 PM postgres.exe:3168 OPENC:\Program Files\PostgreSQL\8.1\data\base\16404\30267 DELETE PEND Options: Open Access: 0012019F I still don't want to make mdsync() treat EACCES as an ignorable error. However, in this situation we've got an infinite loop because the checkpoint will never succeed and thus the bgwriter will never reach smgrcloseall(), which seems to be what's needed to allow the deleted file to die the real death. Perhaps a suitable workaround would be to make the bgwriter do smgrcloseall in its error recovery path? That is /* * Sleep at least 1 second after any error. A write error is likely * to be repeated, and we don't want to be filling the error logs as * fast as we can. */ pg_usleep(100L); + + /* Drop open files to allow deleted files to really go away */ + smgrcloseall(); } /* We can now handle ereport(ERROR) */ PG_exception_stack = local_sigjmp_buf; Perhaps this should be #ifdef WIN32, although there's probably no harm in doing it on Unixen too. Can someone test this idea? regards, tom lane ---(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] fsync and semctl errors with 8.1.5/win32
Yes - processmon always shows the files being locked by postgres.exe processes. My database is being used as a data warehouse, so about all I am doing is bulk insert/updates. I have a job that runs every 5 minutes and loads data into the database. I typically load between 10,000 and 100,000 rows every 5 minutes into my fact tables, although I also make use of transition tables, dimension tables, etc. that get inserts/updates as well. I see the problem occur 3-4 times a day on average, but I don't know how to reproduce it other than letting it run for a while. On Tue, 28 Nov 2006 22:31:42 +0100, Thomas H. [EMAIL PROTECTED] said: I forgot to mention - this problem is occurring on multiple Windows machines. One of them is running Windows XP Professional. The other is running Windows Server 2003. I have disabled indexing, virus scanning, and all non-essential services on both of them. The problem continues to show up even when no queries are being run (although it might always start while queries are running) seems exactly what i'm noticing since 8.2x on windows 2003 as well - no disk services (backup, virus, ...) are running that would block files, and processmon/filemon always show that the files in question are locked by pgsql processes... under higher insert/update load, the errors appear more often here, do you experience the same finding when loading bulk data? - thomas ---(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
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 6: explain analyze is your friend
Re: [BUGS] fsync and semctl errors with 8.1.5/win32
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
[BUGS] fsync and semctl errors with 8.1.5/win32
I've been attempting to run PostgreSQL 8.1.5/win32 on a production deployment, but have started having many problems. McAfee Antivirus is installed and running, although I've excluded the entire drive where PostgreSQL is installed and where the data is installed. I've received several errors in the past few days/weeks. They fall into three general categories 1) permission denied errors 2) semctl errors 3) fsync errors. I am not sure how to reproduce these errors locally - they seem to occur at unpredictable intervals. The following posts seem related, although I don't see a resolution for any of the problems listed: http://www.mail-archive.com/pgsql-bugs@postgresql.org/msg16097.html http://www.mail-archive.com/pgsql-bugs@postgresql.org/msg14792.html http://www.mail-archive.com/pgsql-bugs@postgresql.org/msg14916.html I have run PostgreSQL on Linux in the past and not had any problems. Is the win32 build generally considered stable or unstable for production use? Any help would be greatly appreciated! 1) PERMISSION DENIED ERROR This error occurred on the same day as the semctl started, but stopped occurring for a few hours before the semctl errors started. The following is an example: 2006-11-25 00:46:04 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:05 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:06 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:07 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:08 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:09 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:10 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:11 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:12 ERROR: could not open relation 1663/16404/84855: Permission denied 2) SEMCTL ERROR This error occurred over and over one day with the same pattern - several semctl errors, then the unexpected EOF. This resulted in clients being unable to create database connections. The error occurred overnight and into the next day, and did not disappear until postgres was restarted. The following is an example: 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 LOG: could not receive data from client: No connection could be made because the target machine actively refused it. 2006-11-25 22:10:03 LOG: unexpected EOF on client connection 3) FSYNC ERROR I've seen this error several times in the past - including today. The following is an example: 2006-11-27 00:00:20 LOG: autovacuum: processing database incommDashboard 2006-11-27 00:00:20 LOG: could not fsync segment 0 of relation 1663/16404/89952: Permission denied 2006-11-27 00:00:20 ERROR: storage sync failed on magnetic disk: Permission denied 2006-11-27 00:00:24 LOG: could not fsync segment 0 of relation 1663/16404/89952: Permission denied 2006-11-27 00:00:24 ERROR: storage sync failed on magnetic disk: Permission denied 2006-11-27 00:00:26 LOG: could not fsync segment 0 of relation 1663/16404/89952: Permission denied 2006-11-27 00:00:26 ERROR: storage sync failed on magnetic disk: Permission denied 2006-11-27 00:00:29 LOG: could not fsync segment 0 of relation 1663/16404/89952: Permission denied 2006-11-27 00:00:29 ERROR: storage sync failed on magnetic disk: Permission denied 2006-11-27 00:00:32 LOG: could not fsync segment 0 of relation 1663/16404/89952: Permission denied 2006-11-27 00:00:32 ERROR: storage sync failed on magnetic
Re: [BUGS] fsync and semctl errors with 8.1.5/win32
Per the FAQ, we suggest that you *uninstall* your antivirus. Especially if it has firewall-like functionality (like I beleive McAfee does). Just disabling the scan does *not* remove the filter drivers and does not make the antivirus not affect the database processes. So try this. If the problem doesn't go away, look for something else installed that might be interfernig with the normal operation of your windows install. //Magnus -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jeremy Haile Sent: den 27 november 2006 15:21 To: pgsql-bugs@postgresql.org Subject: [BUGS] fsync and semctl errors with 8.1.5/win32 I've been attempting to run PostgreSQL 8.1.5/win32 on a production deployment, but have started having many problems. McAfee Antivirus is installed and running, although I've excluded the entire drive where PostgreSQL is installed and where the data is installed. I've received several errors in the past few days/weeks. They fall into three general categories 1) permission denied errors 2) semctl errors 3) fsync errors. I am not sure how to reproduce these errors locally - they seem to occur at unpredictable intervals. The following posts seem related, although I don't see a resolution for any of the problems listed: http://www.mail-archive.com/pgsql-bugs@postgresql.org/msg16097.html http://www.mail-archive.com/pgsql-bugs@postgresql.org/msg14792.html http://www.mail-archive.com/pgsql-bugs@postgresql.org/msg14916.html I have run PostgreSQL on Linux in the past and not had any problems. Is the win32 build generally considered stable or unstable for production use? Any help would be greatly appreciated! 1) PERMISSION DENIED ERROR This error occurred on the same day as the semctl started, but stopped occurring for a few hours before the semctl errors started. The following is an example: 2006-11-25 00:46:04 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:05 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:06 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:07 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:08 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:09 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:10 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:11 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:12 ERROR: could not open relation 1663/16404/84855: Permission denied 2) SEMCTL ERROR This error occurred over and over one day with the same pattern - several semctl errors, then the unexpected EOF. This resulted in clients being unable to create database connections. The error occurred overnight and into the next day, and did not disappear until postgres was restarted. The following is an example: 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 LOG: could not receive data from client: No connection could be made because the target machine actively refused it. 2006-11-25 22:10:03 LOG: unexpected EOF on client connection 3) FSYNC ERROR I've seen this error several times in the past - including today. The following is an example: 2006-11-27 00:00:20 LOG: autovacuum: processing database incommDashboard 2006-11-27 00:00:20 LOG: could not fsync segment 0 of relation 1663/16404/89952
Re: [BUGS] fsync and semctl errors with 8.1.5/win32
Thanks Magnus. I will uninstall the AntiVirus and see if my problems persist. I have disabled all other non-essential services, indexing, etc. so I don't know of anything else that could be causing the problems. However, in some of the posts I referred to, the poster indicated that they were not running antivirus software and still experienced the problems I'm having. I'll repost if I do or don't continue to experience problems after uninstalling the antivirus. On Mon, 27 Nov 2006 15:58:33 +0100, Magnus Hagander [EMAIL PROTECTED] said: Per the FAQ, we suggest that you *uninstall* your antivirus. Especially if it has firewall-like functionality (like I beleive McAfee does). Just disabling the scan does *not* remove the filter drivers and does not make the antivirus not affect the database processes. So try this. If the problem doesn't go away, look for something else installed that might be interfernig with the normal operation of your windows install. //Magnus -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jeremy Haile Sent: den 27 november 2006 15:21 To: pgsql-bugs@postgresql.org Subject: [BUGS] fsync and semctl errors with 8.1.5/win32 I've been attempting to run PostgreSQL 8.1.5/win32 on a production deployment, but have started having many problems. McAfee Antivirus is installed and running, although I've excluded the entire drive where PostgreSQL is installed and where the data is installed. I've received several errors in the past few days/weeks. They fall into three general categories 1) permission denied errors 2) semctl errors 3) fsync errors. I am not sure how to reproduce these errors locally - they seem to occur at unpredictable intervals. The following posts seem related, although I don't see a resolution for any of the problems listed: http://www.mail-archive.com/pgsql-bugs@postgresql.org/msg16097.html http://www.mail-archive.com/pgsql-bugs@postgresql.org/msg14792.html http://www.mail-archive.com/pgsql-bugs@postgresql.org/msg14916.html I have run PostgreSQL on Linux in the past and not had any problems. Is the win32 build generally considered stable or unstable for production use? Any help would be greatly appreciated! 1) PERMISSION DENIED ERROR This error occurred on the same day as the semctl started, but stopped occurring for a few hours before the semctl errors started. The following is an example: 2006-11-25 00:46:04 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:05 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:06 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:07 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:08 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:09 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:10 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:11 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:12 ERROR: could not open relation 1663/16404/84855: Permission denied 2) SEMCTL ERROR This error occurred over and over one day with the same pattern - several semctl errors, then the unexpected EOF. This resulted in clients being unable to create database connections. The error occurred overnight and into the next day, and did not disappear until postgres was restarted. The following is an example: 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could
Re: [BUGS] fsync and semctl errors with 8.1.5/win32
I've gotten pushback from my organization on removing antivirus from the servers completely. Are there any antiviruses that are known to be compatible with PostgreSQL/win32? On Mon, 27 Nov 2006 10:28:23 -0500, Jeremy Haile [EMAIL PROTECTED] said: Thanks Magnus. I will uninstall the AntiVirus and see if my problems persist. I have disabled all other non-essential services, indexing, etc. so I don't know of anything else that could be causing the problems. However, in some of the posts I referred to, the poster indicated that they were not running antivirus software and still experienced the problems I'm having. I'll repost if I do or don't continue to experience problems after uninstalling the antivirus. On Mon, 27 Nov 2006 15:58:33 +0100, Magnus Hagander [EMAIL PROTECTED] said: Per the FAQ, we suggest that you *uninstall* your antivirus. Especially if it has firewall-like functionality (like I beleive McAfee does). Just disabling the scan does *not* remove the filter drivers and does not make the antivirus not affect the database processes. So try this. If the problem doesn't go away, look for something else installed that might be interfernig with the normal operation of your windows install. //Magnus -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jeremy Haile Sent: den 27 november 2006 15:21 To: pgsql-bugs@postgresql.org Subject: [BUGS] fsync and semctl errors with 8.1.5/win32 I've been attempting to run PostgreSQL 8.1.5/win32 on a production deployment, but have started having many problems. McAfee Antivirus is installed and running, although I've excluded the entire drive where PostgreSQL is installed and where the data is installed. I've received several errors in the past few days/weeks. They fall into three general categories 1) permission denied errors 2) semctl errors 3) fsync errors. I am not sure how to reproduce these errors locally - they seem to occur at unpredictable intervals. The following posts seem related, although I don't see a resolution for any of the problems listed: http://www.mail-archive.com/pgsql-bugs@postgresql.org/msg16097.html http://www.mail-archive.com/pgsql-bugs@postgresql.org/msg14792.html http://www.mail-archive.com/pgsql-bugs@postgresql.org/msg14916.html I have run PostgreSQL on Linux in the past and not had any problems. Is the win32 build generally considered stable or unstable for production use? Any help would be greatly appreciated! 1) PERMISSION DENIED ERROR This error occurred on the same day as the semctl started, but stopped occurring for a few hours before the semctl errors started. The following is an example: 2006-11-25 00:46:04 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:05 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:06 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:07 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:08 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:09 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:10 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:11 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:12 ERROR: could not open relation 1663/16404/84855: Permission denied 2) SEMCTL ERROR This error occurred over and over one day with the same pattern - several semctl errors, then the unexpected EOF. This resulted in clients being unable to create database connections. The error occurred overnight and into the next day, and did not disappear until postgres was restarted. The following is an example: 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0
Re: [BUGS] fsync and semctl errors with 8.1.5/win32
Jeremy Haile wrote: I've gotten pushback from my organization on removing antivirus from the servers completely. Are there any antiviruses that are known to be compatible with PostgreSQL/win32? All my boxes (2 build farm members, 1 production server, and the laptop on which the official releases are built and tested) run Sophos Anti Virus (http://www.sophos.com/products/es/endpoint/sav.html), with no problems. Regards, Dave ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] fsync and semctl errors with 8.1.5/win32
Thanks for the feedback. If you don't mind, what version of PostgreSQL are you running? I'm trying to bring PostgreSQL into this company - they are primarily a Windows/SQL Server shop (although Java software development) I've already gotten comments similar to Why don't you just switch to SQL Server? - so I'm hoping to find a workaround before I get forced to switch DB platforms. As it is, my application seems unreliable because I haven't been able to resolve the PostgreSQL hanging problems in Windows. If I had my way, I'd switch the server to Linux - but alas, that hasn't been an option so far. I know this may be the wrong list to ask this question on - but as I'm an outspoken PostgreSQL advocate, I'd like your opinions. If I am unable to resolve these PostgreSQL issues given my constraints, will I likely have less problems running MySQL/InnoDB on Windows? (since it has had a native Windows build for much longer) On Mon, 27 Nov 2006 16:40:57 +, Dave Page [EMAIL PROTECTED] said: Jeremy Haile wrote: I've gotten pushback from my organization on removing antivirus from the servers completely. Are there any antiviruses that are known to be compatible with PostgreSQL/win32? All my boxes (2 build farm members, 1 production server, and the laptop on which the official releases are built and tested) run Sophos Anti Virus (http://www.sophos.com/products/es/endpoint/sav.html), with no problems. Regards, Dave ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
