CODEWITHSUNDEEP
phpsqlpostgresql
goffrie
goffrie

Reputation: 471

Block SELECT until results available

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?

Upvotes: 5

Views: 2130

Answers (5)

mikelikespie
mikelikespie

Reputation: 5842

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/

Upvotes: 0

3Dave
3Dave

Reputation: 29061

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.

Upvotes: 0

Matthew Wood
Matthew Wood

Reputation: 16427

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

Upvotes: 5

Kendall Hopkins
Kendall Hopkins

Reputation: 44124

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.

Upvotes: 0

Randy
Randy

Reputation: 16673

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.

Upvotes: 2

Related Questions