Home > mailing lists
BUG #5006: Backend crashed after select with subselect in where cluase - Mailing list pgsql-bugs

From	Miroslav Trisc
Subject	BUG #5006: Backend crashed after select with subselect in where cluase
Date	August 24, 2009 15:00:29
Msg-id	200908241500.n7OF0Ppk015847@wwwmaster.postgresql.org Whole thread Raw
Responses	Re: BUG #5006: Backend crashed after select with subselect in where cluase (Tom Lane <tgl@sss.pgh.pa.us>)
List	pgsql-bugs
Tree view
The following bug has been logged online:

Bug reference:      5006
Logged by:          Miroslav Trisc
Email address:      miro.trisc@swissonline.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
pgsql-bugs by date:
From: "Brian Ceccarelli"
Date: 24 August 2009, 14:11:33
Subject: Re: BUG #4999: select 'a' < 'A' is true, but should be false . . .
From: Tom Lane
Date: 24 August 2009, 15:12:31
Subject: Re: BUG #5006: Backend crashed after select with subselect in where cluase
BUG #5006: Backend crashed after select with subselect in where cluase - Mailing list pgsql-bugs

Previous

Next
