Re: Postgresql "FIFO" Tables, How-To ?

All,

There's a database format called "rrd" or round robin database. this was
specifically designed for wrap-around data storage. since you are trying to
store 10mil+ syslog messages this might not be the right tool. I'm just
mentioning it because it perhaps the way the rrd keeps track of wrap-around
might be a good way to implement this in postgres.

Sincerely,

Leon Oosterwijk



> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Dennis Gearon
> Sent: Wednesday, July 16, 2003 1:14 PM
> To: Jean-Luc Lachance
> Cc: softlist@codeangels.com; pg_general
> Subject: Re: [GENERAL] Postgresql "FIFO" Tables, How-To ?
>
>
> Good idea!
>
> Jean-Luc Lachance wrote:
>
> > OUCH!!! Do a COUNT(*) on a 10M row table???? Forget it.
> >
> > Here is a simple solution.
> > Add a SERIAL field to the table.
> > Set the maximum value for that sequence to the number of records you
> > want to keep.
> > Use a before insert trigger to replace the insert with an update if the
> > key already exist.
> >
> > No need for a cron.
> >
> >
> >
> > Dennis Gearon wrote:
> >
> >>use a PL/PGSQL function. Just do count(*) to find out how many
> there are, calculate how many to be deleted, and put a timestamp
> field in the table. NOW, how to  select the correct ones to
> delete is PROBABLY done by:
> >>
> >>DELETE FROM table_in_question
> >>WHERE some_primary_key_id IN
> >>   (SELECT some_primary_key_id
> >>    FROM table_in_question
> >>    ORDER BY the_time_stamp_field
> >>    LIMIT the_qty_to_be_deleted);
> >>
> >>More than likely, in a concurrent environment, you will
> oscillate between:
> >>
> >>(the maximum number you want)
> >>
> >>and
> >>
> >>(the maximum number you want - the maximum current connections).
> >>
> >>Unless you so some kind of table locking.
> >>
> >>Kirill Ponazdyr wrote:
> >>
> >>
> >>>Hello,
> >>>
> >>>We are currently working on a project where we need to limit number of
> >>>records in a table to a certain number. As soon as the number has been
> >>>reached, for each new row the oldest row should be deleted
> (Kinda FIFO),
> >>>thus keeping a total number of rows at predefined number.
> >>>
> >>>The actual limits would be anywhere from 250k to 10mil rows per table.
> >>>
> >>>It would be great if this could be achieved by RDBMS engine
> itself, does
> >>>Postgres supports this kind of tables ? And if not, what would
> be the most
> >>>elegant soluion to achieve our goal in your oppinion ?
> >>>
> >>>Regards
> >>>
> >>>Kirill
> >>>
> >>
> >>---------------------------(end of broadcast)---------------------------
> >>TIP 9: the planner will ignore your desire to choose an index
> scan if your
> >>      joining column's datatypes do not match
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> >
> >                http://www.postgresql.org/docs/faqs/FAQ.html
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)