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();