Wait until row becomes available with SKIP LOCKED

Question

I'm using PostgreSQL with SKIP LOCKED to implement a queue as described in What is SKIP LOCKED for in PostgreSQL 9.5?. Basically, this works fine, but sometimes when the workers are fast and there is not that much work to do, the queue runs empty. IOW: The SELECT does not find anything, and so the statement runs, does nothing in the end, and we're done.

Now what I would like to do is to re-run the statement as soon as a new row comes in, and try again (although all the other workers may try to do this as well). This in the end comes down to polling the database, which is – let's say – not optimal.

Of course I could use PostgreSQL's NOTIFY feature to inform the workers once a new row has been written, but I was wondering if this is possible in the SELECT itself, such as leaving it open until it succeeds and returns a record? Something such as a long-running SELECT which stays open as long as it takes to return a result.

Is this possible?

Seems like you need to use a message queue instead of polling the database. Then your client could block on the queue until there's something to read. When a client inserts a new row, that client would also need to push a notification onto the message queue so consumers would read it. — Bill Karwin, Jul 09 '19 at 15:05

olinox14 · Accepted Answer · 2019-07-09T15:40:43.330

According to those answers, this is not possible to force a select query to wait for a result:

An idea may be to lock the table when it turns to be empty, and releases it before an insertion is made, but that sounds creepy... (see there: https://stackoverflow.com/a/17525747/4279120)

My opinion is that using NOTIFY, even if it won't achieve exactly what you expect, would be more suitable here, because it has been made for such cases.

Of course, you could implement some get_item() PL/SQL method with a simple WHILE loop, but I assume this is not what you are looking for...

Just for further knowledge, a PLSQL function which waits for the queue to return an item:

CREATE OR REPLACE FUNCTION get_item() 
RETURNS int8 
LANGUAGE plpgsql
AS
$$
DECLARE
    count int8 = 0;
    id int8;
BEGIN
    WHILE id IS Null LOOP
        DELETE FROM queue
        WHERE itemid = (
          SELECT itemid
          FROM queue
          ORDER BY itemid
          FOR UPDATE SKIP LOCKED
          LIMIT 1
        )
        RETURNING itemid INTO id;
        IF id IS Null THEN
           PERFORM pg_sleep(1);
        END IF;
    END LOOP;

    RETURN id;
END;
$$;

Thanks for your detailed answer. Just one question: Why do you consider NOTIFY as not optimal here? — Golo Roden, Jul 09 '19 at 15:36
I just meant that it was not the solution you expected, sorry: that was not really obvious. However, I think this is the good solution, because this is specially designed for such cases... — olinox14, Jul 09 '19 at 15:39

Wait until row becomes available with SKIP LOCKED

1 Answers1