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 abnormally
	before or while processing 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 GCC 2.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',
   PRIMARY KEY (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 UPDATE CASCADE
                                                ON DELETE NO ACTION
						DEFERRABLE,
   PRIMARY KEY (product)
)
   INHERITS (job)
;

(gdb) bt full
#0  0x814786c in pfree (pointer=0x83054a8) at mcxt.c:451
	pointer = (void *) 0x83054a8
#1  0x814766f in MemoryContextDelete (context=0x83054a8) at mcxt.c:191
	context = 0x83054a8
#2  0x80c31b5 in FreeExprContext (econtext=0x8344bd8) at execUtils.c:218
	econtext = (ExprContext *) 0x8344bd8
#3  0x80c65e9 in ExecEndIndexScan (node=0x8338f00) at nodeIndexscan.c:468
	scanstate = (CommonScanState *) 0x8344998
	indexstate = (IndexScanState *) 0x8344a78
	runtimeKeyInfo = (int **) 0x8344b60
	scanKeys = (ScanKey *) 0x8344b18
	numScanKeys = (int *) 0x8344b00
	numIndices = 1
	i = 137644664
#4  0x80c1589 in ExecEndNode (node=0x8338f00, parent=0x8338f00)
    at execProcnode.c:501
	node = (Plan *) 0x8338f00
	subp = (List *) 0x0
#5  0x80ca121 in ExecEndSubPlan (node=0x833bb50) at nodeSubplan.c:452
	node = (SubPlan *) 0x833bb50
#6  0x80c151b in ExecEndNode (node=0x833bf50, parent=0x833bff0)
    at execProcnode.c:471
	node = (Plan *) 0x833bf50
	subp = (List *) 0x833c2d8
#7  0x80c41db in ExecEndAppend (node=0x833bff0) at nodeAppend.c:367
	node = (Append *) 0x833bff0
	appendstate = (AppendState *) 0x8305418
	nplans = 3
	appendplans = (List *) 0x833be68
	initialized = (bool *) 0x833c660 "\001\001\001"
	i = 2
#8  0x80c156b in ExecEndNode (node=0x833bff0, parent=0x833c078)
    at execProcnode.c:489
	node = (Plan *) 0x833bff0
	subp = (List *) 0x0
#9  0x80c8187 in ExecEndResult (node=0x833c078) at nodeResult.c:278
	node = (Result *) 0x833c078
	resstate = (ResultState *) 0x833c5f0
#10 0x80c155d in ExecEndNode (node=0x833c078, parent=0x833c078)
    at execProcnode.c:485
	node = (Plan *) 0x833c078
	subp = (List *) 0x0
#11 0x80c015b in EndPlan (plan=0x833c078, estate=0x833c308) at execMain.c:851
	plan = (Plan *) 0x833c078
	estate = (EState *) 0x833c308
	resultRelInfo = (ResultRelInfo *) 0x833c078
	i = 137609968
	l = (List *) 0x833c078
#12 0x80bf9b1 in ExecutorEnd (queryDesc=0x833c2f0, estate=0x833c308)
    at execMain.c:265
	queryDesc = (QueryDesc *) 0x8305418
	estate = (EState *) 0x833c308
#13 0x81070da in ProcessQuery (parsetree=0x832afc0, plan=0x833c078, 
    dest=Remote) at pquery.c:314
	parsetree = (Query *) 0x833c308
	plan = (Plan *) 0x833c2f0
	operation = 1
	tag = 0x81850e0 "SELECT"
	isRetrieveIntoPortal = 0 '\000'
	isRetrieveIntoRelation = 0 '\000'
	intoName = 0x83453c0 "\004"
	portal = 0x0
	oldContext = 0x0
	queryDesc = (QueryDesc *) 0x833c2f0
	state = (EState *) 0x833c308
	attinfo = 0x83453c0
#14 0x8105b01 in pg_exec_query_string (
    query_string=0x832a958 "select * from job AS j where not exists (select * from price where price.product = j.product);", dest=Remote, 
    parse_context=0x8304c30) at postgres.c:810
	plan = (Plan *) 0x833c078
	querytree = (Query *) 0x832afc0
	parsetree = (Node *) 0x832afc0
	isTransactionStmt = 0 '\000'
	querytree_list = (List *) 0x832afc0
	querytree_item = (List *) 0x832c848
	xact_started = 1 '\001'
	oldcontext = 0x833c078
	parsetree_list = (List *) 0x832afc0
	parsetree_item = (List *) 0x832af60
#15 0x8106b64 in PostgresMain (argc=6, argv=0xbfffef80, real_argc=7, 
    real_argv=0xbffff944, username=0x82fac21 "olly") at postgres.c:1883
	flag = 81
	DBName = 0x82fad88 "bray"
	secure = 0 '\000'
	errs = 0
	firstchar = 81
	parser_input = 0x832a940
	remote_host = 0x832a940 "X�2\b^"
	remote_port = 2071
	potential_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."             I Peter 5:7  



Tue, 27 Mar 2001 11:25:17 +0100
"Oliver Elphick" <olly@lfix.co.uk>
Backend crash (segfault) on query with inheritance hierarchy
"Oliver Elphick" <olly@lfix.co.uk> writes:
> Table `job' is inherited by `manufactured_job' and `purchased_job'.  This
> query works on either inherited table but not on the whole hierarchy:

I've committed a fix to CVS.

			regards, tom lane

Tue, 27 Mar 2001 13:03:32 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Backend crash (segfault) on query with inheritance hierarchy 