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

On 16 Jul 2003 at 19:09, Kirill Ponazdyr wrote:
> It is for a advanced syslog server product we are currently developing.
>
> The very basic idea is to feed all syslog messages into a DB and allow
> easy monitoring and even correlation, we use Postgres as our DB Backend,
> in big environments the machine would be hit with dozens of syslog
> messages in a second and the messaging tables could grow out of controll
> pretty soon (We are talking of up to 10mil messages daily).

You are looking at wrong end of pipe with fifo approach.

What I would do.

1. Create an empty table.
2. Create a table stamped with date and time. Let's call it 'T'
3. Create rule on original table to direct all the processing to 'T'.
4. Do record counting in middleware application. A mutex in application would
be far less expensive than PG locking mechanism with disk.

When 'T' fills

a. create another table 'T1',
b. rewrite rules accordingly

and bingo you are done.

Do (a) and (b) in transaction and nobody would notice the difference.No vacuum
drag, no dead tuples nothing. And you are free to play with 'T' the way you
want, completely independently.

I am sure this will work barring relations between rules and transaction. I
don't know if rewriting rules is transaction safe or not. Most probably yes but
would like to confirm that.

Bye
Shridhar

--
What's this script do? unzip ; touch ; finger ; mount ; gasp ; yes ; umount
; sleepHint for the answer: not everything is computer-oriented. Sometimes
you'rein a sleeping bag, camping out.(Contributed by Frans van der Zande.)