1. 12
  1. 4

    There was a conversation about tuple spaces and whether you can emulate them with a relational DB last month:


    Then I came across this comment which linked to the posted article.


    Admittedly I don’t understand all the subtleties, but this feature was added in Postgres 9.5 (January 2016) to make correct work queues easier to write in SQL.

    The article has an example that:

    1. Scans the queue table in itemid order
    2. Tries to acquire a lock on each row. If it fails to acquire the lock, it ignores the row as if it wasn’t in the table at all and carries on.
    3. Stops scanning once it’s locked one item
    4. Returns the itemid of the locked item
    5. Looks up the found itemid in the index to get its physical location
    6. Marks the tuple as deleted (but this doesn’t take effect until commit)
    1. 2

      Not mentioned in the article, but things get much simpler if you don’t need fifo behaviour. In practice, I’ve seen projects rarely need that. In that case you don’t need the subquery for ordering. Just update ... limit 1 ... returning and as long as you have enough workers, it’s going to be good enough.

      1. 1

        Do you know if that works in sqlite? I have something that’s not very performance intensive that I would like to work in sqlite, and doesn’t need LIFO

        1. 2

          Since 3.35.0 (https://www.sqlite.org/lang_returning.html). I’ve got something which does

          update $table set done=datetime('now','-2000 days') where ( done is null or done='' ) returning substr(id, 10), date(published), title limit 1;

          as a “get me something which isn’t done yet and flag it as being processed” (currently working in 3.37.0 2021-11-27 14:13:22 bd41822c)

          1. 1

            Never tried, but it seems to support all the relevant syntax and there can be only one writer at a time, so it should be safe.