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 disk:
Permission denied
2006-11-27 00:00:42 LOG: could not fsync segment 0 of relation
1663/16404/89952: Permission denied
2006-11-27 00:00:42 ERROR: storage sync failed on magnetic disk:
Permission denied
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
Show quoted text
-----Original Message-----
From: pgsql-bugs-owner@postgresql.org
[mailto:pgsql-bugs-owner@postgresql.org] 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/win32I'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.htmlI 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 denied2) 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 connection3) 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 disk:
Permission denied
2006-11-27 00:00:42 LOG: could not fsync segment 0 of relation
1663/16404/89952: Permission denied
2006-11-27 00:00:42 ERROR: storage sync failed on magnetic disk:
Permission denied---------------------------(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
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"
<mha@sollentuna.net> said:
Show quoted text
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: pgsql-bugs-owner@postgresql.org
[mailto:pgsql-bugs-owner@postgresql.org] 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/win32I'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.htmlI 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 denied2) 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 connection3) 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 disk:
Permission denied
2006-11-27 00:00:42 LOG: could not fsync segment 0 of relation
1663/16404/89952: Permission denied
2006-11-27 00:00:42 ERROR: storage sync failed on magnetic disk:
Permission denied---------------------------(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
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" <jhaile@fastmail.fm>
said:
Show quoted text
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"
<mha@sollentuna.net> 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: pgsql-bugs-owner@postgresql.org
[mailto:pgsql-bugs-owner@postgresql.org] 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/win32I'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.htmlI 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 denied2) 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 connection3) 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 disk:
Permission denied
2006-11-27 00:00:42 LOG: could not fsync segment 0 of relation
1663/16404/89952: Permission denied
2006-11-27 00:00:42 ERROR: storage sync failed on magnetic disk:
Permission denied---------------------------(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 5: don't forget to increase your free space map settings
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
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 +0000, "Dave Page" <dpage@postgresql.org>
said:
Show quoted text
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
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 OPEN C:\Program
Files\PostgreSQL\8.1\data\base\16404\30267 DELETE PEND Options:
Open Access: 0012019F
4:16:11 PM postgres.exe:3168 OPEN C:\Program
Files\PostgreSQL\8.1\data\base\16404\30267 DELETE PEND Options:
Open Access: 0012019F
4:16:12 PM postgres.exe:3168 OPEN C:\Program
Files\PostgreSQL\8.1\data\base\16404\30267 DELETE PEND Options:
Open Access: 0012019F
4:16:13 PM postgres.exe:3168 OPEN C:\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" <jhaile@fastmail.fm>
said:
Show quoted text
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 +0000, "Dave Page" <dpage@postgresql.org>
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
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" <jhaile@fastmail.fm>
said:
Show quoted text
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 deniedHere's the FileMon output from the same seconds:
4:16:10 PM postgres.exe:3168 OPEN C:\Program
Files\PostgreSQL\8.1\data\base\16404\30267 DELETE PEND Options:
Open Access: 0012019F
4:16:11 PM postgres.exe:3168 OPEN C:\Program
Files\PostgreSQL\8.1\data\base\16404\30267 DELETE PEND Options:
Open Access: 0012019F
4:16:12 PM postgres.exe:3168 OPEN C:\Program
Files\PostgreSQL\8.1\data\base\16404\30267 DELETE PEND Options:
Open Access: 0012019F
4:16:13 PM postgres.exe:3168 OPEN C:\Program
Files\PostgreSQL\8.1\data\base\16404\30267 DELETE PEND Options:
Open Access: 0012019FThis 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" <jhaile@fastmail.fm>
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 +0000, "Dave Page" <dpage@postgresql.org>
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
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
"Jeremy Haile" <jhaile@fastmail.fm> 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 OPEN C:\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(1000000L);
+
+ /* 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
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.
Show quoted text
On Tue, 28 Nov 2006 22:31:42 +0100, "Thomas H." <me@alternize.com> 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
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
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.
Show quoted text
On Tue, 28 Nov 2006 23:39:00 +0100, "Thomas H." <me@alternize.com> 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
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/00000001000000190000005E" to
"pg_xlog/00000001000000190000007F", continuing to try
On Tue, 28 Nov 2006 17:49:22 -0500, "Jeremy Haile" <jhaile@fastmail.fm>
said:
Show quoted text
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." <me@alternize.com> 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
2006-11-29 23:57:52 LOG: could not rename file
"pg_xlog/00000001000000190000005E" to
"pg_xlog/00000001000000190000007F", continuing to try
i had this one as well. good news is: this bug is fixed in 8.2
- thomas
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?
Show quoted text
On Thu, 30 Nov 2006 15:14:46 +0100, "Thomas H." <me@alternize.com> said:
2006-11-29 23:57:52 LOG: could not rename file
"pg_xlog/00000001000000190000005E" to
"pg_xlog/00000001000000190000007F", continuing to tryi had this one as well. good news is: this bug is fixed in 8.2
- thomas
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
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: pgsql-bugs-owner@postgresql.org
[mailto:pgsql-bugs-owner@postgresql.org] 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
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
Import Notes
Reference msg id not found: 008001c71493$c2c46f70$4a0a10ac@npci.com | Resolved by subject fallback
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:me@alternize.com]
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