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

On Tue, 2024-02-13 at 11:17 +0000, Wiwwo Staff wrote:
> 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?"

I don't think there is a way to enforce that. Your application code
has to do the right thing.

Yours,
Laurenz Albe