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

On Tue, 13 Feb 2024 at 14:49, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
wrote:

> On Tuesday, February 13, 2024, Wiwwo Staff <wiwwo(at)wiwwo(dot)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.
>
>
Hi David,
thanks for your answer.
You are absolutely right, it is not a good idea. The reasoning behind is 1)
everything is Python controlled and 2) -more importantly- the DB user in
charge of consuming the queue has grants just ion that part, and absolutely
nothing else. Hence the 2 sessions and the process separation.

Cosimo