Bug #893: Trigger causes database to crash - Mailing list pgsql-bugs

Peter Childs (Blue.Dragon@blueyonder.co.uk) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
Trigger causes database to crash

Long Description
I have a trigger that when in use on a table causes the postgresql database backend to crash with a segmentation fault.
The database works fine without the trigger and only crashes when it is switched on.
This happerns durring an update but if the query is run by itself the update works (with the trigger on)
I suspect it has somthing to do with recent queries run by that particular client.
The trigger is applied for insert, delete and update accross multiple tables.
The bug is repeatable so if anyone wants more information please request.

Many thanks

Peter Childs

The Trigger.....

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_attributea 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';


Sample Code


No file was uploaded with this report