Обсуждение: Postgres 9.0 crash on win7
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. --=20 ----------------- Andrea Peri . . . . . . . . . qwerty =E0=E8=EC=F2=F9 -----------------
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/
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: Table "public.linee_elementari" Column | Type | Modifiers | Storage | Description -------------------+---------------------------+---------------------------= -------------------------------------+----------+------------- codlinea | character varying(35) | not null | extended | codctr | character varying(35) | | extended | codvisibilita | character varying(35) | | extended | codbreakline | character varying(35) | | extended | codclasse | character varying(35) | | extended | codlatovestizione | character varying(35) | | extended | codoriginelinea | character varying(35) | | extended | codmodifica | character varying(35) | | extended | poslist | character varying(200000) | | extended | lung_poslist | integer | | plain | num_vertex | integer | | plain | idedge | character varying(35) | | extended | srsname | character varying(35) | | extended | dimension | character varying(1) | | extended | coord_n1 | character varying(50) | | extended | coord_n2 | character varying(50) | | extended | geom | geometry | | main | oid | integer | not null default nextval('linee_elementari_oid_seq'::regclass) | plain | Indexes: "linee_elementari_pkey" PRIMARY KEY, btree (codlinea) Check constraints: "enforce_dims_geom" CHECK (st_ndims(geom) =3D 2) "enforce_geotype_geom" CHECK (geometrytype(geom) =3D 'LINESTRING'::text= OR geom IS NULL) "enforce_srid_geom" CHECK (st_srid(geom) =3D 3003) Has OIDs: no >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_Postgre= SQL_backend_on_Windows<http://wiki.postgresql.org/wiki/Getting_a_stack_trac= e_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 backtra= ce > 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_Postgr= eSQL_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/ > --=20 ----------------- Andrea Peri . . . . . . . . . qwerty =E0=E8=EC=F2=F9 -----------------
>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 # 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 The second set is when PG9 crash during execution of script sql and windbg intercept the crash -- the second 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 # 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.
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.
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/
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
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
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/
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/
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: > 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 >
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.
Вложения
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) Line877 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/
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
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/
>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.