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 wrote: > On Tuesday, February 13, 2024, Wiwwo Staff 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
Re: FOR UPDATE SKIP LOCKED and get locked row/avoid updating other row(s)
On Tuesday, February 13, 2024, Wiwwo Staff 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.
Re: FOR UPDATE SKIP LOCKED and get locked row/avoid updating other row(s)
On Tue, 2024-02-13 at 11:17 +, 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
