Re: FOR UPDATE SKIP LOCKED and get locked row/avoid updating other row(s)

On Tuesday, February 13, 2024, Wiwwo Staff <wiwwo@wiwwo.com> wrote:

Hi!

I am implementing a queue using PostgreSQL.

I am of course using "FOR UPDATE SKIP LOCKED".

Is there any way I can tell PostgreSQL to only "operate" on the locked row, and/or a way to reference it?

Some explanations of what I mean:

• I have a table with N rows
• I lock row X with a PG Function, in transaction 1
  
• I do something in my code, using transaction 2
  
• I update the status of row X with a PG Function, in transaction 1

In last step, I update the row X status, passing my function the ID of this row X.

But, nothing stops me from updating row Y in the queue table, for whatever reason.

My question again: any way to force a "Just update the row X you locked before, and nothing else/more?"

Holding locks and open transactions while doing queue job processing is generally not a good idea anyway so the lack of this ability doesn’t seem too problematic - but a cursor can probably get you close  You can abstract interactions with the queue table through a functional API to implement a server-enforced policy, removing the ability for clients to do arbitrary queries on the underlying tables.  The checkout function can tag who got the job and the completion function can validate the input arguments supplied by the client belong to a job they checked out.

David J.