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

You probably want to use sleep, usleep or time_sleep_until rather than running a loop until time runs out. — Charles
– Charles, Commented Mar 15, 2011 at 17:11
Maybe you're notifying on "job_complete" instead of "job_complete".$id? — Brian L
– Brian L, Commented Mar 15, 2011 at 21:39
brian-l: Afraid not. In the daemon I basically do snprintf(buf, sizeof(buf), "NOTIFY job_complete%d", job_id); and then call PQexec(conn, buf); from libpq. — tbh1
– tbh1, Commented Mar 16, 2011 at 8:52
Problem solved. I wasn't passing a connection handle to pg_get_notify, I was passing a database object (for a custom class) instead. Silly me! Apologies. — tbh1
– tbh1, Commented Mar 16, 2011 at 10:01
You might consider posting this as an answer for future reference — user330315
– user330315, Commented Mar 16, 2011 at 16:24

Chris Travers · Accepted Answer · 2013-03-13 12:54:43Z

3

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.

answered Mar 13, 2013 at 12:54

Chris Travers

26.6k6 gold badges70 silver badges191 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL LISTEN/NOTIFY not working

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related