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

Commitfcfd134

Browse files
committed
improve update/delete on a missing key (mentioned in issue#104)
1 parente98dd7b commitfcfd134

File tree

3 files changed

+56
-9
lines changed

3 files changed

+56
-9
lines changed

‎expected/pathman_basic.out

Lines changed: 30 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1657,7 +1657,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt > '2010-12-15';
16571657
CREATE TABLE test.tmp (id INTEGER NOT NULL, value INTEGER NOT NULL);
16581658
INSERT INTO test.tmp VALUES (1, 1), (2, 2);
16591659
/* Test UPDATE and DELETE */
1660-
EXPLAIN (COSTS OFF) UPDATE test.range_rel SET value = 111 WHERE dt = '2010-06-15';
1660+
EXPLAIN (COSTS OFF) UPDATE test.range_rel SET value = 111 WHERE dt = '2010-06-15';/* have partitions for this 'dt' */
16611661
QUERY PLAN
16621662
--------------------------------------------------------------------------------
16631663
Update on range_rel_6
@@ -1672,7 +1672,7 @@ SELECT * FROM test.range_rel WHERE dt = '2010-06-15';
16721672
166 | Tue Jun 15 00:00:00 2010 | 111
16731673
(1 row)
16741674

1675-
EXPLAIN (COSTS OFF) DELETE FROM test.range_rel WHERE dt = '2010-06-15';
1675+
EXPLAIN (COSTS OFF) DELETE FROM test.range_rel WHERE dt = '2010-06-15';/* have partitions for this 'dt' */
16761676
QUERY PLAN
16771677
--------------------------------------------------------------------------------
16781678
Delete on range_rel_6
@@ -1686,6 +1686,34 @@ SELECT * FROM test.range_rel WHERE dt = '2010-06-15';
16861686
----+----+-------
16871687
(0 rows)
16881688

1689+
EXPLAIN (COSTS OFF) UPDATE test.range_rel SET value = 222 WHERE dt = '1990-01-01';/* no partitions for this 'dt' */
1690+
QUERY PLAN
1691+
--------------------------------------------------------------------------------
1692+
Update on range_rel
1693+
-> Seq Scan on range_rel
1694+
Filter: (dt = 'Mon Jan 01 00:00:00 1990'::timestamp without time zone)
1695+
(3 rows)
1696+
1697+
UPDATE test.range_rel SET value = 111 WHERE dt = '1990-01-01';
1698+
SELECT * FROM test.range_rel WHERE dt = '1990-01-01';
1699+
id | dt | value
1700+
----+----+-------
1701+
(0 rows)
1702+
1703+
EXPLAIN (COSTS OFF) DELETE FROM test.range_rel WHERE dt < '1990-01-01';/* no partitions for this 'dt' */
1704+
QUERY PLAN
1705+
--------------------------------------------------------------------------------
1706+
Delete on range_rel
1707+
-> Seq Scan on range_rel
1708+
Filter: (dt < 'Mon Jan 01 00:00:00 1990'::timestamp without time zone)
1709+
(3 rows)
1710+
1711+
DELETE FROM test.range_rel WHERE dt < '1990-01-01';
1712+
SELECT * FROM test.range_rel WHERE dt < '1990-01-01';
1713+
id | dt | value
1714+
----+----+-------
1715+
(0 rows)
1716+
16891717
EXPLAIN (COSTS OFF) UPDATE test.range_rel r SET value = t.value FROM test.tmp t WHERE r.dt = '2010-01-01' AND r.id = t.id;
16901718
QUERY PLAN
16911719
--------------------------------------------------------------------------------------------

‎sql/pathman_basic.sql

Lines changed: 15 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -503,18 +503,31 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt > '2010-12-15';
503503
CREATETABLEtest.tmp (idINTEGERNOT NULL, valueINTEGERNOT NULL);
504504
INSERT INTOtest.tmpVALUES (1,1), (2,2);
505505

506+
506507
/* Test UPDATE and DELETE*/
507-
EXPLAIN (COSTS OFF)UPDATEtest.range_relSET value=111WHERE dt='2010-06-15';
508+
EXPLAIN (COSTS OFF)UPDATEtest.range_relSET value=111WHERE dt='2010-06-15';/* have partitions for this 'dt'*/
508509
UPDATEtest.range_relSET value=111WHERE dt='2010-06-15';
509510
SELECT*FROMtest.range_relWHERE dt='2010-06-15';
510-
EXPLAIN (COSTS OFF)DELETEFROMtest.range_relWHERE dt='2010-06-15';
511+
512+
EXPLAIN (COSTS OFF)DELETEFROMtest.range_relWHERE dt='2010-06-15';/* have partitions for this 'dt'*/
511513
DELETEFROMtest.range_relWHERE dt='2010-06-15';
512514
SELECT*FROMtest.range_relWHERE dt='2010-06-15';
515+
516+
EXPLAIN (COSTS OFF)UPDATEtest.range_relSET value=222WHERE dt='1990-01-01';/* no partitions for this 'dt'*/
517+
UPDATEtest.range_relSET value=111WHERE dt='1990-01-01';
518+
SELECT*FROMtest.range_relWHERE dt='1990-01-01';
519+
520+
EXPLAIN (COSTS OFF)DELETEFROMtest.range_relWHERE dt<'1990-01-01';/* no partitions for this 'dt'*/
521+
DELETEFROMtest.range_relWHERE dt<'1990-01-01';
522+
SELECT*FROMtest.range_relWHERE dt<'1990-01-01';
523+
513524
EXPLAIN (COSTS OFF)UPDATEtest.range_rel rSET value=t.valueFROMtest.tmp tWHEREr.dt='2010-01-01'ANDr.id=t.id;
514525
UPDATEtest.range_rel rSET value=t.valueFROMtest.tmp tWHEREr.dt='2010-01-01'ANDr.id=t.id;
526+
515527
EXPLAIN (COSTS OFF)DELETEFROMtest.range_rel r USINGtest.tmp tWHEREr.dt='2010-01-02'ANDr.id=t.id;
516528
DELETEFROMtest.range_rel r USINGtest.tmp tWHEREr.dt='2010-01-02'ANDr.id=t.id;
517529

530+
518531
/* Create range partitions from whole range*/
519532
SELECT drop_partitions('test.range_rel');
520533

‎src/planner_tree_modification.c

Lines changed: 11 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -249,6 +249,7 @@ handle_modification_query(Query *parse, ParamListInfo params)
249249
Expr*expr;
250250
WalkerContextcontext;
251251
Indexresult_rel;
252+
intnum_selected;
252253

253254
/* Fetch index of result relation */
254255
result_rel=parse->resultRelation;
@@ -291,12 +292,10 @@ handle_modification_query(Query *parse, ParamListInfo params)
291292
wrap=walk_expr_tree(expr,&context);
292293

293294
ranges=irange_list_intersection(ranges,wrap->rangeset);
295+
num_selected=irange_list_length(ranges);
294296

295-
/*
296-
* If only one partition is affected,
297-
* substitute parent table with the partition.
298-
*/
299-
if (irange_list_length(ranges)==1)
297+
/* Special case #1: only one partition is affected */
298+
if (num_selected==1)
300299
{
301300
IndexRangeirange=linitial_irange(ranges);
302301

@@ -361,6 +360,13 @@ handle_modification_query(Query *parse, ParamListInfo params)
361360
rte->inh= false;
362361
}
363362
}
363+
364+
/* Special case #2: no partitions are affected */
365+
elseif (num_selected==0)
366+
{
367+
/* HACK: unset the 'inh' flag (no children) */
368+
rte->inh= false;
369+
}
364370
}
365371

366372

0 commit comments

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