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

> On Oct 19, 2020, at 7:33 PM, David Christensen <david(at)endpoint(dot)com> wrote:
>
> 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. )

This does appear to be quite a bit harder than originally hoped for on the surface; creating a node to “pass-thru” unlocked tuples without locking them would require building out some additional infrastructure that does not appear to be present in all AMs; something to allow a test for locks without actually calling table_tuple_lock() formally. Perhaps adding a param to indicate “test” only—not sure if something like similar to heapam's “test_lockmode_for_conflict()” is something we’d need to had. (Or even a new lock mode which doesn’t create RowMarks but skips any conflicting modes.)

That said, I think it might be worth trying to go down the road of “forbid this behavior”, particularly if a workaround is available. Perhaps looking into why Álvaro’s plan was generating the multiple LockRows nodes would be more fruitful and less likely to cause backpatching pain.

David
--
David Christensen
Senior Software and Database Engineer
End Point Corporation
david(at)endpoint(dot)com
785-727-1171