Re: [GENERAL] logging stuff in the right sequence.

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.