Backend crash (segfault) on query with inheritance hierarchy - Mailing list pgsql-hackers

Table `job' is inherited by `manufactured_job' and `purchased_job'.  This
query works on either inherited table but not on the whole hierarchy:

bray=# select * from manufactured_job AS j where not exists (select * from 
price where price.product = j.product);product | qty | carriage | qc_sheet | barcodes | instructions | description 
---------+-----+----------+----------+----------+--------------+-------------
(0 rows)

bray=# select * from purchased_job AS j where not exists (select * from price 
where price.product = j.product);product | qty | carriage | qc_sheet | supplier | specification | del_point | 
import_licence | import_duty | terms | deliv_clear
---------+-----+----------+----------+----------+---------------+-----------+--
--------------+-------------+-------+-------------
(0 rows)

bray=# select * from job AS j where not exists (select * from price where 
price.product = j.product);
pqReadData() -- backend closed the channel unexpectedly.This probably means the backend terminated abnormallybefore or
whileprocessing the request.
 
The connection to the server was lost. Attempting reset: Failed.
!# 

The log shows:
QUERY STATISTICS
! system usage stats:
!    0.160325 elapsed 0.100000 user 0.010000 system sec
!    [0.380000 user 0.040000 sys total]
!    0/0 [0/0] filesystem blocks in/out
!    71/2 [1109/429] page faults/reclaims, 0 [0] swaps
!    0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
!    0/0 [0/0] voluntary/involuntary context switches
! postgres usage stats:
!    Shared blocks:        150 read,          0 written, buffer hit rate = 97.53%
!    Local  blocks:          0 read,          0 written, buffer hit rate = 0.00%
!    Direct blocks:          0 read,          0 written
Server process (pid 15994) exited with status 139 at Tue Mar 27 10:47:49 2001

(segmentation fault:  128 + 11  the coredump backtrace is below).

bray=# select version();                            version                              
------------------------------------------------------------------PostgreSQL 7.1beta5 on i686-pc-linux-gnu, compiled by
GCC2.95.3
 
(1 row)

bray=# select count(*) from job;count 
------- 4766
(1 row)

bray=# select count(*) from only job;count 
-------    0
(1 row)

bray=# select count(*) from manufactured_job;count 
------- 3279
(1 row)

bray=# select count(*) from purchased_job;count 
------- 1487
(1 row)



CREATE TABLE job
(  product      VARCHAR(10)     CONSTRAINT product                                REFERENCES stock (product)
                          ON UPDATE CASCADE                                       ON DELETE NO ACTION,  qty
INTEGER        NOT NULL                               CHECK (qty > 0),  carriage     NUMERIC(12,2)   NOT NULL
                   DEFAULT 0,  qc_sheet     BOOLEAN         NOT NULL                               DEFAULT 'f',
PRIMARYKEY (product)
 
)
;


CREATE TABLE manufactured_job
(  barcodes             BOOLEAN         NOT NULL                                       DEFAULT 'f',  instructions
 TEXT,  description          TEXT,  PRIMARY KEY (product)
 
)  INHERITS (job)
;


CREATE TABLE purchased_job
(  supplier             VARCHAR(10)        NOT NULL             CONSTRAINT supplier
 REFERENCES supplier (id)                                               ON UPDATE CASCADE
               ON DELETE NO ACTION,  specification        TEXT,  del_point            CHAR(3)         NOT NULL
 CONSTRAINT del_point                                       REFERENCES location (id)
          ON UPDATE CASCADE                                               ON DELETE NO ACTION,  import_licence
BOOLEAN        DEFAULT 'f',  import_duty          NUMERIC(12,2),  terms                CHAR(3),  deliv_clear
NUMERIC(12,2),
  CONSTRAINT product_supplier     FOREIGN KEY (product, supplier)               REFERENCES product_supplier (product,
supplier)                                              MATCH FULL                                               ON
UPDATECASCADE                                               ON DELETE NO ACTION                    DEFERRABLE,  PRIMARY
KEY(product)
 
)  INHERITS (job)
;

(gdb) bt full
#0  0x814786c in pfree (pointer=0x83054a8) at mcxt.c:451pointer = (void *) 0x83054a8
#1  0x814766f in MemoryContextDelete (context=0x83054a8) at mcxt.c:191context = 0x83054a8
#2  0x80c31b5 in FreeExprContext (econtext=0x8344bd8) at execUtils.c:218econtext = (ExprContext *) 0x8344bd8
#3  0x80c65e9 in ExecEndIndexScan (node=0x8338f00) at nodeIndexscan.c:468scanstate = (CommonScanState *)
0x8344998indexstate= (IndexScanState *) 0x8344a78runtimeKeyInfo = (int **) 0x8344b60scanKeys = (ScanKey *)
0x8344b18numScanKeys= (int *) 0x8344b00numIndices = 1i = 137644664
 
#4  0x80c1589 in ExecEndNode (node=0x8338f00, parent=0x8338f00)   at execProcnode.c:501node = (Plan *) 0x8338f00subp =
(List*) 0x0
 
#5  0x80ca121 in ExecEndSubPlan (node=0x833bb50) at nodeSubplan.c:452node = (SubPlan *) 0x833bb50
#6  0x80c151b in ExecEndNode (node=0x833bf50, parent=0x833bff0)   at execProcnode.c:471node = (Plan *) 0x833bf50subp =
(List*) 0x833c2d8
 
#7  0x80c41db in ExecEndAppend (node=0x833bff0) at nodeAppend.c:367node = (Append *) 0x833bff0appendstate =
(AppendState*) 0x8305418nplans = 3appendplans = (List *) 0x833be68initialized = (bool *) 0x833c660 "\001\001\001"i = 2
 
#8  0x80c156b in ExecEndNode (node=0x833bff0, parent=0x833c078)   at execProcnode.c:489node = (Plan *) 0x833bff0subp =
(List*) 0x0
 
#9  0x80c8187 in ExecEndResult (node=0x833c078) at nodeResult.c:278node = (Result *) 0x833c078resstate = (ResultState
*)0x833c5f0
 
#10 0x80c155d in ExecEndNode (node=0x833c078, parent=0x833c078)   at execProcnode.c:485node = (Plan *) 0x833c078subp =
(List*) 0x0
 
#11 0x80c015b in EndPlan (plan=0x833c078, estate=0x833c308) at execMain.c:851plan = (Plan *) 0x833c078estate = (EState
*)0x833c308resultRelInfo = (ResultRelInfo *) 0x833c078i = 137609968l = (List *) 0x833c078
 
#12 0x80bf9b1 in ExecutorEnd (queryDesc=0x833c2f0, estate=0x833c308)   at execMain.c:265queryDesc = (QueryDesc *)
0x8305418estate= (EState *) 0x833c308
 
#13 0x81070da in ProcessQuery (parsetree=0x832afc0, plan=0x833c078,    dest=Remote) at pquery.c:314parsetree = (Query
*)0x833c308plan = (Plan *) 0x833c2f0operation = 1tag = 0x81850e0 "SELECT"isRetrieveIntoPortal = 0
'\000'isRetrieveIntoRelation= 0 '\000'intoName = 0x83453c0 "\004"portal = 0x0oldContext = 0x0queryDesc = (QueryDesc *)
0x833c2f0state= (EState *) 0x833c308attinfo = 0x83453c0
 
#14 0x8105b01 in pg_exec_query_string (   query_string=0x832a958 "select * from job AS j where not exists (select *
fromprice where price.product = j.product);", dest=Remote,    parse_context=0x8304c30) at postgres.c:810plan = (Plan *)
0x833c078querytree= (Query *) 0x832afc0parsetree = (Node *) 0x832afc0isTransactionStmt = 0 '\000'querytree_list = (List
*)0x832afc0querytree_item = (List *) 0x832c848xact_started = 1 '\001'oldcontext = 0x833c078parsetree_list = (List *)
0x832afc0parsetree_item= (List *) 0x832af60
 
#15 0x8106b64 in PostgresMain (argc=6, argv=0xbfffef80, real_argc=7,    real_argv=0xbffff944, username=0x82fac21
"olly")at postgres.c:1883flag = 81DBName = 0x82fad88 "bray"secure = 0 '\000'errs = 0firstchar = 81parser_input =
0x832a940remote_host= 0x832a940 "X�2\b^"remote_port = 2071potential_DataDir = 0x0
 
...

--==_Exmh_-19388723140
Content-Type: text/plain; charset=iso-8859-1

Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47  6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
========================================   "Cast all your anxiety on him, because he cares for       you."
IPeter 5:7