Block SELECT until results available

Question

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

Answer 1

You could look into LOCK and FOR UPDATE. FOR UPDATE can allow a query to wait until the row(s) that are being selected are unlocked. I'm not sure if there is a timeout or what resources impact having a large number of these can have, but it's one possibility.

Answer 2

there is no blocking select statement.

you could just issue the select statement on a regular basis - which incurs a certain overhead. If the query is expensive, then you might write a cheaper one like count(*) and keep track of new entries that may possibly be returned, and if the number changes issue the more expensive query.

Answer 3

Take a look at LISTEN/NOTIFY:

The NOTIFY command sends a notification event to each client application that has previously executed 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 an "ON INSERT" trigger to the table to fire off a NOTIFY event. However, you will need another mechanism to figure out which records need to be selected as the ability to deliver a payload with the NOTIFY event won't be available until 9.0:

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

Answer 4

You're trying to get an interrupt (event), when you should probably think about polling.

Create and call a stored procedure which will determine if there are new rows that the client should retrieve. If this is a web app, call an Ajax method periodically which, on the server, will query the db to see if there are new rows since its last call. If so, run another query to retrieve them and send them back to the client.

Answer 5

I love postgres and all, but if you're trying to do something simple and not super enterprisey, perhaps redis will be enough for you. I've had a lot of success with using it myself, and it can scale.

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