Re: Self-join elimination discards differing row lock wait policies

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> 于2026年10月25日周日 08:56写道：
>
> So I still don't see a reachable bug here, but there's dead code
> (the bit about handling an rmark without kmark), and the
> commentary omits even more than I thought before.
>

Thanks for the very detailed analysis. I tested the cases you
described, and you are right: my original example does not demonstrate
a reachable correctness bug.

Following your suggestion, the attached v2 patch removes the dead code
for transferring an rmark when there is no kmark, adds assertions for
that invariant, and expands the comments about why relations with
different row mark types are not currently merged.

> I experimented with writing logic that would allow merging locks of
> different strengths, but I'm not really convinced that it's worth the
> trouble.

I haven't included the other improvement you suggested—performing SJE
when lock strengths differ and retaining the stronger lock type, along
with an appropriate rule for choosing the waitPolicy.
I think it would be better to consider that separately from this cleanup patch.

During testing, I also noticed another possible missed optimization.
For example, adding a restriction such as:
postgres=# EXPLAIN (VERBOSE, COSTS OFF)
SELECT p.a, q.a
FROM t1 p
JOIN t1 q ON p.a = q.a
WHERE p.a = 18;
QUERY PLAN
-------------------------------
Nested Loop
Output: p.a, q.a
-> Seq Scan on public.t1 p
Output: p.a, p.b
Filter: (p.a = 18)
-> Seq Scan on public.t1 q
Output: q.a, q.b
Filter: (q.a = 18)
(8 rows)

to the self-join query prevents SJE, whereas removing that restriction
allows SJE to occur.
It appears that the equivalence class containing the constant absorbs
the equality between p.a and q.a, so
generate_join_implied_equalities() returns NIL and SJE gives up.

Do you think this case is worth improving, perhaps as a separate patch?

--
Thanks,
Tender Wang