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 [EMAIL PROTECTED]
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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
