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

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