On Tue, 13 Feb 2024 at 14:49, David G. Johnston <david.g.johns...@gmail.com> wrote:
> On Tuesday, February 13, 2024, Wiwwo Staff <wi...@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. > > 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
