History

We'll try this again for the third 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 from an api say the backend has this habbit of crashing with signal
11 I'm trying to find some more logs now...

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