History

We'll try this again for the forth time!

    I need to record history for a table so I have come up with a
trigger (in plpython) to automatically insert records when anything
happerns into a history table. (could also be used for replication etc...)
The problem is that the table is likly to grow very quickly once I put the
trigger on a few tables. Should I use inherrtance and have a different
table for each table I want history for or one table for the lot?
    Currently the table looks like this.... (my key may not be an int)

              Table "public.history"
 Column  |            Type             | Modifiers
---------+-----------------------------+-----------
 tab     | text                        |
 field   | text                        |
 action  | text                        |
 before  | text                        |
 after   | text                        |
 occured | timestamp without time zone |
 key     | text                        |
 who     | text                        |
Indexes: history_tab btree (tab),
         history_tab_field btree (tab, field),
         history_tab_key btree (tab, "key"),
         history_who btree (who)

and for anyone who wants to see the trigger....
I found I could not do this at all in plpgsql.
On the one off query this works fine. But if you run this in a group of
queris quickly the backend crashes with signal
11 (This is with 7.3rc3 or 7.3.1 (I wanted to try and see if this was a fixed
bug))

Peter Childs

PS The Trigger..... for anyone intrested (will work on any table)


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 not(dont):
      plpy.execute(plan,[relname,key,TD["event"],old,new,pkey])
' LANGUAGE 'plpython';


DROP TRIGGER test_history_update ON test;
CREATE TRIGGER test_history_update AFTER INSERT OR UPDATE OR DELETE ON test
 FOR EACH ROW EXECUTE PROCEDURE history_update();