Re: what fired a trigger - Mailing list pgsql-general

On Tue, 5 Sep 2006 22:53:14 +0200
Ivan Sergio Borgonovo <mail@webthatworks.it> wrote:

> Hi,
>
> The most general problem that may be a design problem (and I'm not
> asking to do my homework even if well, you may still help <g>) is
> I've a "temporary relation" and a permanent relation. A typical
> situation is the one of session in a web app and temporary basket.
> Once the user log in, the temporary basket becomes "permanent". But
> I'd like automatic garbage collection for all tables simply related
> to sessions. When a session get deleted all row that are *just*
> related to the session, but not to the user, should be deleted.
> Anyway I still need normal "User related" delete.

OK let's see if I can make the description of the problem more general.

on delete cascade is comfortable because every time someone will add a new Session related table he just has to
rememberto add it making the garbage collection a "local" problem. 
Adding a rule on delete of session table will require modification to the rule every time a new table is added; unless
Ican think of a very smart way that will look into system tables[*] etc... but I suspect this will be terribly
inefficient.

A trigger on delete doesn't have any information about how it was called, it seems to me it can just know on which
tableit was fired and which field was deleted from the TG_NAME eventually, but considering that more than one trigger
maybe called for the same row, and the first one is determined by alphabetical order, it doesn't seem too useful. 
Arguments can't be passed because triggers are fired "indirectly". That means that a trigger may cause a delete that
willfire another trigger. The delete won't pass the argument of the previous trigger to the next. 

A possible workaround could be to use an update trigger to delete sessions/users.
A trigger may be linked to the update of the idUser, stored in the session table.
Updates that set to null a idUser are "alias" for delete everything unconditionally.
A delete... may be a conditional delete.
On every idSession/idUser table I'll have 2 triggers.

One on delete idSession. This one should be similar to the previous one I wrote.

One on update idUser should be something like

if NEW.idUser is null then
begin
--    delete from TG_RELNAME where idSession=OLD.idSession;
    delete from TG_RELNAME where idUser=OLD.idUser; --??
end;
return NEW;

I'm still considering if this approach is feasible and if it has any drawback.
Surely a bit more overhead to unset idUser when anyway the row is going to be deleted and the risk I'll have to
"delete"by idSession since the idUser get lost (I've to play with after/before). 
It doesn't seem too elegant and my main concern is it doesn't look flexible/general enough.


/****************
Anyway I'm still interested to know if there is any system to pass parameters between triggers or at least to know what
causeda trigger (a direct operation or a cascade operation). 
If anyone can enlighten me I'd be grateful.
*****************/



[*] eg. find in the system table all the tables that have an idSession column, use a cursor, a dynamically generated
statementand delete unconditionally on tables that don't have idUser, and conditionally (idUser is null) on table that
haveit. 
If there are tables where idSession is not unique this could be more efficient than triggers. But this won't be the
mostcommon case and dynamically generated statements and cursors will be expensive. 



--
Ivan Sergio Borgonovo
http://www.webthatworks.it