Thread: A generic trigger?
Hi, Am looking for a way to minimize the amount of fuctions that support triggers. E.g., there's "company" and "company_backup" tables. Update trigger on the "company" table will put a record in the "company_backup" table whenever "company" record is updated. The problem is that there's quite a few other tables for which similar backup logic has to be done (e.g. "custormer" and "customer_backup", etc). The backup logic is the same, only structure of the tables changes. Is there a way to write a generic trigger/function that would deal with backup regardless of the table structure? Thanks in advance. __________________________________ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com
On Sunday 14 September 2003 02:13, ow wrote:
> Hi,
>
> Am looking for a way to minimize the amount of fuctions that support
> triggers. E.g., there's "company" and "company_backup" tables. Update
> trigger on the "company" table will put a record in the "company_backup"
> table whenever "company" record is updated.
>
> The problem is that there's quite a few other tables for which similar
> backup logic has to be done (e.g. "custormer" and "customer_backup", etc).
> The backup logic is the same, only structure of the tables changes.
>
> Is there a way to write a generic trigger/function that would deal with
> backup regardless of the table structure?
>
> Thanks in advance.
>
>
>
Yes it is possible and I've done it. The reason I'm not using it is because I
wrote it in Pl/Python and if you attach the same trigger to more than one
table in the same transaction pg/python (actually the entire server crashes
but thats not the point) crashes. Well it did when I last tested it in early
versions. I'm still thinking of getting around to rewriting it in a language
without this bug, since nobody sounds like they are going to fix it. C might
be best!This version inserts all the history in the same table. But since its broke
anyway changing it to insert into different tables should not be too
difficult. There are some scripting languages where somthing don't work hense why I
chose pl/python.... The trigger/function is below although it should be in
the archives somwhere as well. Full problem with it can be seen of Bugs....
Peter Childs
-- CREATE TABLE history ( tab text field text
action text before text
after text occured timestamp without time zone key
text who text );
DROP INDEX history_tab;
DROP INDEX history_tab_field;
DROP INDEX history_tab_key;
DROP INDEX history_tab_who;
DROP INDEX history_who;
CREATE INDEX history_tab on history(tab);
CREATE INDEX history_tab_field on history(tab,field);
CREATE INDEX history_tab_key on history(tab,key);
CREATE INDEX history_tab_who on history(tab,who);
CREATE INDEX history_who on history(who);
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]
ifnew == 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
account_history
FOR EACH ROW EXECUTE PROCEDURE history_update();
--- Peter Childs <Blue.Dragon@blueyonder.co.uk> wrote: > Yes it is possible and I've done it. The reason I'm not using it is because > I > wrote it in Pl/Python and if you attach the same trigger to more than one > table in the same transaction pg/python (actually the entire server crashes > but thats not the point) crashes. Well it did when I last tested it in early > versions. I'm still thinking of getting around to rewriting it in a language > without this bug, since nobody sounds like they are going to fix it. C might > be best! [snip] Hi, In my case, "company" and "company_backup" tables have the *same* structure, so I was hoping for a simpler solution using just plpgsql. Any ideas? Thanks __________________________________ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com
Peter Childs <Blue.Dragon@blueyonder.co.uk> writes:
> Yes it is possible and I've done it. The reason I'm not using it is because I
> wrote it in Pl/Python and if you attach the same trigger to more than one
> table in the same transaction pg/python (actually the entire server crashes
> but thats not the point) crashes. Well it did when I last tested it in early
> versions.
I've been expecting someone to submit a fix for this, but nobody did
:-(. So I went ahead and repaired it in CVS tip. The patch is attached
if you want to try patching your local copy (it looks like it will apply
to 7.3 branch with some fuzz, but I have not actually tested it there).
regards, tom lane
*** src/pl/plpython/plpython.c.orig Mon Aug 4 14:40:50 2003
--- src/pl/plpython/plpython.c Sun Sep 14 13:07:02 2003
***************
*** 224,236 **** static PyObject *PLy_procedure_call(PLyProcedure *, char *, PyObject *);
! /* returns a cached PLyProcedure, or creates, stores and returns
! * a new PLyProcedure.
! */
! static PLyProcedure *PLy_procedure_get(FunctionCallInfo fcinfo, bool); static PLyProcedure
*PLy_procedure_create(FunctionCallInfofcinfo,
! bool is_trigger, HeapTuple procTup, char *key); static void
PLy_procedure_compile(PLyProcedure*, const char *);
--- 224,234 ---- static PyObject *PLy_procedure_call(PLyProcedure *, char *, PyObject *);
! static PLyProcedure *PLy_procedure_get(FunctionCallInfo fcinfo,
! Oid tgreloid); static PLyProcedure *PLy_procedure_create(FunctionCallInfo
fcinfo,
! Oid tgreloid, HeapTuple procTup, char *key); static void
PLy_procedure_compile(PLyProcedure*, const char *);
***************
*** 326,332 **** { DECLARE_EXC(); Datum retval;
- volatile bool is_trigger; PLyProcedure *volatile proc = NULL; enter();
--- 324,329 ----
***************
*** 337,343 **** elog(ERROR, "could not connect to SPI manager"); CALL_LEVEL_INC();
- is_trigger = CALLED_AS_TRIGGER(fcinfo); SAVE_EXC(); if (TRAP_EXC())
--- 334,339 ----
***************
*** 364,379 **** * PLy_restart_in_progress); */
! proc = PLy_procedure_get(fcinfo, is_trigger);
!
! if (is_trigger) {
! HeapTuple trv = PLy_trigger_handler(fcinfo, proc); retval = PointerGetDatum(trv); } else
retval = PLy_function_handler(fcinfo, proc); CALL_LEVEL_DEC(); RESTORE_EXC();
--- 360,380 ---- * PLy_restart_in_progress); */
! if (CALLED_AS_TRIGGER(fcinfo)) {
! TriggerData *tdata = (TriggerData *) fcinfo->context;
! HeapTuple trv;
+ proc = PLy_procedure_get(fcinfo,
+ RelationGetRelid(tdata->tg_relation));
+ trv = PLy_trigger_handler(fcinfo, proc); retval = PointerGetDatum(trv); } else
+ {
+ proc = PLy_procedure_get(fcinfo, InvalidOid); retval = PLy_function_handler(fcinfo, proc);
+ } CALL_LEVEL_DEC(); RESTORE_EXC();
***************
*** 962,971 **** }
! /* PLyProcedure functions */ static PLyProcedure *
! PLy_procedure_get(FunctionCallInfo fcinfo, bool is_trigger) { Oid fn_oid; HeapTuple procTup;
--- 963,979 ---- }
! /*
! * PLyProcedure functions
! */
!
! /* PLy_procedure_get: returns a cached PLyProcedure, or creates, stores and
! * returns a new PLyProcedure. fcinfo is the call info, tgreloid is the
! * relation OID when calling a trigger, or InvalidOid (zero) for ordinary
! * function calls. */ static PLyProcedure *
! PLy_procedure_get(FunctionCallInfo fcinfo, Oid tgreloid) { Oid fn_oid; HeapTuple procTup;
***************
*** 983,991 **** if (!HeapTupleIsValid(procTup)) elog(ERROR, "cache lookup failed for function %u",
fn_oid);
! rv = snprintf(key, sizeof(key), "%u%s",
! fn_oid,
! is_trigger ? "_trigger" : ""); if ((rv >= sizeof(key)) || (rv < 0)) elog(ERROR, "key
toolong");
--- 991,997 ---- if (!HeapTupleIsValid(procTup)) elog(ERROR, "cache lookup failed for function %u",
fn_oid);
! rv = snprintf(key, sizeof(key), "%u_%u", fn_oid, tgreloid); if ((rv >= sizeof(key)) || (rv < 0))
elog(ERROR,"key too long");
***************
*** 1012,1018 **** } if (proc == NULL)
! proc = PLy_procedure_create(fcinfo, is_trigger, procTup, key); ReleaseSysCache(procTup);
--- 1018,1024 ---- } if (proc == NULL)
! proc = PLy_procedure_create(fcinfo, tgreloid, procTup, key); ReleaseSysCache(procTup);
***************
*** 1020,1026 **** } static PLyProcedure *
! PLy_procedure_create(FunctionCallInfo fcinfo, bool is_trigger, HeapTuple procTup, char *key) {
char procName[NAMEDATALEN + 256];
--- 1026,1032 ---- } static PLyProcedure *
! PLy_procedure_create(FunctionCallInfo fcinfo, Oid tgreloid, HeapTuple procTup, char *key) {
char procName[NAMEDATALEN + 256];
***************
*** 1037,1047 **** procStruct = (Form_pg_proc) GETSTRUCT(procTup);
! rv = snprintf(procName, sizeof(procName),
! "__plpython_procedure_%s_%u%s",
! NameStr(procStruct->proname),
! fcinfo->flinfo->fn_oid,
! is_trigger ? "_trigger" : ""); if ((rv >= sizeof(procName)) || (rv < 0)) elog(ERROR,
"procedurename would overrun buffer");
--- 1043,1059 ---- procStruct = (Form_pg_proc) GETSTRUCT(procTup);
! if (OidIsValid(tgreloid))
! rv = snprintf(procName, sizeof(procName),
! "__plpython_procedure_%s_%u_trigger_%u",
! NameStr(procStruct->proname),
! fcinfo->flinfo->fn_oid,
! tgreloid);
! else
! rv = snprintf(procName, sizeof(procName),
! "__plpython_procedure_%s_%u",
! NameStr(procStruct->proname),
! fcinfo->flinfo->fn_oid); if ((rv >= sizeof(procName)) || (rv < 0)) elog(ERROR,
"procedurename would overrun buffer");
***************
*** 1073,1079 **** * get information required for output conversion of the return value, * but only if this
isn'ta trigger. */
! if (!is_trigger) { HeapTuple rvTypeTup; Form_pg_type rvTypeStruct;
--- 1085,1091 ---- * get information required for output conversion of the return value, * but only if this
isn'ta trigger. */
! if (!CALLED_AS_TRIGGER(fcinfo)) { HeapTuple rvTypeTup; Form_pg_type rvTypeStruct;