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-bu.../msg16097.html
http://www.mail-archive.com/pgsql-bu.../msg14792.html
http://www.mail-archive.com/pgsql-bu.../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

---------------------------(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

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=20

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

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 (AT) sollentuna (DOT) net> said:
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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 (AT) fastmail (DOT) fm>
said:
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Jeremy Haile wrote:
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

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 (AT) postgresql (DOT) org>
said:
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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 (AT) fastmail (DOT) fm>
said:
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

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 (AT) fastmail (DOT) fm>
said:
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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 majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

"Jeremy Haile" <jhaile (AT) fastmail (DOT) fm> writes:
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

---------------------------(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