Thread: 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.
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
At 10:33 AM 23-11-1999 -0600, you wrote: >Lincoln Yeoh wrote: > >> Hi, >> >> I'm trying to set up logging tables and need a bit of help. I got it to work - I screwed up somewhere... ;). >And if the 'serial' type doesn't mainatain a serial order across multiple >clients/users, I'm in deep trouble. :) Actually I meant "is datetime good enough?". It doesn't seem to be, that's why I'm using a serial as well as datetime. I tried doing insert ...; insert ....; and when I do the order by datetime it can be the wrong order. Plus the "order by" resolution only seems to be down to the second. The docs say datetime resolution is a microsecond, but I haven't figured out how to display stuff down to that. When datetime matches the most recently updated record is displayed last. So if you "order by datetime" it's fine. But if you "order by datetime desc", you have probs. If a record is updated it will move to the end. >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). Hmm what's the recommended way of doing it? Or there are and will always be many ways of doing this (ala Perl). I've seen 'now'::text in the FAQ http://www.postgresql.org/docs/postgres/datatype1019.htm mentions something like "current", but that doesn't work like 'now'::text works (or CURRENT_TIMESTAMP). There's one more thing I'd like in the documentation- for the data types it'll be good to have the min/max values/sizes for all of them. For example, it's not obvious what the maximum size a 'text' column can be. In other places in the docs I read the max row size can be 8K (by default). So can I assume that the max size for 'text' is limited by that? Similar for sequence - the default max seq num is about 2^31. But no mention of what the max "max seq num" can be. I could assume it's actually an int4. But who knows maybe it's int8. Cheerio, Link
I can not get THE book's pdf link. Not sure whether it is my side's irregular firewall issue. If in that case, can it be a http link? I guess I question/request is: seems the ftp link is broken? if not, can it be a http one? thanks Kai