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

Commitd75175a

Browse files
committed
more tests for pathman_rel_pathlist_hook()
1 parent6fb5b84 commitd75175a

File tree

4 files changed

+152
-39
lines changed

4 files changed

+152
-39
lines changed

‎expected/pathman_upd_del.out

Lines changed: 88 additions & 39 deletions
Original file line numberDiff line numberDiff line change
@@ -14,6 +14,7 @@ SET enable_seqscan = OFF;
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);
17+
INSERT INTO test.tmp2 SELECT i % 10 + 1, i FROM generate_series(1, 100) i;
1718
SELECT pathman.create_range_partitions('test.tmp2', 'id', 1, 1, 10);
1819
create_range_partitions
1920
-------------------------
@@ -218,48 +219,57 @@ USING (SELECT *
218219
JOIN test.tmp2 a2
219220
USING(id)) t
220221
WHERE t.id = r.id;
221-
QUERY PLAN
222-
------------------------------------------------------
222+
QUERY PLAN
223+
------------------------------------------------
223224
Delete on tmp r
224225
-> Nested Loop
225-
Join Filter: (a1.id = a2.id)
226-
-> Append
226+
-> Nested Loop
227+
-> Seq Scan on tmp r
228+
-> Custom Scan (RuntimeAppend)
229+
Prune by: (r.id = a1.id)
230+
-> Seq Scan on tmp2_1 a1
231+
Filter: (r.id = id)
232+
-> Seq Scan on tmp2_2 a1
233+
Filter: (r.id = id)
234+
-> Seq Scan on tmp2_3 a1
235+
Filter: (r.id = id)
236+
-> Seq Scan on tmp2_4 a1
237+
Filter: (r.id = id)
238+
-> Seq Scan on tmp2_5 a1
239+
Filter: (r.id = id)
240+
-> Seq Scan on tmp2_6 a1
241+
Filter: (r.id = id)
242+
-> Seq Scan on tmp2_7 a1
243+
Filter: (r.id = id)
244+
-> Seq Scan on tmp2_8 a1
245+
Filter: (r.id = id)
246+
-> Seq Scan on tmp2_9 a1
247+
Filter: (r.id = id)
248+
-> Seq Scan on tmp2_10 a1
249+
Filter: (r.id = id)
250+
-> Custom Scan (RuntimeAppend)
251+
Prune by: (a1.id = a2.id)
227252
-> 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)
253+
Filter: (a1.id = id)
254+
-> Seq Scan on tmp2_2 a2
255+
Filter: (a1.id = id)
256+
-> Seq Scan on tmp2_3 a2
257+
Filter: (a1.id = id)
258+
-> Seq Scan on tmp2_4 a2
259+
Filter: (a1.id = id)
260+
-> Seq Scan on tmp2_5 a2
261+
Filter: (a1.id = id)
262+
-> Seq Scan on tmp2_6 a2
263+
Filter: (a1.id = id)
264+
-> Seq Scan on tmp2_7 a2
265+
Filter: (a1.id = id)
266+
-> Seq Scan on tmp2_8 a2
267+
Filter: (a1.id = id)
268+
-> Seq Scan on tmp2_9 a2
269+
Filter: (a1.id = id)
270+
-> Seq Scan on tmp2_10 a2
271+
Filter: (a1.id = id)
272+
(48 rows)
263273

264274
BEGIN;
265275
DELETE FROM test.tmp r
@@ -279,6 +289,45 @@ UPDATE test.range_rel r SET value = 1 FROM test.tmp2 t
279289
WHERE t.id = r.id;
280290
ERROR: DELETE and UPDATE queries with a join of partitioned tables are not supported
281291
ROLLBACK;
292+
/*
293+
* UPDATE + subquery with partitioned table (PG 9.5).
294+
* See pathman_rel_pathlist_hook() + RELOPT_OTHER_MEMBER_REL.
295+
*/
296+
EXPLAIN (COSTS OFF)
297+
UPDATE test.tmp t SET value = 2
298+
WHERE t.id IN (SELECT id
299+
FROM test.tmp2 t2
300+
WHERE id = t.id);
301+
QUERY PLAN
302+
--------------------------------------------
303+
Update on tmp t
304+
-> Seq Scan on tmp t
305+
Filter: (SubPlan 1)
306+
SubPlan 1
307+
-> Custom Scan (RuntimeAppend)
308+
Prune by: (t2.id = t.id)
309+
-> Seq Scan on tmp2_1 t2
310+
Filter: (id = t.id)
311+
-> Seq Scan on tmp2_2 t2
312+
Filter: (id = t.id)
313+
-> Seq Scan on tmp2_3 t2
314+
Filter: (id = t.id)
315+
-> Seq Scan on tmp2_4 t2
316+
Filter: (id = t.id)
317+
-> Seq Scan on tmp2_5 t2
318+
Filter: (id = t.id)
319+
-> Seq Scan on tmp2_6 t2
320+
Filter: (id = t.id)
321+
-> Seq Scan on tmp2_7 t2
322+
Filter: (id = t.id)
323+
-> Seq Scan on tmp2_8 t2
324+
Filter: (id = t.id)
325+
-> Seq Scan on tmp2_9 t2
326+
Filter: (id = t.id)
327+
-> Seq Scan on tmp2_10 t2
328+
Filter: (id = t.id)
329+
(26 rows)
330+
282331
/* Test special rule for CTE; SELECT (PostgreSQL 9.5) */
283332
EXPLAIN (COSTS OFF)
284333
WITH q AS (SELECT * FROM test.range_rel r

‎expected/pathman_upd_del_1.out

Lines changed: 41 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -14,6 +14,7 @@ SET enable_seqscan = OFF;
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);
17+
INSERT INTO test.tmp2 SELECT i % 10 + 1, i FROM generate_series(1, 100) i;
1718
SELECT pathman.create_range_partitions('test.tmp2', 'id', 1, 1, 10);
1819
create_range_partitions
1920
-------------------------
@@ -264,6 +265,46 @@ UPDATE test.range_rel r SET value = 1 FROM test.tmp2 t
264265
WHERE t.id = r.id;
265266
ERROR: DELETE and UPDATE queries with a join of partitioned tables are not supported
266267
ROLLBACK;
268+
/*
269+
* UPDATE + subquery with partitioned table (PG 9.5).
270+
* See pathman_rel_pathlist_hook() + RELOPT_OTHER_MEMBER_REL.
271+
*/
272+
EXPLAIN (COSTS OFF)
273+
UPDATE test.tmp t SET value = 2
274+
WHERE t.id IN (SELECT id
275+
FROM test.tmp2 t2
276+
WHERE id = t.id);
277+
QUERY PLAN
278+
-----------------------------------------------
279+
Update on tmp t
280+
-> Seq Scan on tmp t
281+
Filter: (SubPlan 1)
282+
SubPlan 1
283+
-> Append
284+
-> Seq Scan on tmp2 t2
285+
Filter: (id = t.id)
286+
-> Seq Scan on tmp2_1 t2_1
287+
Filter: (id = t.id)
288+
-> Seq Scan on tmp2_2 t2_2
289+
Filter: (id = t.id)
290+
-> Seq Scan on tmp2_3 t2_3
291+
Filter: (id = t.id)
292+
-> Seq Scan on tmp2_4 t2_4
293+
Filter: (id = t.id)
294+
-> Seq Scan on tmp2_5 t2_5
295+
Filter: (id = t.id)
296+
-> Seq Scan on tmp2_6 t2_6
297+
Filter: (id = t.id)
298+
-> Seq Scan on tmp2_7 t2_7
299+
Filter: (id = t.id)
300+
-> Seq Scan on tmp2_8 t2_8
301+
Filter: (id = t.id)
302+
-> Seq Scan on tmp2_9 t2_9
303+
Filter: (id = t.id)
304+
-> Seq Scan on tmp2_10 t2_10
305+
Filter: (id = t.id)
306+
(27 rows)
307+
267308
/* Test special rule for CTE; SELECT (PostgreSQL 9.5) */
268309
EXPLAIN (COSTS OFF)
269310
WITH q AS (SELECT * FROM test.range_rel r

‎sql/pathman_upd_del.sql

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -22,6 +22,7 @@ CREATE TABLE test.tmp (id INTEGER NOT NULL, value INTEGER NOT NULL);
2222
INSERT INTOtest.tmpVALUES (1,1), (2,2);
2323

2424
CREATETABLEtest.tmp2 (idINTEGERNOT NULL, valueINTEGERNOT NULL);
25+
INSERT INTOtest.tmp2SELECT i %10+1, iFROM generate_series(1,100) i;
2526
SELECTpathman.create_range_partitions('test.tmp2','id',1,1,10);
2627

2728

@@ -186,6 +187,17 @@ WHERE t.id = r.id;
186187
ROLLBACK;
187188

188189

190+
/*
191+
* UPDATE + subquery with partitioned table (PG 9.5).
192+
* See pathman_rel_pathlist_hook() + RELOPT_OTHER_MEMBER_REL.
193+
*/
194+
EXPLAIN (COSTS OFF)
195+
UPDATEtest.tmp tSET value=2
196+
WHEREt.idIN (SELECT id
197+
FROMtest.tmp2 t2
198+
WHERE id=t.id);
199+
200+
189201
/* Test special rule for CTE; SELECT (PostgreSQL 9.5)*/
190202
EXPLAIN (COSTS OFF)
191203
WITH qAS (SELECT*FROMtest.range_rel r

‎src/hooks.c

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -358,6 +358,17 @@ pathman_rel_pathlist_hook(PlannerInfo *root,
358358
/*
359359
* Check that this child is not the parent table itself.
360360
* This is exactly how standard inheritance works.
361+
*
362+
* Helps with queries like this one:
363+
*
364+
*UPDATE test.tmp t SET value = 2
365+
*WHERE t.id IN (SELECT id
366+
* FROM test.tmp2 t2
367+
* WHERE id = t.id);
368+
*
369+
* Since we disable optimizations on 9.5, we
370+
* have to skip parent table that has already
371+
* been expanded by standard inheritance.
361372
*/
362373
if (rel->reloptkind==RELOPT_OTHER_MEMBER_REL)
363374
{

0 commit comments

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