Postgres doesn't allow locking (e.g. with SELECT FOR UPDATE) relations on the right side of a LEFT JOIN partially for convenience, and partially because it's unclear what the locking semantics are for join output rows that have no corresponding row from the right relation. For similar reasons, neither input of a FULL JOIN can be locked.

postgres=# SELECT * FROM xy LEFT JOIN ab ON x = a FOR UPDATE;
ERROR:  FOR UPDATE cannot be applied to the nullable side of an outer join

Currently, CRDB does not make this restriction:

root@localhost:26257/defaultdb> EXPLAIN (OPT) SELECT * FROM xy LEFT JOIN ab ON x = a FOR UPDATE;
              info
---------------------------------
  left-join (hash)
   ├── scan xy
   │    └── locking: for-update
   ├── scan ab
   │    └── locking: for-update
   └── filters
        └── x = a
(7 rows)

Time: 2ms total (execution 1ms / network 0ms)

Fixing this may simplify the work involved with the re-evaluation step of read-committed isolation. It may also become necessary for a correct implementation when #75457 is fixed, for the reason stated above.

Jira issue: CRDB-24694