Skip to content

Commit 4d29720

Browse files
funbringerfunbringer
committed
improve checks for DELETE FROM part_table USING part_table
1 parent 64dac62 commit 4d29720

File tree

3 files changed

+242
-75
lines changed

3 files changed

+242
-75
lines changed

‎expected/pathman_upd_del.out

Lines changed: 142 additions & 55 deletions
Original file line numberDiff line numberDiff line change
@@ -10,7 +10,7 @@ CREATE EXTENSION pg_pathman SCHEMA pathman;
1010
CREATE SCHEMA test;
1111
SET enable_indexscan = ON;
1212
SET enable_seqscan = OFF;
13-
/* Temporary table for JOINs */
13+
/* Temporary tables for JOINs */
1414
CREATE TABLE test.tmp (id INTEGER NOT NULL, value INTEGER NOT NULL);
1515
INSERT INTO test.tmp VALUES (1, 1), (2, 2);
1616
CREATE TABLE test.tmp2 (id INTEGER NOT NULL, value INTEGER NOT NULL);
@@ -35,6 +35,7 @@ SELECT pathman.create_range_partitions('test.range_rel', 'dt',
3535
12
3636
(1 row)
3737

38+
VACUUM ANALYZE;
3839
/*
3940
* Test UPDATE and DELETE
4041
*/
@@ -111,16 +112,15 @@ ROLLBACK;
111112
EXPLAIN (COSTS OFF)
112113
UPDATE test.range_rel r SET value = t.value
113114
FROM test.tmp t WHERE r.dt = '2010-01-01' AND r.id = t.id;
114-
QUERY PLAN
115-
--------------------------------------------------------------------------------------------
115+
QUERY PLAN
116+
--------------------------------------------------------------------------------------
116117
Update on range_rel_1 r
117-
-> Hash Join
118-
Hash Cond: (t.id = r.id)
118+
-> Nested Loop
119+
Join Filter: (r.id = t.id)
120+
-> Index Scan using range_rel_1_pkey on range_rel_1 r
121+
Filter: (dt = 'Fri Jan 01 00:00:00 2010'::timestamp without time zone)
119122
-> Seq Scan on tmp t
120-
-> Hash
121-
-> Index Scan using range_rel_1_pkey on range_rel_1 r
122-
Filter: (dt = 'Fri Jan 01 00:00:00 2010'::timestamp without time zone)
123-
(7 rows)
123+
(6 rows)
124124

125125
BEGIN;
126126
UPDATE test.range_rel r SET value = t.value
@@ -130,17 +130,16 @@ ROLLBACK;
130130
EXPLAIN (COSTS OFF)
131131
UPDATE test.tmp t SET value = r.value
132132
FROM test.range_rel r WHERE r.dt = '2010-01-01' AND r.id = t.id;
133-
QUERY PLAN
134-
--------------------------------------------------------------------------------------------------
133+
QUERY PLAN
134+
--------------------------------------------------------------------------------------------
135135
Update on tmp t
136-
-> Hash Join
137-
Hash Cond: (t.id = r.id)
136+
-> Nested Loop
138137
-> Seq Scan on tmp t
139-
-> Hash
140-
-> Append
141-
-> Index Scan using range_rel_1_pkey on range_rel_1 r
142-
Filter: (dt = 'Fri Jan 01 00:00:00 2010'::timestamp without time zone)
143-
(8 rows)
138+
-> Append
139+
-> Index Scan using range_rel_1_pkey on range_rel_1 r
140+
Index Cond: (id = t.id)
141+
Filter: (dt = 'Fri Jan 01 00:00:00 2010'::timestamp without time zone)
142+
(7 rows)
144143

145144
BEGIN;
146145
UPDATE test.tmp t SET value = r.value
@@ -150,16 +149,15 @@ ROLLBACK;
150149
EXPLAIN (COSTS OFF)
151150
DELETE FROM test.range_rel r USING test.tmp t
152151
WHERE r.dt = '2010-01-02' AND r.id = t.id;
153-
QUERY PLAN
154-
--------------------------------------------------------------------------------------------
152+
QUERY PLAN
153+
--------------------------------------------------------------------------------------
155154
Delete on range_rel_1 r
156-
-> Hash Join
157-
Hash Cond: (t.id = r.id)
155+
-> Nested Loop
156+
Join Filter: (r.id = t.id)
157+
-> Index Scan using range_rel_1_pkey on range_rel_1 r
158+
Filter: (dt = 'Sat Jan 02 00:00:00 2010'::timestamp without time zone)
158159
-> Seq Scan on tmp t
159-
-> Hash
160-
-> Index Scan using range_rel_1_pkey on range_rel_1 r
161-
Filter: (dt = 'Sat Jan 02 00:00:00 2010'::timestamp without time zone)
162-
(7 rows)
160+
(6 rows)
163161

164162
BEGIN;
165163
DELETE FROM test.range_rel r USING test.tmp t
@@ -169,22 +167,118 @@ ROLLBACK;
169167
EXPLAIN (COSTS OFF)
170168
DELETE FROM test.tmp t USING test.range_rel r
171169
WHERE r.dt = '2010-01-02' AND r.id = t.id;
172-
QUERY PLAN
173-
--------------------------------------------------------------------------------------------------
170+
QUERY PLAN
171+
--------------------------------------------------------------------------------------------
174172
Delete on tmp t
175-
-> Hash Join
176-
Hash Cond: (t.id = r.id)
173+
-> Nested Loop
177174
-> Seq Scan on tmp t
178-
-> Hash
179-
-> Append
180-
-> Index Scan using range_rel_1_pkey on range_rel_1 r
181-
Filter: (dt = 'Sat Jan 02 00:00:00 2010'::timestamp without time zone)
182-
(8 rows)
175+
-> Append
176+
-> Index Scan using range_rel_1_pkey on range_rel_1 r
177+
Index Cond: (id = t.id)
178+
Filter: (dt = 'Sat Jan 02 00:00:00 2010'::timestamp without time zone)
179+
(7 rows)
183180

184181
BEGIN;
185182
DELETE FROM test.tmp t USING test.range_rel r
186183
WHERE r.dt = '2010-01-02' AND r.id = t.id;
187184
ROLLBACK;
185+
/* DELETE + USING, two partitioned tables */
186+
EXPLAIN (COSTS OFF)
187+
DELETE FROM test.range_rel r USING test.tmp2 t
188+
WHERE t.id = r.id;
189+
ERROR: DELETE and UPDATE queries with a join of partitioned tables are not supported
190+
BEGIN;
191+
DELETE FROM test.range_rel r USING test.tmp2 t
192+
WHERE t.id = r.id;
193+
ERROR: DELETE and UPDATE queries with a join of partitioned tables are not supported
194+
ROLLBACK;
195+
/* DELETE + USING, partitioned table + two partitioned tables in subselect */
196+
EXPLAIN (COSTS OFF)
197+
DELETE FROM test.range_rel r
198+
USING (SELECT *
199+
FROM test.tmp2 a1
200+
JOIN test.tmp2 a2
201+
USING(id)) t
202+
WHERE t.id = r.id;
203+
ERROR: DELETE and UPDATE queries with a join of partitioned tables are not supported
204+
BEGIN;
205+
DELETE FROM test.range_rel r
206+
USING (SELECT *
207+
FROM test.tmp2 a1
208+
JOIN test.tmp2 a2
209+
USING(id)) t
210+
WHERE t.id = r.id;
211+
ERROR: DELETE and UPDATE queries with a join of partitioned tables are not supported
212+
ROLLBACK;
213+
/* DELETE + USING, single table + two partitioned tables in subselect */
214+
EXPLAIN (COSTS OFF)
215+
DELETE FROM test.tmp r
216+
USING (SELECT *
217+
FROM test.tmp2 a1
218+
JOIN test.tmp2 a2
219+
USING(id)) t
220+
WHERE t.id = r.id;
221+
QUERY PLAN
222+
------------------------------------------------------
223+
Delete on tmp r
224+
-> Nested Loop
225+
Join Filter: (a1.id = a2.id)
226+
-> Append
227+
-> Seq Scan on tmp2_1 a2
228+
-> Seq Scan on tmp2_2 a2_1
229+
-> Seq Scan on tmp2_3 a2_2
230+
-> Seq Scan on tmp2_4 a2_3
231+
-> Seq Scan on tmp2_5 a2_4
232+
-> Seq Scan on tmp2_6 a2_5
233+
-> Seq Scan on tmp2_7 a2_6
234+
-> Seq Scan on tmp2_8 a2_7
235+
-> Seq Scan on tmp2_9 a2_8
236+
-> Seq Scan on tmp2_10 a2_9
237+
-> Materialize
238+
-> Nested Loop
239+
-> Seq Scan on tmp r
240+
-> Custom Scan (RuntimeAppend)
241+
Prune by: (r.id = a1.id)
242+
-> Seq Scan on tmp2_1 a1
243+
Filter: (r.id = id)
244+
-> Seq Scan on tmp2_2 a1
245+
Filter: (r.id = id)
246+
-> Seq Scan on tmp2_3 a1
247+
Filter: (r.id = id)
248+
-> Seq Scan on tmp2_4 a1
249+
Filter: (r.id = id)
250+
-> Seq Scan on tmp2_5 a1
251+
Filter: (r.id = id)
252+
-> Seq Scan on tmp2_6 a1
253+
Filter: (r.id = id)
254+
-> Seq Scan on tmp2_7 a1
255+
Filter: (r.id = id)
256+
-> Seq Scan on tmp2_8 a1
257+
Filter: (r.id = id)
258+
-> Seq Scan on tmp2_9 a1
259+
Filter: (r.id = id)
260+
-> Seq Scan on tmp2_10 a1
261+
Filter: (r.id = id)
262+
(39 rows)
263+
264+
BEGIN;
265+
DELETE FROM test.tmp r
266+
USING (SELECT *
267+
FROM test.tmp2 a1
268+
JOIN test.tmp2 a2
269+
USING(id)) t
270+
WHERE t.id = r.id;
271+
ROLLBACK;
272+
/* UPDATE + FROM, two partitioned tables */
273+
EXPLAIN (COSTS OFF)
274+
UPDATE test.range_rel r SET value = 1 FROM test.tmp2 t
275+
WHERE t.id = r.id;
276+
ERROR: DELETE and UPDATE queries with a join of partitioned tables are not supported
277+
BEGIN;
278+
UPDATE test.range_rel r SET value = 1 FROM test.tmp2 t
279+
WHERE t.id = r.id;
280+
ERROR: DELETE and UPDATE queries with a join of partitioned tables are not supported
281+
ROLLBACK;
188282
/* Test special rule for CTE; SELECT (PostgreSQL 9.5) */
189283
EXPLAIN (COSTS OFF)
190284
WITH q AS (SELECT * FROM test.range_rel r
@@ -198,10 +292,9 @@ DELETE FROM test.tmp USING q;
198292
-> Seq Scan on range_rel_1 r
199293
Filter: (dt = 'Sat Jan 02 00:00:00 2010'::timestamp without time zone)
200294
-> Nested Loop
295+
-> Seq Scan on tmp
201296
-> CTE Scan on q
202-
-> Materialize
203-
-> Seq Scan on tmp
204-
(9 rows)
297+
(8 rows)
205298

206299
BEGIN;
207300
WITH q AS (SELECT * FROM test.range_rel r
@@ -222,10 +315,9 @@ DELETE FROM test.tmp USING q;
222315
-> Seq Scan on range_rel_1 r
223316
Filter: (dt = 'Sat Jan 02 00:00:00 2010'::timestamp without time zone)
224317
-> Nested Loop
318+
-> Seq Scan on tmp
225319
-> CTE Scan on q
226-
-> Materialize
227-
-> Seq Scan on tmp
228-
(9 rows)
320+
(8 rows)
229321

230322
BEGIN;
231323
WITH q AS (DELETE FROM test.range_rel r
@@ -240,23 +332,21 @@ WITH q AS (DELETE FROM test.tmp t
240332
WHERE r.dt = '2010-01-02' AND r.id = t.id
241333
RETURNING *)
242334
DELETE FROM test.tmp USING q;
243-
QUERY PLAN
244-
----------------------------------------------------------------------------------------------------------
335+
QUERY PLAN
336+
----------------------------------------------------------------------------------------------------
245337
Delete on tmp
246338
CTE q
247339
-> Delete on tmp t
248-
-> Hash Join
249-
Hash Cond: (t.id = r.id)
340+
-> Nested Loop
250341
-> Seq Scan on tmp t
251-
-> Hash
252-
-> Append
253-
-> Index Scan using range_rel_1_pkey on range_rel_1 r
254-
Filter: (dt = 'Sat Jan 02 00:00:00 2010'::timestamp without time zone)
342+
-> Append
343+
-> Index Scan using range_rel_1_pkey on range_rel_1 r
344+
Index Cond: (id = t.id)
345+
Filter: (dt = 'Sat Jan 02 00:00:00 2010'::timestamp without time zone)
255346
-> Nested Loop
347+
-> Seq Scan on tmp
256348
-> CTE Scan on q
257-
-> Materialize
258-
-> Seq Scan on tmp
259-
(14 rows)
349+
(12 rows)
260350

261351
BEGIN;
262352
WITH q AS (DELETE FROM test.tmp t
@@ -265,9 +355,6 @@ WITH q AS (DELETE FROM test.tmp t
265355
RETURNING *)
266356
DELETE FROM test.tmp USING q;
267357
ROLLBACK;
268-
/* Test special rule for CTE; DELETE + USING with partitioned table */
269-
DELETE FROM test.range_rel r USING test.tmp2 t WHERE t.id = r.id;
270-
ERROR: pg_pathman doesn't support DELETE queries with joining of partitioned tables
271358
DROP SCHEMA test CASCADE;
272359
NOTICE: drop cascades to 27 other objects
273360
DROP EXTENSION pg_pathman CASCADE;

‎sql/pathman_upd_del.sql

Lines changed: 65 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -17,7 +17,7 @@ SET enable_indexscan = ON;
1717
SET enable_seqscan = OFF;
1818

1919

20-
/* Temporary table for JOINs */
20+
/* Temporary tables for JOINs */
2121
CREATE TABLE test.tmp (id INTEGER NOT NULL, value INTEGER NOT NULL);
2222
INSERT INTO test.tmp VALUES (1, 1), (2, 2);
2323

@@ -39,6 +39,9 @@ SELECT pathman.create_range_partitions('test.range_rel', 'dt',
3939
12);
4040

4141

42+
VACUUM ANALYZE;
43+
44+
4245
/*
4346
* Test UPDATE and DELETE
4447
*/
@@ -123,6 +126,66 @@ WHERE r.dt = '2010-01-02' AND r.id = t.id;
123126
ROLLBACK;
124127

125128

129+
/* DELETE + USING, two partitioned tables */
130+
EXPLAIN (COSTS OFF)
131+
DELETE FROM test.range_rel r USING test.tmp2 t
132+
WHERE t.id = r.id;
133+
134+
BEGIN;
135+
DELETE FROM test.range_rel r USING test.tmp2 t
136+
WHERE t.id = r.id;
137+
ROLLBACK;
138+
139+
140+
/* DELETE + USING, partitioned table + two partitioned tables in subselect */
141+
EXPLAIN (COSTS OFF)
142+
DELETE FROM test.range_rel r
143+
USING (SELECT *
144+
FROM test.tmp2 a1
145+
JOIN test.tmp2 a2
146+
USING(id)) t
147+
WHERE t.id = r.id;
148+
149+
BEGIN;
150+
DELETE FROM test.range_rel r
151+
USING (SELECT *
152+
FROM test.tmp2 a1
153+
JOIN test.tmp2 a2
154+
USING(id)) t
155+
WHERE t.id = r.id;
156+
ROLLBACK;
157+
158+
159+
/* DELETE + USING, single table + two partitioned tables in subselect */
160+
EXPLAIN (COSTS OFF)
161+
DELETE FROM test.tmp r
162+
USING (SELECT *
163+
FROM test.tmp2 a1
164+
JOIN test.tmp2 a2
165+
USING(id)) t
166+
WHERE t.id = r.id;
167+
168+
BEGIN;
169+
DELETE FROM test.tmp r
170+
USING (SELECT *
171+
FROM test.tmp2 a1
172+
JOIN test.tmp2 a2
173+
USING(id)) t
174+
WHERE t.id = r.id;
175+
ROLLBACK;
176+
177+
178+
/* UPDATE + FROM, two partitioned tables */
179+
EXPLAIN (COSTS OFF)
180+
UPDATE test.range_rel r SET value = 1 FROM test.tmp2 t
181+
WHERE t.id = r.id;
182+
183+
BEGIN;
184+
UPDATE test.range_rel r SET value = 1 FROM test.tmp2 t
185+
WHERE t.id = r.id;
186+
ROLLBACK;
187+
188+
126189
/* Test special rule for CTE; SELECT (PostgreSQL 9.5) */
127190
EXPLAIN (COSTS OFF)
128191
WITH q AS (SELECT * FROM test.range_rel r
@@ -167,8 +230,7 @@ WITH q AS (DELETE FROM test.tmp t
167230
DELETE FROM test.tmp USING q;
168231
ROLLBACK;
169232

170-
/* Test special rule for CTE; DELETE + USING with partitioned table */
171-
DELETE FROM test.range_rel r USING test.tmp2 t WHERE t.id = r.id;
233+
172234

173235
DROP SCHEMA test CASCADE;
174236
DROP EXTENSION pg_pathman CASCADE;

0 commit comments

Comments
 (0)