Describe the problem

This is similar to #57031, but it's not the same. Currently, SELECT FOR UPDATE places locks on each key scanned by the base index scan. This means that even if some of those keys are later filtered out by a predicate which could not be pushed into the scan, they will still be locked.

This is not the correct behavior (according to PG), but was initially implemented for ease of implementation and to avoid a two-phase locking process with multiple RPCs.

To Reproduce

Setup

create table t(a int primary key, b bool);
insert into t values (1, false), (2, true);

Example

--tx1                                          --tx2
begin transaction;
                                               begin transaction;
select * from t  
where b=true for update;
                                               select * from 
                                               t where b=false for update; -- BLOCKS!

Expected behavior

txn2's SELECT query should not block, because txn1 should have only acquire a lock on the row that it returned ((2, true)).

Jira issue: CRDB-12682

Epic CRDB-25322