Skip site navigation (1) Skip section navigation (2)

BUG #5006: Backend crashed after select with subselect in where cluase

From: "Miroslav Trisc" <miro(dot)trisc(at)swissonline(dot)ch>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #5006: Backend crashed after select with subselect in where cluase
Date: 2009-08-24 15:00:25
Message-ID: 200908241500.n7OF0Ppk015847@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      5006
Logged by:          Miroslav Trisc
Email address:      miro(dot)trisc(at)swissonline(dot)ch
PostgreSQL version: 8.4.0
Operating system:   Windows Server 2003 R2 service pack 2
Description:        Backend crashed after select with subselect in where
cluase
Details: 

Hello 
My select : select * from wms_shp_delivery where id in ( 
	select shp_delivery_id from wms_shp_bins 
	where edi_id='5452'
	)
caused that Postgre server completely crash.
subselect get back just 12 rows.
Problem is that it is happen just by 2 concrete edi_id.
Select with another one is OK.
It works if i don't specify where clause(edi_id='5452')
After rewriting select to hard coded list it works.
After rewriting subselect into inner join select it works as well.
After dropping wms_shp_bins and making new same table with the same data ,it
works!
WMS_SHP_DELIVERY.id is type bigserial.
WMS_SHP_BINS.shp_delivery_id is type integer.
I did test on 2 standalone same servers with same result.
I 

postgre_error.log:
2009-08-24 16:42:34 CESTDEBUG:  00000: name: unnamed; blockState:      
STARTED; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children: 
2009-08-24 16:42:34 CESTORT:  ShowTransactionStateRec,
.\src\backend\access\transam\xact.c:4111
2009-08-24 16:42:34 CESTDEBUG:  00000: Parsen <unnamed>: select * from
wms_shp_delivery where id in ( 
		select shp_delivery_id from wms_shp_bins 
		where edi_id='5452'
		)
2009-08-24 16:42:34 CESTORT:  exec_parse_message,
.\src\backend\tcop\postgres.c:1117
2009-08-24 16:42:34 CESTANWEISUNG:  select * from wms_shp_delivery where id
in ( 
		select shp_delivery_id from wms_shp_bins 
		where edi_id='5452'
		)
2009-08-24 16:42:34 CESTDEBUG:  00000: StartTransactionCommand
2009-08-24 16:42:34 CESTORT:  start_xact_command,
.\src\backend\tcop\postgres.c:2337
2009-08-24 16:42:34 CESTANWEISUNG:  select * from wms_shp_delivery where id
in ( 
		select shp_delivery_id from wms_shp_bins 
		where edi_id='5452'
		)
2009-08-24 16:42:34 CESTDEBUG:  00000: StartTransaction
2009-08-24 16:42:34 CESTORT:  ShowTransactionState,
.\src\backend\access\transam\xact.c:4073
2009-08-24 16:42:34 CESTANWEISUNG:  select * from wms_shp_delivery where id
in ( 
		select shp_delivery_id from wms_shp_bins 
		where edi_id='5452'
		)
2009-08-24 16:42:34 CESTDEBUG:  00000: name: unnamed; blockState:      
DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children: 
2009-08-24 16:42:34 CESTORT:  ShowTransactionStateRec,
.\src\backend\access\transam\xact.c:4111
2009-08-24 16:42:34 CESTANWEISUNG:  select * from wms_shp_delivery where id
in ( 
		select shp_delivery_id from wms_shp_bins 
		where edi_id='5452'
		)
2009-08-24 16:42:34 CESTDEBUG:  00000: Binden <unnamed> an <unnamed>
2009-08-24 16:42:34 CESTORT:  exec_bind_message,
.\src\backend\tcop\postgres.c:1399
2009-08-24 16:42:34 CESTDEBUG:  00000: shmem_exit(-1): 6 callbacks to make
2009-08-24 16:42:34 CESTORT:  shmem_exit,
.\src\backend\storage\ipc\ipc.c:197
2009-08-24 16:42:34 CESTANWEISUNG:  select * from wms_shp_delivery where id
in ( 
		select shp_delivery_id from wms_shp_bins 
		where edi_id='5452'
		)
2009-08-24 16:42:34 CESTDEBUG:  00000: reaping dead processes
2009-08-24 16:42:34 CESTORT:  reaper,
.\src\backend\postmaster\postmaster.c:2184
2009-08-24 16:42:34 CESTDEBUG:  00000: Serverprozess (PID 5468) beendete mit
Status 128
2009-08-24 16:42:34 CESTORT:  LogChildExit,
.\src\backend\postmaster\postmaster.c:2653
2009-08-24 16:42:34 CESTLOG:  00000: Serverprozess (PID 5468) beendete mit
Status 128
2009-08-24 16:42:34 CESTORT:  LogChildExit,
.\src\backend\postmaster\postmaster.c:2653
2009-08-24 16:42:34 CESTLOG:  00000: aktive Serverprozesse werden
abgebrochen
2009-08-24 16:42:34 CESTORT:  HandleChildCrash,
.\src\backend\postmaster\postmaster.c:2500
2009-08-24 16:42:34 CESTDEBUG:  00000: sending SIGQUIT to process 692
2009-08-24 16:42:34 CESTORT:  HandleChildCrash,
.\src\backend\postmaster\postmaster.c:2543
2009-08-24 16:42:34 CESTDEBUG:  00000: sending SIGQUIT to process 5376
2009-08-24 16:42:34 CESTORT:  HandleChildCrash,
.\src\backend\postmaster\postmaster.c:2569
2009-08-24 16:42:34 CESTWARNUNG:  57P02: breche Verbindung ab wegen Absturz
eines anderen Serverprozesses
2009-08-24 16:42:34 CESTDETAIL:  Der Postmaster hat diesen Serverprozess
angewiesen, die aktuelle Transaktion zurückzurollen und die Sitzung zu
beenden, weil ein anderer Serverprozess abnormal beendet wurde und
möglicherweise das Shared Memory verfälscht hat.
2009-08-24 16:42:34 CESTTIPP:  In einem Moment sollten Sie wieder mit der
Datenbank verbinden und Ihren Befehl wiederholen können.
2009-08-24 16:42:34 CESTORT:  quickdie, .\src\backend\tcop\postgres.c:2495
2009-08-24 16:42:34 CESTDEBUG:  00000: shmem_exit(-1): 0 callbacks to make
2009-08-24 16:42:34 CESTORT:  shmem_exit,
.\src\backend\storage\ipc\ipc.c:197
2009-08-24 16:42:34 CESTDEBUG:  00000: proc_exit(-1): 0 callbacks to make
2009-08-24 16:42:34 CESTORT:  proc_exit_prepare,
.\src\backend\storage\ipc\ipc.c:169
2009-08-24 16:42:34 CESTDEBUG:  00000: sending SIGQUIT to process 4152
2009-08-24 16:42:34 CESTORT:  HandleChildCrash,
.\src\backend\postmaster\postmaster.c:2581
2009-08-24 16:42:34 CESTDEBUG:  00000: sending SIGQUIT to process 860
2009-08-24 16:42:34 CESTORT:  HandleChildCrash,
.\src\backend\postmaster\postmaster.c:2593
2009-08-24 16:42:34 CESTDEBUG:  00000: sending SIGQUIT to process 2188
2009-08-24 16:42:34 CESTORT:  HandleChildCrash,
.\src\backend\postmaster\postmaster.c:2623
2009-08-24 16:42:34 CESTDEBUG:  00000: shmem_exit(-1): 0 callbacks to make
2009-08-24 16:42:34 CESTORT:  shmem_exit,
.\src\backend\storage\ipc\ipc.c:197
2009-08-24 16:42:34 CESTDEBUG:  00000: proc_exit(-1): 0 callbacks to make
2009-08-24 16:42:34 CESTORT:  proc_exit_prepare,
.\src\backend\storage\ipc\ipc.c:169
2009-08-24 16:42:34 CESTDEBUG:  00000: shmem_exit(-1): 0 callbacks to make
2009-08-24 16:42:34 CESTORT:  shmem_exit,
.\src\backend\storage\ipc\ipc.c:197
2009-08-24 16:42:34 CESTDEBUG:  00000: proc_exit(-1): 0 callbacks to make
2009-08-24 16:42:34 CESTORT:  proc_exit_prepare,
.\src\backend\storage\ipc\ipc.c:169
2009-08-24 16:42:34 CESTDEBUG:  00000: shmem_exit(-1): 0 callbacks to make
2009-08-24 16:42:34 CESTORT:  shmem_exit,
.\src\backend\storage\ipc\ipc.c:197
2009-08-24 16:42:34 CESTDEBUG:  00000: proc_exit(-1): 0 callbacks to make
2009-08-24 16:42:34 CESTORT:  proc_exit_prepare,
.\src\backend\storage\ipc\ipc.c:169
2009-08-24 16:42:34 CESTDEBUG:  00000: reaping dead processes
2009-08-24 16:42:34 CESTORT:  reaper,
.\src\backend\postmaster\postmaster.c:2184
2009-08-24 16:42:34 CESTDEBUG:  00000: Serverprozess (PID 692) beendete mit
Status 2
2009-08-24 16:42:34 CESTORT:  LogChildExit,
.\src\backend\postmaster\postmaster.c:2653
2009-08-24 16:42:34 CESTDEBUG:  00000: reaping dead processes
2009-08-24 16:42:34 CESTORT:  reaper,
.\src\backend\postmaster\postmaster.c:2184
2009-08-24 16:42:34 CESTDEBUG:  00000: reaping dead processes
2009-08-24 16:42:34 CESTORT:  reaper,
.\src\backend\postmaster\postmaster.c:2184
2009-08-24 16:42:34 CESTDEBUG:  00000: reaping dead processes
2009-08-24 16:42:34 CESTORT:  reaper,
.\src\backend\postmaster\postmaster.c:2184
2009-08-24 16:42:34 CESTDEBUG:  00000: reaping dead processes
2009-08-24 16:42:34 CESTORT:  reaper,
.\src\backend\postmaster\postmaster.c:2184
2009-08-24 16:42:34 CESTLOG:  00000: alle Serverprozesse beendet;
initialisiere neu
2009-08-24 16:42:34 CESTORT:  PostmasterStateMachine,
.\src\backend\postmaster\postmaster.c:2858
2009-08-24 16:42:34 CESTDEBUG:  00000: shmem_exit(1): 2 callbacks to make
2009-08-24 16:42:34 CESTORT:  shmem_exit,
.\src\backend\storage\ipc\ipc.c:197
2009-08-24 16:42:34 CESTDEBUG:  00000: invoking
IpcMemoryCreate(size=739778560)
2009-08-24 16:42:34 CESTORT:  CreateSharedMemoryAndSemaphores,
.\src\backend\storage\ipc\ipci.c:130
2009-08-24 16:42:44 CESTFATAL:  XX000: bereits bestehender
Shared-Memory-Block wird noch benutzt
2009-08-24 16:42:44 CESTTIPP:  Prüfen Sie, ob irgendwelche alten
Serverprozesse noch laufen und beenden Sie diese.
2009-08-24 16:42:44 CESTORT:  PGSharedMemoryCreate,
.\src\backend\port\win32_shmem.c:185
2009-08-24 16:42:44 CESTDEBUG:  00000: shmem_exit(1): 0 callbacks to make
2009-08-24 16:42:44 CESTORT:  shmem_exit,
.\src\backend\storage\ipc\ipc.c:197
2009-08-24 16:42:44 CESTDEBUG:  00000: proc_exit(1): 1 callbacks to make
2009-08-24 16:42:44 CESTORT:  proc_exit_prepare,
.\src\backend\storage\ipc\ipc.c:169
2009-08-24 16:42:44 CESTDEBUG:  00000: exit(1)
2009-08-24 16:42:44 CESTORT:  proc_exit,
.\src\backend\storage\ipc\ipc.c:134
2009-08-24 16:42:44 CESTDEBUG:  00000: shmem_exit(-1): 0 callbacks to make
2009-08-24 16:42:44 CESTORT:  shmem_exit,
.\src\backend\storage\ipc\ipc.c:197
2009-08-24 16:42:44 CESTDEBUG:  00000: proc_exit(-1): 0 callbacks to make
2009-08-24 16:42:44 CESTORT:  proc_exit_prepare,
.\src\backend\storage\ipc\ipc.c:169
2009-08-24 16:42:45 CESTDEBUG:  00000: Logger fährt herunter
2009-08-24 16:42:45 CESTORT:  SysLoggerMain,
.\src\backend\postmaster\syslogger.c:434
2009-08-24 16:42:45 CESTDEBUG:  00000: shmem_exit(0): 0 callbacks to make
2009-08-24 16:42:45 CESTORT:  shmem_exit,
.\src\backend\storage\ipc\ipc.c:197
2009-08-24 16:42:45 CESTDEBUG:  00000: proc_exit(0): 0 callbacks to make
2009-08-24 16:42:45 CESTORT:  proc_exit_prepare,
.\src\backend\storage\ipc\ipc.c:169
2009-08-24 16:42:45 CESTDEBUG:  00000: exit(0)
2009-08-24 16:42:45 CESTORT:  proc_exit, .\src\backend\storage\ipc\ipc.c:134

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2009-08-24 15:12:18
Subject: Re: BUG #5006: Backend crashed after select with subselect in where cluase
Previous:From: Brian CeccarelliDate: 2009-08-24 12:24:20
Subject: Re: BUG #4999: select 'a' < 'A' is true, but should be false . . .

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group