From: | Ed Loehr <ELOEHR(at)austin(dot)rr(dot)com> |
---|---|
To: | Lincoln Yeoh <lylyeoh(at)mecomb(dot)com> |
Cc: | PostreSQL <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: [GENERAL] logging stuff in the right sequence. |
Date: | 1999-11-23 16:33:05 |
Message-ID: | 383AC1C1.4574D9DC@austin.rr.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Kane Tao | 1999-11-23 16:46:09 | Re: [GENERAL] Re: Is PostgreSQL ready for mission criticalapplications? |
Previous Message | jose soares | 1999-11-23 16:05:45 | Re: [GENERAL] PL |