Hi, Self-join elimination discards one of the row lock wait policies when two aliases of the same relation use different policies.
How to reproduce: CREATE TABLE t1 (a int PRIMARY KEY, b int); INSERT INTO t1 SELECT i, (i * 7) % 13 FROM generate_series(1, 40) i; ANALYZE t1; In session 1, lock the row where a = 5: postgres=# begin; BEGIN postgres=*# SELECT a FROM t1 WHERE a=5 FOR UPDATE; a --- 5 (1 row) In session 2: postgres=# show enable_self_join_elimination ; enable_self_join_elimination ------------------------------ on (1 row) postgres=# SELECT p.a, q.a FROM t1 p JOIN t1 q ON p.a = q.a WHERE p.b = 9 AND q.b = 9 FOR UPDATE OF p NOWAIT FOR UPDATE OF q SKIP LOCKED; a | a ----+---- 18 | 18 31 | 31 (2 rows) postgres=# set enable_self_join_elimination = off; SET postgres=# SELECT p.a, q.a FROM t1 p JOIN t1 q ON p.a = q.a WHERE p.b = 9 AND q.b = 9 FOR UPDATE OF p NOWAIT FOR UPDATE OF q SKIP LOCKED; ERROR: could not obtain lock on row in relation "t1" The two aliases have PlanRowMarks with the same markType and locking strength, but different waitPolicy values. The row mark equivalence check in remove_self_joins_one_group() currently compares only markType. Self-join elimination is therefore allowed, and remove_self_join_rel() discards the removed relation's PlanRowMark. The query is then executed using only the wait policy of the retained relation. In this example, the NOWAIT behavior is lost, and the locked row is handled using SKIP LOCKED. Reversing the wait-policy order exposes the opposite behavior change. A blocking policy can be lost in the same way. The attached patch requires the row mark type, locking strength, and wait policy to match before eliminating the self-join. Thoughts? -- Thanks, Tender Wang
From 2f7816cdbd94dec0c4be95579b3aafd0262be450 Mon Sep 17 00:00:00 2001 From: Tender Wang <[email protected]> Date: Sat, 24 Oct 2026 15:52:09 +0800 Subject: [PATCH] Preserve row lock semantics during self-join elimination Self-join elimination checks whether row marks on the relations being merged have the same RowMarkType. However, it does not compare their lock strengths or wait policies. Consequently, when two aliases have row marks with the same RowMarkType but different wait policies, such as NOWAIT and SKIP LOCKED, remove_self_join_rel() discards one PlanRowMark and retains only the other policy. This can change an error into successful execution with the locked row skipped, or vice versa. Require the row mark type, lock strength, and wait policy to match before eliminating the self-join. --- src/backend/optimizer/plan/analyzejoins.c | 13 ++++++++----- 1 file changed, 8 insertions(+), 5 deletions(-) diff --git a/src/backend/optimizer/plan/analyzejoins.c b/src/backend/optimizer/plan/analyzejoins.c index 7aad91fd34e..6d55e243352 100644 --- a/src/backend/optimizer/plan/analyzejoins.c +++ b/src/backend/optimizer/plan/analyzejoins.c @@ -1827,10 +1827,10 @@ remove_self_joins_one_group(PlannerInfo *root, Relids relids) continue; /* - * Check Row Marks equivalence. We can't remove the join if the - * relations have row marks of different strength (e.g., one is - * locked FOR UPDATE, and another just has ROW_MARK_REFERENCE for - * EvalPlanQual rechecking). + * Check row mark equivalence. If both relations have row marks, + * their mark type, locking strength, and wait policy must match. + * Otherwise, discarding the removed relation's row mark could change + * tuple-locking or EvalPlanQual behavior. */ foreach(lc, root->rowMarks) { @@ -1850,7 +1850,10 @@ remove_self_joins_one_group(PlannerInfo *root, Relids relids) if (kmark && rmark) break; } - if (kmark && rmark && kmark->markType != rmark->markType) + if (kmark && rmark && + (kmark->markType != rmark->markType || + kmark->strength != rmark->strength || + kmark->waitPolicy != rmark->waitPolicy)) continue; /* -- 2.43.0
