Hi, I'm trying to set up logging tables and need a bit of help. I would like to ensure that things are stored so that they can be retrieved in the correct sequence. The example at http://www.postgresql.org/docs/postgres/rules17277.htm says: CREATE TABLE shoelace_log ( sl_name char(10), -- shoelace changed sl_avail integer, -- new available value log_who name, -- who did it log_when datetime -- when ); CREATE RULE log_shoelace AS ON UPDATE TO shoelace_data WHERE NEW.sl_avail != OLD.sl_avail DO INSERT INTO shoelace_log VALUES ( NEW.sl_name, NEW.sl_avail, getpgusername(), 'now'::text ); However is there a guarantee that datetime is sufficient for correct order if an item is updated by different people one after the other at almost the same time? I would prefer something like CREATE TABLE shoelace_log ( log_sequence serial -- sequence of events sl_name char(10), -- shoelace changed sl_avail integer, -- new available value log_who name, -- who did it log_when datetime, -- when ); CREATE RULE log_shoelace AS ON UPDATE TO shoelace_data WHERE NEW.sl_avail != OLD.sl_avail DO INSERT INTO shoelace_log VALUES ( NEW.sl_name, NEW.sl_avail, getpgusername(), 'now'::text ); However I notice there isn't a column name specification in the DO INSERT INTO, how would I format the INSERT INTO statement so that log_sequence is not clobbered? Can I use the normal INSERT into format and specify the columns? I haven't managed to get it to work that way. Would defining the sequence at the end of the table help? That would be untidy tho ;). Can/should I use now() instead of 'now'::text? The serial type is an int4. Hmm, there actually may be more than 2 billion updates to keep track off :). But I suppose we could cycle the logs and resequence. Cheerio, Link. ************
