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