Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit4d29720

Browse files
committed
improve checks for DELETE FROM part_table USING part_table
1 parent64dac62 commit4d29720

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-
/* Temporarytable for JOINs */
13+
/* Temporarytables 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-
->IndexScan 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+
IndexCond: (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-
->IndexScan 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+
IndexCond: (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-
->IndexScan 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+
IndexCond: (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-
/* Temporarytable for JOINs*/
20+
/* Temporarytables for JOINs*/
2121
CREATETABLEtest.tmp (idINTEGERNOT NULL, valueINTEGERNOT NULL);
2222
INSERT INTOtest.tmpVALUES (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+
DELETEFROMtest.range_rel r USINGtest.tmp2 t
132+
WHEREt.id=r.id;
133+
134+
BEGIN;
135+
DELETEFROMtest.range_rel r USINGtest.tmp2 t
136+
WHEREt.id=r.id;
137+
ROLLBACK;
138+
139+
140+
/* DELETE + USING, partitioned table + two partitioned tables in subselect*/
141+
EXPLAIN (COSTS OFF)
142+
DELETEFROMtest.range_rel r
143+
USING (SELECT*
144+
FROMtest.tmp2 a1
145+
JOINtest.tmp2 a2
146+
USING(id)) t
147+
WHEREt.id=r.id;
148+
149+
BEGIN;
150+
DELETEFROMtest.range_rel r
151+
USING (SELECT*
152+
FROMtest.tmp2 a1
153+
JOINtest.tmp2 a2
154+
USING(id)) t
155+
WHEREt.id=r.id;
156+
ROLLBACK;
157+
158+
159+
/* DELETE + USING, single table + two partitioned tables in subselect*/
160+
EXPLAIN (COSTS OFF)
161+
DELETEFROMtest.tmp r
162+
USING (SELECT*
163+
FROMtest.tmp2 a1
164+
JOINtest.tmp2 a2
165+
USING(id)) t
166+
WHEREt.id=r.id;
167+
168+
BEGIN;
169+
DELETEFROMtest.tmp r
170+
USING (SELECT*
171+
FROMtest.tmp2 a1
172+
JOINtest.tmp2 a2
173+
USING(id)) t
174+
WHEREt.id=r.id;
175+
ROLLBACK;
176+
177+
178+
/* UPDATE + FROM, two partitioned tables*/
179+
EXPLAIN (COSTS OFF)
180+
UPDATEtest.range_rel rSET value=1FROMtest.tmp2 t
181+
WHEREt.id=r.id;
182+
183+
BEGIN;
184+
UPDATEtest.range_rel rSET value=1FROMtest.tmp2 t
185+
WHEREt.id=r.id;
186+
ROLLBACK;
187+
188+
126189
/* Test special rule for CTE; SELECT (PostgreSQL 9.5)*/
127190
EXPLAIN (COSTS OFF)
128191
WITH qAS (SELECT*FROMtest.range_rel r
@@ -167,8 +230,7 @@ WITH q AS (DELETE FROM test.tmp t
167230
DELETEFROMtest.tmp USING q;
168231
ROLLBACK;
169232

170-
/* Test special rule for CTE; DELETE + USING with partitioned table*/
171-
DELETEFROMtest.range_rel r USINGtest.tmp2 tWHEREt.id=r.id;
233+
172234

173235
DROPSCHEMA test CASCADE;
174236
DROP EXTENSION pg_pathman CASCADE;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp