PostgreSQL LISTEN/NOTIFY not working

Question

Here is the basic setup:

• A PHP script writes to a table in a database and then issues NOTIFY job_added. It then begins listening for a response by issuing LISTEN job_complete

• A daemon (written in C) has already issued a LISTEN jod_added and hence wakes up and processes the table.

• The daemon processes the table and writes results into a results table before calling NOTIFY job_complete

• The PHP script then wakes up and retrieves the result from the results table.

All but the last step is working. The daemon uses libpq and I have checked the success of the NOTIFY issued by the daemon once it has added the result to the results table.

So I think the problem lies with the PHP script. Here is the pertitent code:

$id = query("INSERT into jobs_table (/* details not important */) VALUES (/* */) RETURNING id");

query("NOTIFY job_added");
//daemon wakes up and does its thing.

query("LISTEN job_complete".$id);

$time = time();
while((time() - $time) < 30) {
    $notify = pg_get_notify($conn);
    if($notify) {
        // Never gets here
        if($notify['message']=="job_complete".$id) {
            //our job has completed
            break;
        }
    }
    usleep(25000);
}

So we add to the jobs table, issue a LISTEN and loop for 30seconds until we get the notification that our job is done.

The problem is that pg_get_notify() never picks up the NOTIFY issued by the daemon. Note, the NOTIFY issued by the daemon happens after the LISTEN by the php script, I checked.

Is there anything I am doing that is completely wrong? Btw, I am well aware query() isn't a built-in function, it was added for brevity.

Thanks

Answer 1

I would be willing to bet that the problem is that you are not committing the transaction. Notifies are raised on commit.

Try:

 query('COMMIT');

See if that raises the notification for you.