Re: [ic] Sessions in PostgreSQL

[Jon Jensen <suppressed>]

On Tue, 12 Feb 2008, Doug Whiteley wrote:

> I was looking at moving sessions into a postgresql database, and 
> everything worked fine apart from the last_accessed column not being 
> updated automatically. To get that working, you need to add a trigger, 
> something like:
>
> create trigger trigger_session_modification AFTER UPDATE ON session_data 
> FOR EACH ROW EXECUTE PROCEDURE record_session_modification_trigger();
>
> And just have the procedure do something along the lines of UPDATE 
> session_data SET last_accessed=now() WHERE code=NEW.code;

Doug,

The function we typically use in this situation is:

CREATE OR REPLACE FUNCTION update_last_modified()
RETURNS TRIGGER AS '
BEGIN
    /*
        force last_modified timestamp to be accurately updated,
        using wallclock time, not start of transaction as given
        by CURRENT_TIMESTAMP, now(), etc.
    */
    NEW.last_modified := timeofday()::timestamp;
    RETURN NEW;
END;
' LANGUAGE 'plpgsql';

That has a couple of advantages:

(1) It modifies the row before it's ever written to disk, instead of doing 
a separate UPDATE, and so is more efficient -- almost no impact.

(2) It uses the wallclock time instead of the start of transaction time, 
which may not matter much for sessions, but can be nice when making 
numerous updates in longer-running transactions, to see the order of 
operations. (Other times you may want the start of transaction time 
instead; it just depends on the circumstance.)

When using this function, your trigger should fire *before*, not *after*:

CREATE TRIGGER my_trigger_name
BEFORE INSERT OR UPDATE ON the_table
FOR EACH ROW EXECUTE PROCEDURE update_last_modified();

HTH,
Jon

--
Jon Jensen
End Point Corporation
http://www.endpoint.com/
_______________________________________________
interchange-users mailing list
suppressed
http://www.icdevgroup.org/mailman/listinfo/interchange-users