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:

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) = 2)
"enforce_geotype_geom" CHECK (geometrytype(geom) = 'LINESTRING'::text OR
geom IS NULL)
"enforce_srid_geom" CHECK (st_srid(geom) = 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_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(at)postnewspapers(dot)com(dot)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 àèìòù
-----------------