Postgres on NT freezing - Mailing list pgsql-hackers

Hi all,

I have been using PostgreSQL 6.4.2 on Debain GNU/Linux
for a few months. I am using the Windows ODBC drivers
and a C++/MFC client program.
I fixed a few bugs in the ODBC drivers and now
everything works perfectly.

Now the downside.
Unfortunately our current client insists on using NT not Linux.

I have installed PostgreSQL 6.5 on cygwin 20.1 on NT 4.0
(See "Install log" below for exact versions of the various components.)
I am having freeze up problems. I recompiled with both
-g and -O2 turned off in the vain that hope it might help.
"postmaster -i -d 3" and "postmaster -i -o -F" both
exhibit intermittent freeze ups when a new backend starts.

In LOG1 below I started the postmaster, connected from
psql on my linux box, then quit straight away. Works fine.

LOG2 below shows one successful connection followed by
one the freezes at InitPostgres. There were dozens of
good connections before the one shown here.

I restarted "postmaster -i" (no debug messages) and
was able to run 8 clients simultaneously producing
about 8 connections per second. This was stable.
I tried it again a few times and it mostly worked
but froze sometimes.

Running "postmaster -i -o -F" (no fsync) brought back
the instability.

I noticed that after a freeze I would be less likely to
freeze again if I restarted the ipc-daemon. At one point
the ipc daemon was using 25% cpu after I had killed all
the crashed backends.

My client opens and closes the connection to the back
end allot. I tried altering it to keep the connections
open (not feasible for our release version) and this
made things more stable due to there being fewer
connections. So the query handling seems ok, the
freeze only happens when a new backend starts.

Can anyone suggest anything that I might do to fix this?
I realize PG on NT is a young port. I may be able to spend
a little time on this myself if someone can point me in
the right direction.

Thanks for your help

Sam O'Connor

=========================================================
Install log:
=========================================================

Installed: Microsoft Windows NT Workstation 4.0.1381 SP5
Installed: Cygwin Beta 20.1 (full.exe)
           /sw
           made /bin with sh.exe
           made /tmp
           made etc with passwd & group
           ln -s cygtclsh80 tclsh80 tclsh
Installed: Andy Pipers /usr/local for Cygwin B20
           added /usr/local/bin to path in cygnus.bat
Installed: EGCS 1.1.2 release for Cygwin B20.1
           gcc -v : gcc version egcs-2.91.66 19990314 (egcs-1.1.2
release)
Installed: IPC for Cygwin32 1.03
           added shortcut to ipc-daemon.exe to Startup folder
Untared:   postgresql-6.5.tar.gz in /usr/local/src
           copied src/win32/*.h into Cygwin include directories
           set CFLAGS to "" (no -O2 or -g) in src/template/cygwin32
           ./configure
           make
           mkdir /usr/local/pgsql
           mkdir /usr/local/pgsql/data
           make install
           set PGLIB, PGDATA, PATH, USER
           initdb
           added to data/ph_hba.conf: host all 10.0.0.0 255.255.255.0
trust
           postmaster -i -d 3

Installed: PostgreSQL ODBC Driver 6.40.0006

==================================================
LOG1 successful backend log: postmaster -i -d 3
==================================================

FindExec: found "/usr/local/pgsql/bin/postgres" using argv[0]
binding ShmemCreate(key=52e2c1, size=1073152)
/usr/local/pgsql/bin/postmaster: ServerLoop:            handling reading
9
/usr/local/pgsql/bin/postmaster: ServerLoop:            handling reading
9
/usr/local/pgsql/bin/postmaster: ServerLoop:            handling writing
9
/usr/local/pgsql/bin/postmaster: BackendStartup: environ dump:
-----------------------------------------
        !C:=C:\WINNT\Profiles\Administrator\Desktop
        COMPUTERNAME=WORM
        COMSPEC=C:\WINNT\system32\cmd.exe
        HOMEDRIVE=C:
        HOMEPATH=\
        HOSTNAME=worm
        HOSTTYPE=i586
        INCLUDE=C:\Program Files\Microsoft Visual
Studio\VC98\atl\include;C:\Program Files\Microsoft Visual
Studio\VC98\mfc\include;
C:\Program Files\Microsoft Visual Studio\VC98\include
        LIB=C:\Program Files\Microsoft Visual
Studio\VC98\mfc\lib;C:\Program Files\Microsoft Visual Studio\VC98\lib
        LOGONSERVER=\\WORM
        MACHTYPE=i586-pc-cygwin32
        MAKE_MODE=UNIX
        MSDEVDIR=C:\Program Files\Microsoft Visual Studio\Common\MSDev98
        NUMBER_OF_PROCESSORS=1
        OS2LIBPATH=C:\WINNT\system32\os2\dll;
        OS=Windows_NT
        OSTYPE=cygwin32

PATH=/usr/local/pgsql/bin:/usr/local/pgsql/bin:/sw/CYGWIN~1/H-I586~1/bin:/usr/local/bin:/WINNT/system32:/WINNT:/Program
File
s/Microsoft Visual Studio/Common/Tools/WinNT:/Program Files/Microsoft
Visual Studio/Common/MSDev98/Bin:/Program Files/Microsoft Visu
al Studio/Common/Tools:/Program Files/Microsoft Visual Studio/VC98/bin
        PATHEXT=.COM;.EXE;.BAT;.CMD
        PGDATA=/usr/local/pgsql/data
        PGHOME=/usr/local/pgsql
        PGLIB=/usr/local/pgsql/lib
        PROCESSOR_ARCHITECTURE=x86
        PROCESSOR_IDENTIFIER=x86 Family 6 Model 0 Stepping 0,
CyrixInstead
        PROCESSOR_LEVEL=6
        PROCESSOR_REVISION=0000
        PROMPT=$P$G
        PWD=/usr/local/pgsql/data
        SHELL=/bin/sh
        SHLVL=1
        SYSTEMDRIVE=C:
        SYSTEMROOT=C:\WINNT
        TEMP=C:\TEMP
        TERM=cygwin
        TMP=C:\TEMP
        USER=administrator
        USERDOMAIN=WORM
        USERNAME=Administrator
        USERPROFILE=C:\WINNT\Profiles\Administrator
        WINDIR=C:\WINNT
        _=/usr/local/pgsql/bin/postmaster
        POSTPORT=5432
        POSTID=2147483647
        PG_USER=administrator
        IPC_KEY=5432000
-----------------------------------------
/usr/local/pgsql/bin/postmaster child[2528]: starting with
(/usr/local/pgsql/bin/postgres -d3 -v131072 -p template1 )
FindExec: found "/usr/local/pgsql/bin/postgres" using argv[0]
debug info:
        User         = administrator
        RemoteHost   = 10.0.0.100
        RemotePort   = 5271
        DatabaseName = template1
        Verbose      = 3
        Noversion    = f
        timings      = f
        dates        = Normal
        bufsize      = 64
        sortmem      = 512
        query echo   = f
InitPostgres
/usr/local/pgsql/bin/postmaster: BackendStartup: pid 2528 user
administrator db template1 socket 9
Error semaphore semaphore not equal 0
Error semaphore semaphore not equal 0
Error semaphore semaphore not equal 0
Error semaphore semaphore not equal 0
proc_exit(0) [#0]
shmem_exit(0) [#0]
exit(0)
/usr/local/pgsql/bin/postmaster: reaping dead processes...
/usr/local/pgsql/bin/postmaster: CleanupProc: pid 2528 exited with
status 0


===============================================
LOG2 failed backend log: postmaster -i -d 3
-----------------------------------------------
note: "Error semaphore semaphore not equal 0"
===============================================
exit(0)
/usr/local/pgsql/bin/postmaster: reaping dead processes...
/usr/local/pgsql/bin/postmaster: CleanupProc: pid 2589 exited with
status 0
/usr/local/pgsql/bin/postmaster: ServerLoop:            handling reading
9
/usr/local/pgsql/bin/postmaster: ServerLoop:            handling reading
9
/usr/local/pgsql/bin/postmaster: ServerLoop:            handling writing
9
/usr/local/pgsql/bin/postmaster: BackendStartup: environ dump:
-----------------------------------------
        !C:=C:\WINNT\Profiles\Administrator\Desktop
        COMPUTERNAME=WORM
        COMSPEC=C:\WINNT\system32\cmd.exe
        HOMEDRIVE=C:
        HOMEPATH=\
        HOSTNAME=worm
        HOSTTYPE=i586
        INCLUDE=C:\Program Files\Microsoft Visual
Studio\VC98\atl\include;C:\Program Files\Microsoft Visual
Studio\VC98\mfc\include;
C:\Program Files\Microsoft Visual Studio\VC98\include
        LIB=C:\Program Files\Microsoft Visual
Studio\VC98\mfc\lib;C:\Program Files\Microsoft Visual Studio\VC98\lib
        LOGONSERVER=\\WORM
        MACHTYPE=i586-pc-cygwin32
        MAKE_MODE=UNIX
        MSDEVDIR=C:\Program Files\Microsoft Visual Studio\Common\MSDev98
        NUMBER_OF_PROCESSORS=1
        OS2LIBPATH=C:\WINNT\system32\os2\dll;
        OS=Windows_NT
        OSTYPE=cygwin32

PATH=/usr/local/pgsql/bin:/usr/local/pgsql/bin:/sw/CYGWIN~1/H-I586~1/bin:/usr/local/bin:/WINNT/system32:/WINNT:/Program
File
s/Microsoft Visual Studio/Common/Tools/WinNT:/Program Files/Microsoft
Visual Studio/Common/MSDev98/Bin:/Program Files/Microsoft Visu
al Studio/Common/Tools:/Program Files/Microsoft Visual Studio/VC98/bin
        PATHEXT=.COM;.EXE;.BAT;.CMD
        PGDATA=/usr/local/pgsql/data
        PGHOME=/usr/local/pgsql
        PGLIB=/usr/local/pgsql/lib
        PROCESSOR_ARCHITECTURE=x86
        PROCESSOR_IDENTIFIER=x86 Family 6 Model 0 Stepping 0,
CyrixInstead
        PROCESSOR_LEVEL=6
        PROCESSOR_REVISION=0000
        PROMPT=$P$G
        PWD=/usr/local/pgsql/data
        SHELL=/bin/sh
        SHLVL=1
        SYSTEMDRIVE=C:
        SYSTEMROOT=C:\WINNT
        TEMP=C:\TEMP
        TERM=cygwin
        TMP=C:\TEMP
        USER=administrator
        USERDOMAIN=WORM
        USERNAME=Administrator
        USERPROFILE=C:\WINNT\Profiles\Administrator
        WINDIR=C:\WINNT
        _=/usr/local/pgsql/bin/postmaster
        POSTPORT=5432
        POSTID=2147483614
        PG_USER=administrator
        IPC_KEY=5432000
-----------------------------------------
/usr/local/pgsql/bin/postmaster child[2590]: starting with
(/usr/local/pgsql/bin/postgres -d3 -v131072 -p mentor )
/usr/local/pgsql/bin/postmaster: BackendStartup: pid 2590 user
administrator db mentor socket 9
FindExec: found "/usr/local/pgsql/bin/postgres" using argv[0]
debug info:
        User         = administrator
        RemoteHost   = 127.0.0.1
        RemotePort   = 1084
        DatabaseName = mentor
        Verbose      = 3
        Noversion    = f
        timings      = f
        dates        = Normal
        bufsize      = 64
        sortmem      = 512
        query echo   = f
InitPostgres
Error semaphore semaphore not equal 0
Error semaphore semaphore not equal 0
Error semaphore semaphore not equal 0
Error semaphore semaphore not equal 0
StartTransactionCommand
query: set DateStyle to 'ISO'
ProcessUtility: set DateStyle to 'ISO'
CommitTransactionCommand
StartTransactionCommand
query: set geqo to 'OFF'
ProcessUtility: set geqo to 'OFF'
CommitTransactionCommand
StartTransactionCommand
query: set ksqo to 'ON'
ProcessUtility: set ksqo to 'ON'
CommitTransactionCommand
StartTransactionCommand
query: select oid from pg_type where typname='lo'
ProcessQuery
CommitTransactionCommand
StartTransactionCommand
query: SELECT * FROM released_document WHERE document_id = 2
ProcessQuery
CommitTransactionCommand
StartTransactionCommand
query: SELECT popup_id, content FROM popup WHERE document_id = 2 AND
document_version = 0 ORDER BY popup_id
ProcessQuery
CommitTransactionCommand
StartTransactionCommand
query: SELECT link_id, type, vector FROM link WHERE document_id = 2 AND
document_version = 0
ProcessQuery
CommitTransactionCommand
StartTransactionCommand
query: SELECT section_number, name FROM section WHERE document_id = 2
AND document_version = 0 ORDER BY section_number
ProcessQuery
CommitTransactionCommand
StartTransactionCommand
query: SELECT text, textel_number, type FROM typed_textel WHERE
document_id = 2 AND document_version = 0 AND section_number = 0 ORDE
R BY textel_number
ProcessQuery
CommitTransactionCommand
StartTransactionCommand
query: SELECT text, textel_number, type FROM typed_textel WHERE
document_id = 2 AND document_version = 0 AND section_number = 1 ORDE
R BY textel_number
ProcessQuery
CommitTransactionCommand
StartTransactionCommand
query: SELECT text, textel_number, type FROM typed_textel WHERE
document_id = 2 AND document_version = 0 AND section_number = 2 ORDE
R BY textel_number
ProcessQuery
CommitTransactionCommand
StartTransactionCommand
query: SELECT text, textel_number, type FROM typed_textel WHERE
document_id = 2 AND document_version = 0 AND section_number = 3 ORDE
R BY textel_number
ProcessQuery
CommitTransactionCommand
pq_recvbuf: unexpected EOF on client connection
proc_exit(0) [#0]
shmem_exit(0) [#0]
exit(0)
/usr/local/pgsql/bin/postmaster: reaping dead processes...
/usr/local/pgsql/bin/postmaster: CleanupProc: pid 2590 exited with
status 0
/usr/local/pgsql/bin/postmaster: ServerLoop:            handling reading
9
/usr/local/pgsql/bin/postmaster: ServerLoop:            handling reading
9
/usr/local/pgsql/bin/postmaster: ServerLoop:            handling writing
9
/usr/local/pgsql/bin/postmaster: BackendStartup: environ dump:
-----------------------------------------
        !C:=C:\WINNT\Profiles\Administrator\Desktop
        COMPUTERNAME=WORM
        COMSPEC=C:\WINNT\system32\cmd.exe
        HOMEDRIVE=C:
        HOMEPATH=\
        HOSTNAME=worm
        HOSTTYPE=i586
        INCLUDE=C:\Program Files\Microsoft Visual
Studio\VC98\atl\include;C:\Program Files\Microsoft Visual
Studio\VC98\mfc\include;
C:\Program Files\Microsoft Visual Studio\VC98\include
        LIB=C:\Program Files\Microsoft Visual
Studio\VC98\mfc\lib;C:\Program Files\Microsoft Visual Studio\VC98\lib
        LOGONSERVER=\\WORM
        MACHTYPE=i586-pc-cygwin32
        MAKE_MODE=UNIX
        MSDEVDIR=C:\Program Files\Microsoft Visual Studio\Common\MSDev98
        NUMBER_OF_PROCESSORS=1
        OS2LIBPATH=C:\WINNT\system32\os2\dll;
        OS=Windows_NT
        OSTYPE=cygwin32

PATH=/usr/local/pgsql/bin:/usr/local/pgsql/bin:/sw/CYGWIN~1/H-I586~1/bin:/usr/local/bin:/WINNT/system32:/WINNT:/Program
File
s/Microsoft Visual Studio/Common/Tools/WinNT:/Program Files/Microsoft
Visual Studio/Common/MSDev98/Bin:/Program Files/Microsoft Visu
al Studio/Common/Tools:/Program Files/Microsoft Visual Studio/VC98/bin
        PATHEXT=.COM;.EXE;.BAT;.CMD
        PGDATA=/usr/local/pgsql/data
        PGHOME=/usr/local/pgsql
        PGLIB=/usr/local/pgsql/lib
        PROCESSOR_ARCHITECTURE=x86
        PROCESSOR_IDENTIFIER=x86 Family 6 Model 0 Stepping 0,
CyrixInstead
        PROCESSOR_LEVEL=6
        PROCESSOR_REVISION=0000
        PROMPT=$P$G
        PWD=/usr/local/pgsql/data
        SHELL=/bin/sh
        SHLVL=1
        SYSTEMDRIVE=C:
        SYSTEMROOT=C:\WINNT
        TEMP=C:\TEMP
        TERM=cygwin
        TMP=C:\TEMP
        USER=administrator
        USERDOMAIN=WORM
        USERNAME=Administrator
        USERPROFILE=C:\WINNT\Profiles\Administrator
        WINDIR=C:\WINNT
        _=/usr/local/pgsql/bin/postmaster
        POSTPORT=5432
        POSTID=2147483613
        PG_USER=administrator
        IPC_KEY=5432000
-----------------------------------------
/usr/local/pgsql/bin/postmaster child[2591]: starting with
(/usr/local/pgsql/bin/postgres -d3 -v131072 -p mentor )
/usr/local/pgsql/bin/postmaster: BackendStartup: pid 2591 user
administrator db mentor socket 9
FindExec: found "/usr/local/pgsql/bin/postgres" using argv[0]
debug info:
        User         = administrator
        RemoteHost   = 127.0.0.1
        RemotePort   = 1085
        DatabaseName = mentor
        Verbose      = 3
        Noversion    = f
        timings      = f
        dates        = Normal
        bufsize      = 64
        sortmem      = 512
        query echo   = f
InitPostgres

===========================================================
End of log
===========================================================