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

Lincoln Yeoh wrote:

> 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.

...

> 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 ;).

I haven't used rules yet, but in reviewing the 'CREATE RULE' documentation at

    http://www.postgresql.org/docs/postgres/sql-createrule.htm

I believe the 'action' after the 'DO' can be any SQL statement, so you should
be able to name the columns.  Am I missing something?

And if the 'serial' type doesn't mainatain a serial order across multiple
clients/users, I'm in deep trouble. :)

>
>
> Can/should I use now() instead of 'now'::text?

I've been using a default datetime column definition of

    log_when    datetime default CURRENT_TIMESTAMP

in other similar situations, and that seems to work as you wish (you can then
leave it out of the INSERT statement).

Cheers.
Ed