Postgres 9.0 crash on win7

Started by Andrea Peri 2007over 15 years ago16 messagesbugs

Jump to latest

Andrea Peri 2007

aperi2007@gmail.com

over 15 years ago

Hi,

I'm using usually
Postgres 8.4.4 (32bit) + Postgis 1.5.1 on Windows7 64bit.

Now I try-ing the last
Postgres 9.0 (32bit) + Postgis 1.5.2 on the same machine (win7 64bit).

I experience a

crash of Postgres while it is running a huge load of data.

This is the report of log.

2010-10-01 22:38:55 CEST LOG: last completed transaction was at log time
2010-10-01 22:23:51.389+02
2010-10-01 22:38:56 CEST LOG: autovacuum launcher started
2010-10-01 22:38:56 CEST LOG: database system is ready to accept
connections
2010-10-01 22:44:20 CEST LOG: server process (PID 2540) was terminated by
exception 0xC0000005
2010-10-01 22:44:20 CEST HINT: See C include file "ntstatus.h" for a
description of the hexadecimal value.
2010-10-01 22:44:20 CEST LOG: terminating any other active server processes
2010-10-01 22:44:20 CEST WARNING: terminating connection because of crash
of another server process
2010-10-01 22:44:20 CEST DETAIL: The postmaster has commanded this server
process to roll back the current transaction and exit, because another
server process exited abnormally and possibly corrupted shared memory.
2010-10-01 22:44:20 CEST HINT: In a moment you should be able to reconnect
to the database and repeat your command.
2010-10-01 22:44:20 CEST WARNING: terminating connection because of crash
of another server process
2010-10-01 22:44:20 CEST DETAIL: The postmaster has commanded this server
process to roll back the current transaction and exit, because another
server process exited abnormally and possibly corrupted shared memory.
2010-10-01 22:44:20 CEST HINT: In a moment you should be able to reconnect
to the database and repeat your command.
2010-10-01 22:44:20 CEST LOG: all server processes terminated;
reinitializing
2010-10-01 22:44:31 CEST FATAL: pre-existing shared memory block is still
in use
2010-10-01 22:44:31 CEST HINT: Check if there are any old server processes
still running, and terminate them.

The tablespace where I load the data is new create from postgres9.0

After seeing this.
I try load the same data load procedure (on the same windows machine) using
the postgres 8.4.4 istance and all terminated without any problem.

The procedure is substantially only a huge list of
string sql like

insert into(....)

executed one by one in many tables and with autocommit.

Andrea Peri.

--
-----------------
Andrea Peri
. . . . . . . . .
qwerty àèìòù
-----------------

Craig Ringer

craig@2ndquadrant.com

over 15 years ago

In reply to: Andrea Peri 2007 (#1)

Re: Postgres 9.0 crash on win7

On 2/10/2010 9:08 PM, Andrea Peri wrote:

Hi,

I'm using usually
Postgres 8.4.4 (32bit) + Postgis 1.5.1 on Windows7 64bit.

Now I try-ing the last
Postgres 9.0 (32bit) + Postgis 1.5.2 on the same machine (win7 64bit).

I experience a

crash of Postgres while it is running a huge load of data.

Does that include PostGIS datatypes?

2010-10-01 22:44:20 CEST LOG: server process (PID 2540) was terminated
by exception 0xC0000005

That's invalid memory access - like a UNIX segfault (sig11).

Can you show your schema - the definition of the table(s) involved in
the INSERT and any triggers on them? The output of:

\d+ tablename

from psql would do the trick.

Truly, the most helpful thing at this point would be to collect a
backtrace showing where in the postgresql server it crashed. There are
instructions on how to do that here:

http://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Windows

In your case, as the backend is crashing you will want to use windbg or
Visual Studio Express Edition to collect the crash data; process
explorer will not be enough.

--
Craig Ringer

Tech-related writing at http://soapyfrogs.blogspot.com/

Andrea Peri 2007

aperi2007@gmail.com

over 15 years ago

In reply to: Craig Ringer (#2)

Re: Postgres 9.0 crash on win7

Hi, thx for response.

Does that include PostGIS datatypes?

yes, but after some email with the guys of Posgis team , I think the problem
is related to postgres.

(see this thread on postgis ML):
http://postgis.refractions.net/pipermail/postgis-users/2010-October/027841.html

The postgis team was using a my script sql (about 10mbyte, 1Mb compress)
that always crash a PG9 32bit on windows7 64 bit.

Using that script and setting the verbosity as Postgis team suggest I see
this report:

--- start log ---
2010-10-03 10:46:53 CEST LOG:  00000: database system was shut down at
2010-10-03 10:46:37 CEST
2010-10-03 10:46:53 CEST LOCATION:  StartupXLOG,
.\src\backend\access\transam\xlog.c:5713
2010-10-03 10:46:53 CEST FATAL:  57P03: the database system is starting up
2010-10-03 10:46:53 CEST LOCATION:  ProcessStartupPacket,
.\src\backend\postmaster\postmaster.c:1818
2010-10-03 10:46:54 CEST LOG:  00000: autovacuum launcher started
2010-10-03 10:46:54 CEST LOCATION:  AutoVacLauncherMain,
.\src\backend\postmaster\autovacuum.c:404
2010-10-03 10:46:54 CEST LOG:  00000: database system is ready to accept
connections
2010-10-03 10:46:54 CEST LOCATION:  reaper,
.\src\backend\postmaster\postmaster.c:2402
   --- --- the script start here at 10:48 ----
2010-10-03 10:48:51 CEST LOG:  00000: server process (PID 5076) was
terminated by exception 0xC0000005
2010-10-03 10:48:51 CEST HINT:  See C include file "ntstatus.h" for a
description of the hexadecimal value.
2010-10-03 10:48:51 CEST LOCATION:  LogChildExit,
.\src\backend\postmaster\postmaster.c:2835
2010-10-03 10:48:51 CEST LOG:  00000: terminating any other active server
processes
2010-10-03 10:48:51 CEST LOCATION:  HandleChildCrash,
.\src\backend\postmaster\postmaster.c:2659
2010-10-03 10:48:51 CEST WARNING:  57P02: terminating connection because of
crash of another server process
2010-10-03 10:48:51 CEST DETAIL:  The postmaster has commanded this server
process to roll back the current transaction and exit, because another
server process exited abnormally and possibly corrupted shared memory.
2010-10-03 10:48:51 CEST HINT:  In a moment you should be able to reconnect
to the database and repeat your command.
2010-10-03 10:48:51 CEST LOCATION:  quickdie,
.\src\backend\tcop\postgres.c:2626
2010-10-03 10:48:51 CEST LOG:  00000: all server processes terminated;
reinitializing
2010-10-03 10:48:51 CEST LOCATION:  PostmasterStateMachine,
.\src\backend\postmaster\postmaster.c:3079
2010-10-03 10:49:01 CEST FATAL:  XX000: pre-existing shared memory block is
still in use
2010-10-03 10:49:01 CEST HINT:  Check if there are any old server processes
still running, and terminate them.
2010-10-03 10:49:01 CEST LOCATION:  PGSharedMemoryCreate,
.\src\backend\port\win32_shmem.c:194
  ----- the script take 1 minute and after it was terminate, 35 seconds
after postgres 9.0 crash. ----
  --- at the 10:50 I restart the service PostgresSql. and see other
information about the crash----
2010-10-03 10:50:54 CEST LOG:  00000: database system was interrupted; last
known up at 2010-10-03 10:46:53 CEST
2010-10-03 10:50:54 CEST LOCATION:  StartupXLOG,
.\src\backend\access\transam\xlog.c:5737
2010-10-03 10:50:54 CEST LOG:  00000: database system was not properly shut
down; automatic recovery in progress
2010-10-03 10:50:54 CEST LOCATION:  StartupXLOG,
.\src\backend\access\transam\xlog.c:5962
2010-10-03 10:50:54 CEST LOG:  00000: consistent recovery state reached at
0/2557DA0
2010-10-03 10:50:54 CEST LOCATION:  CheckRecoveryConsistency,
.\src\backend\access\transam\xlog.c:6566
2010-10-03 10:50:54 CEST LOG:  00000: redo starts at 0/2557DA0
2010-10-03 10:50:54 CEST LOCATION:  StartupXLOG,
.\src\backend\access\transam\xlog.c:6154
2010-10-03 10:50:54 CEST FATAL:  57P03: the database system is starting up
2010-10-03 10:50:54 CEST LOCATION:  ProcessStartupPacket,
.\src\backend\postmaster\postmaster.c:1818
2010-10-03 10:50:55 CEST FATAL:  57P03: the database system is starting up
2010-10-03 10:50:55 CEST LOCATION:  ProcessStartupPacket,
.\src\backend\postmaster\postmaster.c:1818
2010-10-03 10:50:57 CEST FATAL:  57P03: the database system is starting up
2010-10-03 10:50:57 CEST LOCATION:  ProcessStartupPacket,
.\src\backend\postmaster\postmaster.c:1818
2010-10-03 10:50:58 CEST FATAL:  57P03: the database system is starting up
2010-10-03 10:50:58 CEST LOCATION:  ProcessStartupPacket,
.\src\backend\postmaster\postmaster.c:1818
2010-10-03 10:50:59 CEST FATAL:  57P03: the database system is starting up
2010-10-03 10:50:59 CEST LOCATION:  ProcessStartupPacket,
.\src\backend\postmaster\postmaster.c:1818
2010-10-03 10:51:00 CEST LOG:  00000: record with zero length at 0/331FFC8
2010-10-03 10:51:00 CEST LOCATION:  ReadRecord,
.\src\backend\access\transam\xlog.c:3765
2010-10-03 10:51:00 CEST LOG:  00000: redo done at 0/331FF88
2010-10-03 10:51:00 CEST LOCATION:  StartupXLOG,
.\src\backend\access\transam\xlog.c:6253
2010-10-03 10:51:00 CEST LOG:  00000: last completed transaction was at log
time 2010-10-03 10:48:50.977+02
2010-10-03 10:51:00 CEST LOCATION:  StartupXLOG,
.\src\backend\access\transam\xlog.c:6258
2010-10-03 10:51:00 CEST FATAL:  57P03: the database system is starting up
2010-10-03 10:51:00 CEST LOCATION:  ProcessStartupPacket,
.\src\backend\postmaster\postmaster.c:1818
2010-10-03 10:51:01 CEST LOG:  00000: autovacuum launcher started
2010-10-03 10:51:01 CEST LOCATION:  AutoVacLauncherMain,
.\src\backend\postmaster\autovacuum.c:404
2010-10-03 10:51:01 CEST LOG:  00000: database system is ready to accept
connections
2010-10-03 10:51:01 CEST LOCATION:  reaper,
.\src\backend\postmaster\postmaster.c:2402
---- end of log ----

Can you show your schema - the definition of the table(s) involved in the

INSERT and any triggers on them? The output of:

\d+ tablename

from psql would do the trick.

If you like I can send you the script.
It create may tables and start to populate using many inserts.

The real script is more big then this, but this 10mbytes is sufficient to
crash PG9.

However the main table involved is this:

Truly, the most helpful thing at this point would be to collect a backtrace

showing where in the postgresql server it crashed. There are instructions on
how to do that here:

http://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Windows<http://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Windows>

In your case, as the backend is crashing you will want to use windbg or

Visual Studio Express Edition to collect the crash data; process explorer
will not be enough.

ok, now I try to do this backtrace.

Regards,

Andrea.

2010/10/3 Craig Ringer <craig@postnewspapers.com.au>

On 2/10/2010 9:08 PM, Andrea Peri wrote:

Hi,

I'm using usually
Postgres 8.4.4 (32bit) + Postgis 1.5.1 on Windows7 64bit.

Now I try-ing the last
Postgres 9.0 (32bit) + Postgis 1.5.2 on the same machine (win7 64bit).

I experience a

crash of Postgres while it is running a huge load of data.

Does that include PostGIS datatypes?

2010-10-01 22:44:20 CEST LOG: server process (PID 2540) was terminated

by exception 0xC0000005

That's invalid memory access - like a UNIX segfault (sig11).

Can you show your schema - the definition of the table(s) involved in the
INSERT and any triggers on them? The output of:

\d+ tablename

from psql would do the trick.

Truly, the most helpful thing at this point would be to collect a backtrace
showing where in the postgresql server it crashed. There are instructions on
how to do that here:

http://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Windows

In your case, as the backend is crashing you will want to use windbg or
Visual Studio Express Edition to collect the crash data; process explorer
will not be enough.

--
Craig Ringer

Tech-related writing at http://soapyfrogs.blogspot.com/

--
-----------------
Andrea Peri
. . . . . . . . .
qwerty àèìòù
-----------------

Andrea Peri 2007

aperi2007@gmail.com

over 15 years ago

In reply to: Andrea Peri 2007 (#3)

Re: Postgres 9.0 crash on win7

Truly, the most helpful thing at this point would be to collect a

backtrace showing where in the postgresql server it crashed.

There are instructions on how to do that here:

http://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Windows

In your case, as the backend is crashing you will want to use windbg

or Visual Studio Express Edition to

collect the crash data; process explorer will not be enough.

Hi,

I collect the trace information of the crash using windbg.

If get two set of trace, the first is when postgres start in the windbg
session.

---  the first set ---

*** wait with pending attach
Symbol search path is: C:\Program Files
(x86)\PostgreSQL\9.0\symbols;SRV*c:\localsymbols*http://msdl.microsoft.com/download/symbols
Executable search path is:
ModLoad: 00000000`003f0000 00000000`008f6000 C:\Program Files
(x86)\PostgreSQL\9.0\bin\postgres.exe
ModLoad: 00000000`77940000 00000000`77aeb000 C:\Windows\SYSTEM32\ntdll.dll
ModLoad: 00000000`77b20000 00000000`77ca0000 ntdll.dll
ModLoad: 00000000`73dc0000 00000000`73dff000 C:\Windows\SYSTEM32\wow64.dll
ModLoad: 00000000`73d60000 00000000`73dbc000
C:\Windows\SYSTEM32\wow64win.dll
ModLoad: 00000000`73d50000 00000000`73d58000
C:\Windows\SYSTEM32\wow64cpu.dll
ModLoad: 00000000`76af0000 00000000`76bf0000 KERNEL32.dll
ModLoad: 00000000`77640000 00000000`77686000 KERNELBASE.dll
ModLoad: 00000000`10000000 00000000`10033000 SSLEAY32.dll
ModLoad: 00000000`00140000 00000000`0023e000 LIBEAY32.dll
ModLoad: 00000000`739f0000 00000000`739f7000 WSOCK32.dll
ModLoad: 00000000`771b0000 00000000`771e5000 WS2_32.dll
ModLoad: 00000000`756f0000 00000000`7579c000 msvcrt.dll
ModLoad: 00000000`76bf0000 00000000`76ce0000 RPCRT4.dll
ModLoad: 00000000`75690000 00000000`756f0000 SspiCli.dll
ModLoad: 00000000`75680000 00000000`7568c000 CRYPTBASE.dll
ModLoad: 00000000`77700000 00000000`77719000 SECHOST.dll
ModLoad: 00000000`775a0000 00000000`775a6000 NSI.dll
ModLoad: 00000000`77110000 00000000`771a0000 GDI32.dll
ModLoad: 00000000`757a0000 00000000`758a0000 USER32.dll
ModLoad: 00000000`77310000 00000000`773b0000 ADVAPI32.dll
ModLoad: 00000000`771a0000 00000000`771aa000 LPK.dll
ModLoad: 00000000`76fe0000 00000000`7707d000 USP10.dll
ModLoad: 00000000`734a0000 00000000`73543000 MSVCR90.dll
ModLoad: 00000000`61cc0000 00000000`61cd3000 libintl-8.dll
ModLoad: 00000000`66000000 00000000`660e7000 libiconv-2.dll
ModLoad: 00000000`002c0000 00000000`003b1000 libxml2.dll
ModLoad: 00000000`00d90000 00000000`00e69000 iconv.dll
ModLoad: 00000000`003c0000 00000000`003d3000 zlib1.dll
ModLoad: 00000000`735d0000 00000000`735d8000 Secur32.dll
ModLoad: 00000000`76f90000 00000000`76fd5000 WLDAP32.dll
ModLoad: 00000000`76f30000 00000000`76f90000 IMM32.dll
ModLoad: 00000000`76500000 00000000`765cc000 MSCTF.dll
ModLoad: 00000000`735f0000 00000000`7362c000 MSWSOCK.dll
ModLoad: 00000000`73490000 00000000`73496000 WSHIP6.dll
ModLoad: 00000000`735e0000 00000000`735e5000 WSHTCPIP.dll
(ec0.fe4): Break instruction exception - code 80000003 (first chance)
ntdll!DbgBreakPoint:
00000000`7798f190 cc int 3
0:003> ~*k

0 Id: ec0.66c Suspend: 1 Teb: 00000000`7efdb000 Unfrozen
Child-SP RetAddr Call Site
00000000`0013e6b8 00000000`73d5282c wow64cpu!CpupSyscallStub+0x9
00000000`0013e6c0 00000000`73dcd07e wow64cpu!WaitForMultipleObjects32+0x32
00000000`0013e780 00000000`73dcc549 wow64!RunCpuSimulation+0xa
00000000`0013e7d0 00000000`779884c8 wow64!Wow64LdrpInitialize+0x429
00000000`0013ed20 00000000`77987623 ntdll!LdrpInitializeProcess+0x17e2
00000000`0013f220 00000000`7797308e ntdll! ?? ::FNODOBFM::`string'+0x2bea0
00000000`0013f290 00000000`00000000 ntdll!LdrInitializeThunk+0xe

1 Id: ec0.868 Suspend: 1 Teb: 00000000`7efad000 Unfrozen
Child-SP RetAddr Call Site
00000000`01e6eca8 00000000`73d52932 wow64cpu!CpupSyscallStub+0x9
00000000`01e6ecb0 00000000`73dcd07e wow64cpu!DeviceIoctlFileFault+0x31
00000000`01e6ed70 00000000`73dcc549 wow64!RunCpuSimulation+0xa
00000000`01e6edc0 00000000`779bd177 wow64!Wow64LdrpInitialize+0x429
00000000`01e6f310 00000000`7797308e ntdll! ?? ::FNODOBFM::`string'+0x2bfe4
00000000`01e6f380 00000000`00000000 ntdll!LdrInitializeThunk+0xe

2 Id: ec0.a50 Suspend: 1 Teb: 00000000`7efaa000 Unfrozen
Child-SP RetAddr Call Site
00000000`01fded28 00000000`73d52bcd wow64cpu!CpupSyscallStub+0x9
00000000`01fded30 00000000`73dcd07e wow64cpu!Thunk0ArgReloadState+0x1a
00000000`01fdedf0 00000000`73dcc549 wow64!RunCpuSimulation+0xa
00000000`01fdee40 00000000`779bd177 wow64!Wow64LdrpInitialize+0x429
00000000`01fdf390 00000000`7797308e ntdll! ?? ::FNODOBFM::`string'+0x2bfe4
00000000`01fdf400 00000000`00000000 ntdll!LdrInitializeThunk+0xe

The second set is when PG9 crash during execution of script sql
and windbg intercept the crash

-- the second set ---

# 3 Id: ec0.fe4 Suspend: 1 Teb: 00000000`7efd8000 Unfrozen
Child-SP RetAddr Call Site
00000000`019cfc58 00000000`77a38638 ntdll!DbgBreakPoint
00000000`019cfc60 00000000`779a39cb ntdll!DbgUiRemoteBreakin+0x38
00000000`019cfc90 00000000`00000000 ntdll!RtlUserThreadStart+0x25
0:003> G
ModLoad: 00000000`6e610000 00000000`6e637000 C:\Program Files
(x86)\PostgreSQL\9.0\lib\plpgsql.dll
ModLoad: 00000000`68f00000 00000000`68f7a000 C:\Program Files
(x86)\PostgreSQL\9.0\lib\postgis-1.5.dll
ModLoad: 00000000`70040000 00000000`700ad000 C:\Program Files
(x86)\PostgreSQL\9.0\bin\libgeos_c-1.dll
ModLoad: 00000000`68280000 00000000`68422000 C:\Program Files
(x86)\PostgreSQL\9.0\bin\libgeos-3-2-2.dll
ModLoad: 00000000`70f40000 00000000`71055000 C:\Program Files
(x86)\PostgreSQL\9.0\bin\libxml2-2.dll
ModLoad: 00000000`01090000 00000000`010c9000 C:\Program Files
(x86)\PostgreSQL\9.0\bin\libproj.dll
ntdll!NtTerminateProcess+0xa:
00000000`7799017a c3 ret
0:000> ~*k

. 0 Id: ec0.66c Suspend: 0 Teb: 00000000`7efdb000 Unfrozen
Child-SP RetAddr Call Site
00000000`0013ddc8 00000000`73dd601a ntdll!NtTerminateProcess+0xa
00000000`0013ddd0 00000000`73dccf87 wow64!whNtTerminateProcess+0x46
00000000`0013de00 00000000`73d5276d wow64!Wow64SystemServiceEx+0xd7
00000000`0013e6c0 00000000`73dcd07e
wow64cpu!TurboDispatchJumpAddressEnd+0x24
00000000`0013e780 00000000`73dcc549 wow64!RunCpuSimulation+0xa
00000000`0013e7d0 00000000`779884c8 wow64!Wow64LdrpInitialize+0x429
00000000`0013ed20 00000000`77987623 ntdll!LdrpInitializeProcess+0x17e2
00000000`0013f220 00000000`7797308e ntdll! ?? ::FNODOBFM::`string'+0x2bea0
00000000`0013f290 00000000`00000000 ntdll!LdrInitializeThunk+0xe

------------------------------------------------------------------------------------

I hope to have executed exactly as needed the instruction of
http://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Windows#Remote_debugging_with_windbg.exe

Regards,

Andrea Peri.

Import Notes

Resolved by subject fallback

Andrea Peri 2007

aperi2007@gmail.com

over 15 years ago

In reply to: Andrea Peri 2007 (#4)

Re: Postgres 9.0 crash on win7

Hi,

I have some update on the crash of pg9.0.

seem that PG9 will crash even on windows 32bit.

But meanwhile in win7-64 bit crash always at first try, in win7 32bit it
crash
from first and second time after restart.
As report here from Postgis Team.

http://postgis.refractions.net/pipermail/postgis-users/2010-October/027843.html

Regards,

Andrea.

Import Notes

Resolved by subject fallback

Craig Ringer

craig@2ndquadrant.com

over 15 years ago

In reply to: Andrea Peri 2007 (#5)

Re: Postgres 9.0 crash on win7

On 4/10/2010 4:41 AM, Andrea Peri 2007 wrote:

Hi,

I have some update on the crash of pg9.0.

seem that PG9 will crash even on windows 32bit.

Yes, it will. I've just been able to reproduce it here with your script,
on 32-bit win7. I should be able to report where it's crashing shortly.

I gave you bad advice on the backtrace, I'm afraid. A backtrace of a
32-bit process on 64-bit Windows, at least using the 64-bit debugging
tools, appears to be pretty useless. Having never tried that particular
setup before, I didn't realize that I'm afraid. I've added a note to
that effect to the instructions. Sorry about that.

I've been able to reproduce the crash (thanks for the test case!) and
obtain the crash information here on my 32-bit windows 7 install, so
there's no need for you to do anything else so far.

--
Craig Ringer

Tech-related writing at http://soapyfrogs.blogspot.com/

Craig Ringer

craig@2ndquadrant.com

over 15 years ago

In reply to: Andrea Peri 2007 (#3)

Re: Postgres 9.0 crash on win7

On 10/03/2010 05:11 PM, Andrea Peri wrote:

Hi, thx for response.

Does that include PostGIS datatypes?

yes, but after some email with the guys of Posgis team , I think the
problem is related to postgres.

(see this thread on postgis ML):
http://postgis.refractions.net/pipermail/postgis-users/2010-October/027841.html

The postgis team was using a my script sql (about 10mbyte, 1Mb compress)
that always crash a PG9 32bit on windows7 64 bit.

While it's consistently crashing my Pg 9 on win7 32-bit, too, I haven't
been able to get a backtrace yet. I thought it'd be trivial given the
ease of reproducing the crash - but the process that's crashing isn't
the backend running the query.

It looks like it's one of the helpers like the stats collector, autovac,
bgwriter, etc. I'm unsure which yet. I've had to go to work, so I won't
be able to pick it up again until much later today. When I get back I'll
turn the logging right up, set windbg up as the post-mortem debugger and
catch it that way.

I'm currently testing to see if I can reproduce the issue under linux as
well.

--
Craig Ringer

Tom Lane

tgl@sss.pgh.pa.us

over 15 years ago

In reply to: Craig Ringer (#7)

Re: Postgres 9.0 crash on win7

Craig Ringer <craig@postnewspapers.com.au> writes:

While it's consistently crashing my Pg 9 on win7 32-bit, too, I haven't
been able to get a backtrace yet. I thought it'd be trivial given the
ease of reproducing the crash - but the process that's crashing isn't
the backend running the query.

It looks like it's one of the helpers like the stats collector, autovac,
bgwriter, etc. I'm unsure which yet.

I'd bet on autovacuum. You might be able to reproduce the crash in the
foreground process by issuing a manual VACUUM or ANALYZE.

regards, tom lane

Craig Ringer

craig@2ndquadrant.com

over 15 years ago

In reply to: Tom Lane (#8)

Re: Postgres 9.0 crash on win7

On 04/10/10 10:56, Tom Lane wrote:

Craig Ringer <craig@postnewspapers.com.au> writes:

While it's consistently crashing my Pg 9 on win7 32-bit, too, I haven't
been able to get a backtrace yet. I thought it'd be trivial given the
ease of reproducing the crash - but the process that's crashing isn't
the backend running the query.

It looks like it's one of the helpers like the stats collector, autovac,
bgwriter, etc. I'm unsure which yet.

I'd bet on autovacuum. You might be able to reproduce the crash in the
foreground process by issuing a manual VACUUM or ANALYZE.

Thanks for the tip.

I can't reproduce this under Linux, so it'll be back to the Windows
gaming/testing desktop when I get home to see if I can catch it there.

--
Craig Ringer

Tech-related writing: http://soapyfrogs.blogspot.com/

#10

Craig Ringer

craig@2ndquadrant.com

over 15 years ago

In reply to: Craig Ringer (#6)

Re: Postgres 9.0 crash on win7

Just an update on this issue:

I've been able to reproduce the crash (thanks for the test case!) and
obtain the crash information here on my 32-bit windows 7 install, so
there's no need for you to do anything else so far.

I still can't get a usable backtrace. The autovacuum workers/launcher
split makes it *really* hard to catch an autovacuum worker in action.
The post-mortem debugger won't trigger for service processes, so I can't
trap it that way, and I can't pre-attach a debugger to it.

OTOH, it's now pretty clearly autovacuum that's dying, as Tom Lane
suggested it probably would be.

debug5 logging shows:

2010-10-04 18:18:54 WST 3692 DEBUG: InitPostgres
2010-10-04 18:18:54 WST 3692 DEBUG: my backend id is 3
2010-10-04 18:18:54 WST 3692 DEBUG: StartTransaction
2010-10-04 18:18:54 WST 3692 DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
2010-10-04 18:18:54 WST 3692 DEBUG: mapped win32 error code 2 to 2
2010-10-04 18:18:54 WST 3692 DEBUG: CommitTransaction
2010-10-04 18:18:54 WST 3692 DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
2010-10-04 18:18:54 WST 3692 DEBUG: autovacuum: processing database "test"
2010-10-04 18:18:54 WST 3692 DEBUG: StartTransaction
2010-10-04 18:18:54 WST 3692 DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
2010-10-04 18:18:54 WST 3692 DEBUG: pg_statistic: vac: 0 (threshold 118), anl: 0 (threshold 84)

... followed by lots more startup messages, a series of transactions, then:

2010-10-04 18:18:55 WST 3692 DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 159661/1/0 (used), nestlvl: 1, children:
2010-10-04 18:18:55 WST 3692 DEBUG: StartTransaction
2010-10-04 18:18:55 WST 3692 DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
2010-10-04 18:18:55 WST 3692 DEBUG: poslist: vac: 0 (threshold 50), anl: 1804 (threshold 50)
2010-10-04 18:18:55 WST 3692 DEBUG: autovac_balance_cost(pid=3692 db=98315, rel=98390, cost_limit=200, cost_delay=20)
2010-10-04 18:18:55 WST 3692 DEBUG: CommitTransaction
2010-10-04 18:18:55 WST 3692 DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
2010-10-04 18:18:55 WST 3692 DEBUG: StartTransaction
2010-10-04 18:18:55 WST 3692 DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
2010-10-04 18:18:55 WST 3692 DEBUG: analyzing "public.poslist"
2010-10-04 18:18:55 WST 2408 DEBUG: server process (PID 3692) was terminated by exception 0xC0000005
2010-10-04 18:18:55 WST 2408 LOG: server process (PID 3692) was terminated by exception 0xC0000005

Autovacuum usually dies after:

analyzing "public.suolo" (three times)

but I've also seen it die after:

analyzing "public.poslist"

as shown above.

I'm really struggling to get a debugger attached to the *@#$@#$@$* thing
though. Ideas?

*punting to PostGIS folks for a look*

--
Craig Ringer

Tech-related writing at http://soapyfrogs.blogspot.com/

#11

Andrea Peri 2007

aperi2007@gmail.com

over 15 years ago

In reply to: Tom Lane (#8)

Re: Postgres 9.0 crash on win7

Hi,

I have do some other test.

I set
autovacuum = off.
With this setting the server don't crash.

But certainly something go bad.
Infact
if (after run the query) I connect with posql as user 'postgres' and try
a single command
VACUUM;

nothing happened.

But if I try a
single command
ANALYZE;
the server crash istantly.

After this I restart the server and
re-try reconnecting to PG9 but without re-run the query script.

again
if a run
ANALYZE the server crash.

I think the script has insert write something wrong in a table and after
this insert.
Always time someone (autovacuum or user) try an analyze
this cause the crash.

however seem to be the analyze to do the crash, not the vacuum.

Perhaps to problem is in the INSERT INTO.
that put sometime that the old PG8.4.4 can understand and use, meanwhile
PG) don't understand or is not capable to use.

for example, I use often some field of big size:
VARCHAR(200000)

the next try I do is to remove the postgis components to see if again
this happened.

Andrea.

Il 04/10/2010 04:56, Tom Lane ha scritto:

Show quoted text

Craig Ringer<craig@postnewspapers.com.au> writes:

While it's consistently crashing my Pg 9 on win7 32-bit, too, I haven't
been able to get a backtrace yet. I thought it'd be trivial given the
ease of reproducing the crash - but the process that's crashing isn't
the backend running the query.
It looks like it's one of the helpers like the stats collector, autovac,
bgwriter, etc. I'm unsure which yet.

I'd bet on autovacuum. You might be able to reproduce the crash in the
foreground process by issuing a manual VACUUM or ANALYZE.

regards, tom lane

#12

Andrea Peri 2007

aperi2007@gmail.com

over 15 years ago

In reply to: Andrea Peri 2007 (#11)

Re: Postgres 9.0 crash on win7

Hi,

I do some others tests.

I f I remove all the call to postgis elements the PG0 don't crash.

If I set
autovacuum = off

and run this script (it is also in attach).

The PG9 don't crash istantly, but if after run the script I do
VACUUM;
ANALYZE;
the PG9 crash meanwhile execute the analyze.

It seem to be a problem of Postgis, but I have some doubt because
this same script work well on PG8.4.4 with postgis 1.5.2 (compiled for
postgres 8.4.4)
meanwhile on PG9 with postgis 1.5.2 (compiled for postgres 9.0) it crash
PG9.

-----
SELECT DropGeometryColumn('public','poslist','geom');
DROP TABLE IF EXISTS poslist;
SELECT DropGeometryColumn('public','suolo','geom');
DROP TABLE IF EXISTS suolo;
SELECT DropGeometryColumn('public','linee_elementari','geom');
DROP TABLE IF EXISTS linee_elementari;
CREATE TABLE SUOLO (CODPOLIGONO VARCHAR(35) PRIMARY KEY, CODCLASSE
VARCHAR(35), CODORIGINESUOLO VARCHAR(35), TAGNO INTEGER );
SELECT AddGeometryColumn( 'public','suolo', 'geom',3003, 'POLYGON', 2 );
alter table suolo add column oid serial not null;
CREATE TABLE POSLIST (ID VARCHAR(35) PRIMARY KEY, IDLINESTRING
VARCHAR(35), POSLIST VARCHAR(200000), LUNG_POSLIST INTEGER, NUM_VERTEX
INTEGER , DIMENSION VARCHAR(1) );
SELECT AddGeometryColumn( 'public','poslist', 'geom',3003, 'LINESTRING',
2 );
alter table poslist add column oid serial not null;
CREATE TABLE LINEE_ELEMENTARI (CODLINEA VARCHAR(35) PRIMARY KEY, CODCTR
VARCHAR(35), CODVISIBILITA VARCHAR(35), CODBREAKLINE VARCHAR(35),
CODCLASSE VARCHAR(35), CODLATOVESTIZIONE VARCHAR(35), CODORIGINELINEA
VARCHAR(35), CODMODIFICA VARCHAR(35), POSLIST VARCHAR(200000),
LUNG_POSLIST INTEGER, NUM_VERTEX INTEGER, IDEDGE VARCHAR(35), SRSNAME
VARCHAR(35), DIMENSION VARCHAR(1), COORD_N1 VARCHAR(50), COORD_N2
VARCHAR(50) );
SELECT AddGeometryColumn( 'public','linee_elementari', 'geom',3003,
'LINESTRING', 2 );
alter table linee_elementari add column oid serial not null;
-----

Regards,

Andrea Peri.

Import Notes

Resolved by subject fallback

#13

Craig Ringer

craig@2ndquadrant.com

over 15 years ago

In reply to: Tom Lane (#8)

Re: Postgres 9.0 crash on win7

On 4/10/2010 10:56 AM, Tom Lane wrote:

Craig Ringer<craig@postnewspapers.com.au> writes:

While it's consistently crashing my Pg 9 on win7 32-bit, too, I haven't
been able to get a backtrace yet. I thought it'd be trivial given the
ease of reproducing the crash - but the process that's crashing isn't
the backend running the query.

It looks like it's one of the helpers like the stats collector, autovac,
bgwriter, etc. I'm unsure which yet.

I'd bet on autovacuum. You might be able to reproduce the crash in the
foreground process by issuing a manual VACUUM or ANALYZE.

Thanks. I wasn't able to get the backend running the script to crash
initially, even with an explicit VACUUM, ANALYZE, or VACUUM ANALYZE.
After turning autovacuum off completely, though, it does crash when
ANALYZE is run.

postgres.exe!pfree(void * pointer=0x68f08610) Line 591 + 0x3 bytes C
postgres.exe!examine_attribute(RelationData * onerel=0x00000000, int attnum=5, Node * index_expr=0x00000000) Line 877 C
postgres.exe!do_analyze_rel(RelationData * onerel=0x01747b48, VacuumStmt * vacstmt=0x01690580, char update_reltuples='', char inh=0) Line 357 + 0xa bytes C
postgres.exe!analyze_rel(unsigned int relid=131097, VacuumStmt * vacstmt=0x01690580, BufferAccessStrategyData * bstrategy=0x018fc0f0, char update_reltuples='') Line 232 C
postgres.exe!vacuum(VacuumStmt * vacstmt=0x01690580, unsigned int relid=0, char do_toast='', BufferAccessStrategyData * bstrategy=0x018fc0f0, char for_wraparound=0, char isTopLevel='') Line 248 C
postgres.exe!standard_ProcessUtility(Node * parsetree=0x01690580, const char * queryString=0x0168fc78, ParamListInfoData * params=0x00000000, char isTopLevel='', _DestReceiver * dest=0x01690730, char * completionTag=0x004ff998) Line 1012 + 0x13 bytes C
postgres.exe!PortalRunUtility(PortalData * portal=0x00000000, Node * utilityStmt=0x00000000, char isTopLevel='', _DestReceiver * dest=0x01690730, char * completionTag=0x004ff998) Line 1199 C
postgres.exe!PortalRunMulti(PortalData * portal=0x00000000, char isTopLevel='', _DestReceiver * dest=0x01690730, _DestReceiver * altdest=0x01690730, char * completionTag=0x004ff998) Line 1298 + 0x11 bytes C
postgres.exe!PortalRun(PortalData * portal=0x016dd028, long count=2147483647, char isTopLevel='', _DestReceiver * dest=0x01690730, _DestReceiver * altdest=0x01690730, char * completionTag=0x004ff998) Line 823 + 0x17 bytes C
postgres.exe!exec_simple_query(const char * query_string=0x00000000) Line 1059 C
postgres.exe!PostgresMain(int argc=2, char * * argv=0x01635220, const char * username=0x009780b0) Line 3871 C
postgres.exe!BackendRun(Port * port=0x00000002) Line 3550 + 0x17 bytes C
postgres.exe!SubPostmasterMain(int argc=3, char * * argv=0x00972878) Line 4042 + 0x8 bytes C
postgres.exe!main(int argc=3, char * * argv=0x00972878) Line 165 + 0x7 bytes C
postgres.exe!__tmainCRTStartup() Line 586 + 0x17 bytes C
kernel32.dll!@BaseThreadInitThunk@12() + 0x12 bytes
ntdll.dll!___RtlUserThreadStart@8() + 0x27 bytes
ntdll.dll!__RtlUserThreadStart@8() + 0x1b bytes

It's crashing in pfree, as called by examine_attribute here:

if (!ok || stats->compute_stats == NULL || stats->minrows <= 0)
{
pfree(stats->attrtype);
pfree(stats->attr); <-- crash
pfree(stats);
return NULL;
}

... which is palloc'd earlier in examine_attribute.

VC++ is having trouble examining the locals in examine_attribute(); I'm
unsure if this is an optimization issue, lack of full debug info, or
something wrong with the state of the stack.

It's definitely crashing while analyzing the relation "suolo" - not only
do the logs show analysis beginning, but onerel->rd_rel->relname is
"suolo". At the time of the crash it seems to have already added the
column with attr->attname = "codpoligono" to vacattrstats and is
examining the column with attnum=5 when it crashes. A quick look at
pg_class and pg_attribute shows that this is (surprise!) the "geom"
column of type "geometry".

PostGIS on Windows is a bit outside my depth, especially as it's not a
neat crash in the analyze function its self. Hopefully this'll give the
PostGIS folks something to go on, though. Andrea: please pass it on.

--
Craig Ringer

Tech-related writing at http://soapyfrogs.blogspot.com/

#14

Tom Lane

tgl@sss.pgh.pa.us

over 15 years ago

In reply to: Craig Ringer (#13)

Re: Postgres 9.0 crash on win7

Craig Ringer <craig@postnewspapers.com.au> writes:

After turning autovacuum off completely, though, it does crash when
ANALYZE is run.

postgres.exe!pfree(void * pointer=0x68f08610) Line 591 + 0x3 bytes C
postgres.exe!examine_attribute(RelationData * onerel=0x00000000, int attnum=5, Node * index_expr=0x00000000) Line 877 C
postgres.exe!do_analyze_rel(RelationData * onerel=0x01747b48, VacuumStmt * vacstmt=0x01690580, char update_reltuples='', char inh=0) Line 357 + 0xa bytes C

Hmm. That is suspiciously close to the location of some last-minute
changes in Postgres 9.0. I wonder whether Andrea is using a version of
PostGIS that was compiled against pre-9.0RC1 Postgres sources. If they
weren't accounting for this patch:
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=67becf8d41a082eaaf6db6e0860d49409b79e32b
then we could easily have a crash right about here --- in fact it looks
like this is exactly what you'd get, because the extension would think
that the compute_stats field is where attrtype now is, so the
"pfree(stats->attrtype)" would be trying to pfree a function address.

In short, what we've got here is a version skew problem. That doubtless
explains why Craig couldn't duplicate it on his Linux machine.

regards, tom lane

#15

Craig Ringer

craig@2ndquadrant.com

over 15 years ago

In reply to: Tom Lane (#14)

Re: Postgres 9.0 crash on win7

On 5/10/2010 10:02 AM, Tom Lane wrote:

Craig Ringer<craig@postnewspapers.com.au> writes:

After turning autovacuum off completely, though, it does crash when
ANALYZE is run.

postgres.exe!pfree(void * pointer=0x68f08610) Line 591 + 0x3 bytes C
postgres.exe!examine_attribute(RelationData * onerel=0x00000000, int attnum=5, Node * index_expr=0x00000000) Line 877 C
postgres.exe!do_analyze_rel(RelationData * onerel=0x01747b48, VacuumStmt * vacstmt=0x01690580, char update_reltuples='', char inh=0) Line 357 + 0xa bytes C

Hmm. That is suspiciously close to the location of some last-minute
changes in Postgres 9.0. I wonder whether Andrea is using a version of
PostGIS that was compiled against pre-9.0RC1 Postgres sources.

If so, I am to and it's the latest PostGIS binary from their website.

In short, what we've got here is a version skew problem. That doubtless
explains why Craig couldn't duplicate it on his Linux machine.

Yep, 'cos I built PostGIS directly against the installed Pg on my linux box.

I'm about to head for work. When I get back I'll build PostGIS locally
and see if the problem conveniently goes away.

--
Craig Ringer

Tech-related writing at http://soapyfrogs.blogspot.com/

#16

Andrea Peri 2007

aperi2007@gmail.com

over 15 years ago

In reply to: Craig Ringer (#15)

Re: Postgres 9.0 crash on win7

Hmm. That is suspiciously close to the location of some last-minute
changes in Postgres 9.0. I wonder whether Andrea is using a version of
PostGIS that was compiled against pre-9.0RC1 Postgres sources.

If so, I am to and it's the latest PostGIS binary from their website.

Hi,

Yes, I will use the latest postgis binary released from postgis team after the release of PG9.0 stable.

However seem the problem was really due to an pre-9.0RC1 postgres source.
( http://trac.osgeo.org/postgis/ticket/619#comment:3 )

I confirm that using this new version of "postgis-1.5.dll" the crash was disappeared.

I think the bug is resolved.

Thx for great support.

Andrea Peri.

Import Notes

Resolved by subject fallback

Postgres 9.0 crash on win7

Attachments: