Skip site navigation (1) Skip section navigation (2)

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

From: Lincoln Yeoh <lylyeoh(at)mecomb(dot)com>
To: PostreSQL <pgsql-general(at)postgreSQL(dot)org>
Subject: Re: [GENERAL] logging stuff in the right sequence.
Date: 1999-11-23 08:52:28
Message-ID: 3.0.5.32.19991123165228.008c6820@pop.mecomb.po.my (view raw or flat)
Thread:
Lists: pgsql-general
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.




Responses

pgsql-general by date

Next:From: Fabian.FrederickDate: 1999-11-23 09:33:20
Subject: [GENERAL] Socket file lock
Previous:From: RoodieDate: 1999-11-23 08:51:01
Subject: PL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group