Postgres Pro
Downloads
Home   >   mailing lists

Re: BUG #16676: SELECT ... FETCH FIRST ROW WITH TIES FOR UPDATE SKIP LOCKED locks rows it doesn't return - Mailing list pgsql-bugs

From David Christensen
Subject Re: BUG #16676: SELECT ... FETCH FIRST ROW WITH TIES FOR UPDATE SKIP LOCKED locks rows it doesn't return
Date
Msg-id 7EA65D44-7C3A-4FC2-8DE1-1F59BBB10B37@endpoint.com
Whole thread Raw
In response to Re: BUG #16676: SELECT ... FETCH FIRST ROW WITH TIES FOR UPDATE SKIP LOCKED locks rows it doesn't return  (David Christensen <david@endpoint.com>)
Responses Re: BUG #16676: SELECT ... FETCH FIRST ROW WITH TIES FOR UPDATE SKIP LOCKED locks rows it doesn't return  (Alvaro Herrera <alvherre@alvh.no-ip.org>)
Re: BUG #16676: SELECT ... FETCH FIRST ROW WITH TIES FOR UPDATE SKIP LOCKED locks rows it doesn't return  (David Christensen <david@endpoint.com>)
List pgsql-bugs
Tree view 
> On Oct 19, 2020, at 6:59 PM, David Christensen <david@endpoint.com> wrote:
>
>> On Oct 19, 2020, at 6:07 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>
>> David Christensen <david@endpoint.com> writes:
>>> Proposed fix:
>>> Reorder Limit/LockRows nodes to prevent locking extra tuples in FETCH FIRST WITH TIES
>>
>> Isn't that going to break more cases than it fixes?
>
> In the case of Limit, isn’t LockRows supposed to only lock the number of actual rows returned?
>
> What are the scenarios that this might break and do you have any ideas for alternate fixes?

Will now that I think about it, if the LockRows node is responsible for skipping locked rows, etc then my proposed
solutionis clearly wrong.  

Maybe splitting LockRows into two nodes, one for locking and one for emitting unlocked nodes then interleaving Limit in
between?(Or only doing something along these lines for this admittedly narrow use case. ) 

Open to ideas on the appropriate fix.

David

pgsql-bugs by date:

Previous
From: David Christensen
Date:
Subject: Re: BUG #16676: SELECT ... FETCH FIRST ROW WITH TIES FOR UPDATE SKIP LOCKED locks rows it doesn't return
Next
From: Michael Paquier
Date:
Subject: Re: invalid alloc size error possible in shm_mq