Re: [PERFORM] Best way to check for new data.

[Merlin Moncure]

There are a few ways to do this...thinking about it a bit, I would add a 
timestamp column to your log table (indexed) and keep 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 cause some 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 immutable to force planner to treat 
as a constant (others might tell you to do different here).

Merlin


From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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
 

---(end of broadcast)---
TIP 6: explain analyze is your friend

[PERFORM] Best way to check for new data.

[Rodrigo Madera]

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

Re: [PERFORM] Best way to check for new data.

[Havasvölgyi Ottó]

Rodrigo,

You could use LISTEN + NOTIFY with 
triggers.
In after_insert_statement trigger you could notify 
a listener, the client could query it immediately.

Best Regards,
Otto


  - Original Message - 
  From: 
  Rodrigo Madera 
  To: pgsql-performance@postgresql.org 
  
  Sent: Friday, October 28, 2005 11:39 
  PM
  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