| From: | Tender Wang <tndrwang(at)gmail(dot)com> |
|---|---|
| To: | PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
| Subject: | Self-join elimination discards differing row lock wait policies |
| Date: | 2026-10-24 08:06:27 |
| Message-ID: | CAHewXNn989Q4kP8Ewaeo8cBp-8jK6KX8CRs5UX6rAPMBtx54Pg@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
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
| Attachment | Content-Type | Size |
|---|---|---|
| 0001-Preserve-row-lock-semantics-during-self-join-elimina.patch | text/plain | 2.2 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | David Rowley | 2026-10-24 10:21:37 | Do we need an index on pg_constraint.confrelid? |
| Previous Message | Amit Langote | 2026-10-24 07:01:42 | Re: Re: [PATCH] Fix segmentation fault caused by reentrancy in RI_Fkey_cascade_del (ri_triggers.c) |