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

>
>
>That doesn't help you limit the number of rows in the table though
>because what row is going to get "pushed out" of the table?  The nifty
>thing about using a wrapping sequence is that the id's are sequential
>across transactions, which correctly maps to the progression of time,
>which obviates the need for relying on any kind of a date column for
>doing syslog message ordering.
>
You must have misunderstood my suggestion - you'd still have a wrapping 
sequence, that would generate your ids, and determine which row would be 
pushed out - you would just have *another* sequence *in addition* to 
that, that would count the total number of inserts, so that you would 
know if the wraparound had already happened without having to issue 
another query, which, by the way, is not very robust, because another 
connection may be inserting at the same time, so your update will not 
see the new rowid, but the insert will fail... Keeping two sequences - 
one for id generation, and one for counting solves that.

>
>  
>
>>... or you could do it with the single sequence still, if you get
>>rid of the limit and cycle, and just do if nextval >= limit then
>>update ... where id = nextval % limit else insert
>>    
>>
>
>There's no logic necessary with the above function.  You get the next
>val in the sequence and update the ID.  No need to delete, no nothing
>other than VACUUM to remove the dead tuples, which, you have to do
>regardless of the method chosen.  A DELETE + UPDATE is just a waste of
>CPU and IO with the table being MVCC backed.  Just UPDATE and be done
>with it.
>
Well... first of all, "just UPDATE" is *exactly* the same internally as 
DELETE+INSERT.
Secondly, I just don't like having to populate the table with millions 
of empty rows just to keep that 'no logic' function happy :-)
And finally, nextval % limit hardly qualifies as 'logic'  to me... and 
that's really all there is.

>
>That said, it'd be cool if there was a GLOBAL TEMP table that could be
>used for this kinda stuff... not having something WAL backed on this
>kind of an application would make things really scream.
>  
>
Hmmm... That brings up the question I wanted to ask from the very 
beginning, but just didn't dare :-)
What is the reason to throw logs into the database in the first place? 
What's wrong with 'normal' syslog?
The only difference I can imagine is MVCC, transaction consistency, etc...
But if you don't care about all that, why not just throw things into a 
file? Now *that* indeed would scream :-)

Dima