There are a few ways to do this...thinking about it a bit, I would add a timestamp column to your log table (indexed)
andkeep a control table which keeps track of the last log print sweep operation.
The print operation would just do
select * from log where logtime > (select lastlogtime());
The idea here is not to have to keep track of anything on the log table like a flag indicating print status, which will
causesome bloat issues. All you have to do is reindex once in a while.
lastlogtime() is a function which returns the last log time sweep from the control table. we use a function declared
immutableto force planner to treat as a constant (others might tell you to do different here).
Merlin
________________________________________
From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Rodrigo
Madera
Sent: Friday, October 28, 2005 5:39 PM
To: pgsql-performance@postgresql.org
Subject: [PERFORM] Best way to check for new data.
I have a table that holds entries as in a ficticious table Log(id integer, msg text).
Lets say then that I have the program log_tail that has as it´s sole purpose to print newly added data elements.
What is the best solution in terms of performace?
Thank you for your time,
Rodrigo