Block SELECT until results are available.

Question

I am trying to write a PHP script for 'long-polling', returning data when new rows are added to the Postgres database table. Is there a way to get a SELECT query to return only when it will return results, otherwise blocking? Or should I use a different signaling mechanism outside the database?

Answer 1

Take a look at LISTEN / NOTIFY:

The NOTIFY command sends a notification event to each application client that previously executed the LISTEN name for the specified notification name in the current database

http://www.postgresql.org/docs/8.4/static/sql-notify.html

You can add the “ON INSERT” trigger to the table to disable the NOTIFY event. However, you will need another mechanism to determine which records to choose, since the ability to deliver a payload with a NOTIFY event will not be available until 9.0:

http://www.postgresql.org/docs/9.0/static/sql-notify.html

Answer 2

There is no lock selection instruction.

you can simply issue the select statement on a regular basis, which imposes certain overhead. If the query is expensive, then you can write a cheaper option, such as count (*), and keep track of new records that can be returned, and if the number of changes causes a more expensive query.

Answer 3

You can watch LOCK and FOR UPDATE . FOR UPDATE may allow the query to wait until the rows (rows) to be selected are unlocked. I'm not sure if there is a timeout or what resources affect a large number of them, but this is one of the possibilities.

Answer 4

You are trying to get an interrupt (event) when you probably should consider polling.

Create and call a stored procedure that will determine if there are new lines that the client should receive. If this is a web application, periodically call the Ajax method, which the server requests db to see if there are any new lines since the last call. If so, run another request to receive them and send them back to the client.

Answer 5

I like postgres and that’s all, but if you are trying to do something simple, rather than super-entrepreneurship, you may find redis enough. I had great success when I used it myself, and it can scale.

http://code.google.com/p/redis/