Skip to content

Commit 8c441c0

Browse files
akorotkovakorotkov
committed
Forbid SJE with result relation
The target relation for INSERT/UPDATE/DELETE/MERGE has a different behavior than other relations in EvalPlanQual() and RETURNING clause. This is why we forbid target relation to be either source or target relation in SJE. It's not clear if we could ever support this. Reported-by: Alexander Lakhin Discussion: https://postgr.es/m/b9e8f460-f9a6-0e9b-e8ba-60d59f0bc22c%40gmail.com
1 parent 30b4955 commit 8c441c0

File tree

4 files changed

+84
-48
lines changed

4 files changed

+84
-48
lines changed

‎src/backend/optimizer/plan/analyzejoins.c

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2086,6 +2086,14 @@ remove_self_joins_one_group(PlannerInfo *root, Relids relids)
20862086
{
20872087
RelOptInfo *inner = root->simple_rel_array[r];
20882088

2089+
/*
2090+
* We don't accept result relation as either source or target relation
2091+
* of SJE, because result relation has different behavior in
2092+
* EvalPlanQual() and RETURNING clause.
2093+
*/
2094+
if (root->parse->resultRelation == r)
2095+
continue;
2096+
20892097
k = r;
20902098

20912099
while ((k = bms_next_member(relids, k)) > 0)
@@ -2101,6 +2109,9 @@ remove_self_joins_one_group(PlannerInfo *root, Relids relids)
21012109
PlanRowMark *imark = NULL;
21022110
List *uclauses = NIL;
21032111

2112+
if (root->parse->resultRelation == k)
2113+
continue;
2114+
21042115
/* A sanity check: the relations have the same Oid. */
21052116
Assert(root->simple_rte_array[k]->relid ==
21062117
root->simple_rte_array[r]->relid);

‎src/test/regress/expected/join.out

Lines changed: 47 additions & 29 deletions
Original file line numberDiff line numberDiff line change
@@ -6868,18 +6868,58 @@ select * from emp1 t1
68686868
-> Seq Scan on emp1 t3
68696869
(6 rows)
68706870

6871-
-- Check that SJE replaces target relation correctly
6871+
-- Check that SJE doesn't replace the target relation
68726872
explain (costs off)
68736873
WITH t1 AS (SELECT * FROM emp1)
68746874
UPDATE emp1 SET code = t1.code + 1 FROM t1
6875-
WHERE t1.id = emp1.id RETURNING emp1.id, emp1.code;
6876-
QUERY PLAN
6877-
----------------------------------
6875+
WHERE t1.id = emp1.id RETURNING emp1.id, emp1.code, t1.code;
6876+
QUERY PLAN
6877+
-------------------------------------------------------
68786878
Update on emp1
6879-
-> Seq Scan on emp1
6880-
Filter: (id IS NOT NULL)
6881-
(3 rows)
6879+
-> Nested Loop
6880+
-> Seq Scan on emp1
6881+
-> Index Scan using emp1_pkey on emp1 emp1_1
6882+
Index Cond: (id = emp1.id)
6883+
(5 rows)
6884+
6885+
INSERT INTO emp1 VALUES (1, 1), (2, 1);
6886+
WITH t1 AS (SELECT * FROM emp1)
6887+
UPDATE emp1 SET code = t1.code + 1 FROM t1
6888+
WHERE t1.id = emp1.id RETURNING emp1.id, emp1.code, t1.code;
6889+
id | code | code
6890+
----+------+------
6891+
1 | 2 | 1
6892+
2 | 2 | 1
6893+
(2 rows)
6894+
6895+
TRUNCATE emp1;
6896+
EXPLAIN (COSTS OFF)
6897+
UPDATE sj sq SET b = 1 FROM sj as sz WHERE sq.a = sz.a;
6898+
QUERY PLAN
6899+
-------------------------------------
6900+
Update on sj sq
6901+
-> Nested Loop
6902+
Join Filter: (sq.a = sz.a)
6903+
-> Seq Scan on sj sq
6904+
-> Materialize
6905+
-> Seq Scan on sj sz
6906+
(6 rows)
68826907

6908+
CREATE RULE sj_del_rule AS ON DELETE TO sj
6909+
DO INSTEAD
6910+
UPDATE sj SET a = 1 WHERE a = old.a;
6911+
EXPLAIN (COSTS OFF) DELETE FROM sj;
6912+
QUERY PLAN
6913+
--------------------------------------
6914+
Update on sj sj_1
6915+
-> Nested Loop
6916+
Join Filter: (sj.a = sj_1.a)
6917+
-> Seq Scan on sj sj_1
6918+
-> Materialize
6919+
-> Seq Scan on sj
6920+
(6 rows)
6921+
6922+
DROP RULE sj_del_rule ON sj CASCADE;
68836923
-- Check that SJE does not mistakenly omit qual clauses (bug #18187)
68846924
insert into emp1 values (1, 1);
68856925
explain (costs off)
@@ -7076,7 +7116,6 @@ ON sj_t1.id = _t2t3t4.id;
70767116
--
70777117
-- Test RowMarks-related code
70787118
--
7079-
-- TODO: Why this select returns two copies of ctid field? Should we fix it?
70807119
EXPLAIN (COSTS OFF) -- Both sides have explicit LockRows marks
70817120
SELECT a1.a FROM sj a1,sj a2 WHERE (a1.a=a2.a) FOR UPDATE;
70827121
QUERY PLAN
@@ -7086,27 +7125,6 @@ SELECT a1.a FROM sj a1,sj a2 WHERE (a1.a=a2.a) FOR UPDATE;
70867125
Filter: (a IS NOT NULL)
70877126
(3 rows)
70887127

7089-
EXPLAIN (COSTS OFF) -- A RowMark exists for the table being kept
7090-
UPDATE sj sq SET b = 1 FROM sj as sz WHERE sq.a = sz.a;
7091-
QUERY PLAN
7092-
---------------------------------
7093-
Update on sj sz
7094-
-> Seq Scan on sj sz
7095-
Filter: (a IS NOT NULL)
7096-
(3 rows)
7097-
7098-
CREATE RULE sj_del_rule AS ON DELETE TO sj
7099-
DO INSTEAD
7100-
UPDATE sj SET a = 1 WHERE a = old.a;
7101-
EXPLAIN (COSTS OFF) DELETE FROM sj; -- A RowMark exists for the table being dropped
7102-
QUERY PLAN
7103-
---------------------------------
7104-
Update on sj
7105-
-> Seq Scan on sj
7106-
Filter: (a IS NOT NULL)
7107-
(3 rows)
7108-
7109-
DROP RULE sj_del_rule ON sj CASCADE;
71107128
reset enable_hashjoin;
71117129
reset enable_mergejoin;
71127130
--

‎src/test/regress/expected/updatable_views.out

Lines changed: 10 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -2499,13 +2499,16 @@ SELECT * FROM rw_view1;
24992499
(1 row)
25002500

25012501
EXPLAIN (costs off) DELETE FROM rw_view1 WHERE id = 1 AND snoop(data);
2502-
QUERY PLAN
2503-
--------------------------------------------------
2504-
Update on base_tbl
2505-
-> Index Scan using base_tbl_pkey on base_tbl
2506-
Index Cond: (id = 1)
2507-
Filter: ((NOT deleted) AND snoop(data))
2508-
(4 rows)
2502+
QUERY PLAN
2503+
-------------------------------------------------------------------
2504+
Update on base_tbl base_tbl_1
2505+
-> Nested Loop
2506+
-> Index Scan using base_tbl_pkey on base_tbl base_tbl_1
2507+
Index Cond: (id = 1)
2508+
-> Index Scan using base_tbl_pkey on base_tbl
2509+
Index Cond: (id = 1)
2510+
Filter: ((NOT deleted) AND snoop(data))
2511+
(7 rows)
25092512

25102513
DELETE FROM rw_view1 WHERE id = 1 AND snoop(data);
25112514
NOTICE: snooped value: Row 1

‎src/test/regress/sql/join.sql

Lines changed: 16 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -2616,11 +2616,25 @@ select * from emp1 t1
26162616
inner join emp1 t2 on t1.id = t2.id
26172617
left join emp1 t3 on t1.id > 1 and t1.id < 2;
26182618

2619-
-- Check that SJE replaces target relation correctly
2619+
-- Check that SJE doesn't replace the target relation
26202620
explain (costs off)
26212621
WITH t1 AS (SELECT * FROM emp1)
26222622
UPDATE emp1 SET code = t1.code + 1 FROM t1
2623-
WHERE t1.id = emp1.id RETURNING emp1.id, emp1.code;
2623+
WHERE t1.id = emp1.id RETURNING emp1.id, emp1.code, t1.code;
2624+
INSERT INTO emp1 VALUES (1, 1), (2, 1);
2625+
WITH t1 AS (SELECT * FROM emp1)
2626+
UPDATE emp1 SET code = t1.code + 1 FROM t1
2627+
WHERE t1.id = emp1.id RETURNING emp1.id, emp1.code, t1.code;
2628+
TRUNCATE emp1;
2629+
2630+
EXPLAIN (COSTS OFF)
2631+
UPDATE sj sq SET b = 1 FROM sj as sz WHERE sq.a = sz.a;
2632+
2633+
CREATE RULE sj_del_rule AS ON DELETE TO sj
2634+
DO INSTEAD
2635+
UPDATE sj SET a = 1 WHERE a = old.a;
2636+
EXPLAIN (COSTS OFF) DELETE FROM sj;
2637+
DROP RULE sj_del_rule ON sj CASCADE;
26242638

26252639
-- Check that SJE does not mistakenly omit qual clauses (bug #18187)
26262640
insert into emp1 values (1, 1);
@@ -2729,19 +2743,9 @@ ON sj_t1.id = _t2t3t4.id;
27292743
-- Test RowMarks-related code
27302744
--
27312745

2732-
-- TODO: Why this select returns two copies of ctid field? Should we fix it?
27332746
EXPLAIN (COSTS OFF) -- Both sides have explicit LockRows marks
27342747
SELECT a1.a FROM sj a1,sj a2 WHERE (a1.a=a2.a) FOR UPDATE;
27352748

2736-
EXPLAIN (COSTS OFF) -- A RowMark exists for the table being kept
2737-
UPDATE sj sq SET b = 1 FROM sj as sz WHERE sq.a = sz.a;
2738-
2739-
CREATE RULE sj_del_rule AS ON DELETE TO sj
2740-
DO INSTEAD
2741-
UPDATE sj SET a = 1 WHERE a = old.a;
2742-
EXPLAIN (COSTS OFF) DELETE FROM sj; -- A RowMark exists for the table being dropped
2743-
DROP RULE sj_del_rule ON sj CASCADE;
2744-
27452749
reset enable_hashjoin;
27462750
reset enable_mergejoin;
27472751

0 commit comments

Comments
 (0)