I have reported this bug before. It happerns of 7.3, 7.3.1 and 7.3.2.
Having done some further research on the bug.. I have discovered that the
following tranaction (which can be run from a script) with crash the back
end.
BEGIN;
SET CONSTRAINTS ALL DEFERRED;
UPDATE location set phoneid = NULL, streettype = 1 where id = 3639;
insert into customer
(rating,addressid,switches,bookingmess,dispmess,salesrep,status,company,invoiceformat,invoicecode,paymentmode,terms,servicecharge,bull_desc,waittime,creditlimit,transactionlimit,alias,keep,delme,direct_debit,tariff,paymethod,id,billingaddress,acctype,accnum)
values ('1',3639,FALSE,'','','',0,'PETER
CHILDS',0,1,0,30,15,'',5,0,0,'',FALSE,FALSE,FALSE,1,1,4133,3639,0,992);
COMMIT;
Output from script is
BEGIN
SET CONSTRAINTS
UPDATE 1
psql:crash:4: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
psql:crash:4: connection to server was lost
This only happerns when you have the following trigger (at bottom) on the
database. The trigger is attached to pratically every table. If anyone has
any more questions PLEASE ASK.
This is getting quite fustrating and I would like a answer soon!
Peter Childs
CREATE OR REPLACE FUNCTION history_update() RETURNS TRIGGER AS '
if TD["event"] == "INSERT":
lookup = "new"
elif TD["event"] == "DELETE":
lookup = "old"
else:
lookup = "new"
p = plpy.execute(" SELECT CASE i.indproc WHEN (''-''::pg_catalog.regproc)
THEN a.attname ELSE SUBSTR(pg_catalog.pg_get_indexdef(attrelid),
POSITION(''('' in pg_catalog.pg_get_indexdef(attrelid))) END as pkey,
a.atttypid::int, c2.relname FROM pg_catalog.pg_class c,
pg_catalog.pg_class c2, pg_catalog.pg_index i, pg_catalog.pg_attribute a
WHERE c.oid = " + TD["relid"] + " AND c.oid = i.indrelid AND i.indexrelid
= c2.oid and a.attrelid = i.indexrelid and NOT a.attisdropped and
i.indisprimary ORDER BY i.indisprimary DESC, i.indisunique DESC,
c2.relname;")
if len(p) > 0:
pkey = TD[lookup][p[0]["pkey"]]
ppkey = p[0]["pkey"]
else:
pkey = ""
ppkey = ""
rel = plpy.execute("select relname from pg_class where oid=" + TD["relid"]
+ ";")
relname = rel[0]["relname"]
plan = plpy.prepare("INSERT INTO history
(tab,field,action,before,after,occured,who,key) values
($1,$2,$3,$4,$5,now(),user,$6);",["text","text","text","text","text","text"])
if TD["event"] == "INSERT":
old = ""
new = pkey
plpy.execute(plan,[relname,ppkey,TD["event"],old,new,pkey])
else:
for key in TD[lookup].keys():
dont = 0
if TD["event"] == "INSERT":
old = ""
new = TD["new"][key]
if new == None:
dont = 1
elif TD["event"] == "UPDATE":
old = TD["old"][key]
new = TD["new"][key]
else:
old = TD["old"][key]
new = ""
if old == None:
old = "Null"
if new == None:
new = "Null"
if new == old:
dont = 1
if not(dont):
plpy.execute(plan,[relname,key,TD["event"],old,new,pkey])
' LANGUAGE 'plpython';
CREATE TRIGGER history_update AFTER INSERT OR UPDATE OR DELETE ON
customer
FOR EACH ROW EXECUTE PROCEDURE history_update();
