Large journal as psql table. Good idea? Triggering.

If it's going to be too big for a database, then it's going to be worse
using flat-files on a disk :)

I'd suggest putting it in a database, and have 2 tables:1) "New" messages to be sent2) Archive messages

That way the polling machine only has to wait for the database to scan the
"New" table, then move them in to the Archive table once it has sent them.

Depending how many records you're actually looking at, one table with an
index would probably be fine :)

~p

-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Bryce Nesbitt
Sent: Tuesday, 24 April 2007 09:12

I'm considering using a postgres table for something that could be done
with a flat file.  Is this a good idea?

I have events on a machine "A", which need to be sent by an SMS/Cell
Phone modem that's on a totally different machine "B".  Potentially this
is a job for a flat file FIFO.

But I'm thinking that maybe it's a job for a database table.  Each new
row would be written with a status (10="new").  And that the modem
process would poll for new rows.  Problem is there will be lots of rows,
but only a trivial few will be "new".  The huge index file and the
polling seem like a drag on the database, unless there is a way to optimize.


*******************Confidentiality and Privilege Notice*******************

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments