pathman: regression tests for UPDATE and DELETE

zilder · zilder · commit d941ff43d053 · 2016-03-14T14:35:35.000+03:00
diff --git a/contrib/pg_pathman/expected/pg_pathman.out b/contrib/pg_pathman/expected/pg_pathman.out
@@ -753,8 +753,9 @@ EXPLAIN (COSTS OFF) SELECT * FROM hash_rel WHERE id = 1234;
 /* Range */
 CREATE TABLE range_rel (
     id SERIAL PRIMARY KEY,
-    dt TIMESTAMP NOT NULL);
-INSERT INTO range_rel (dt) SELECT g FROM generate_series('2010-01-01'::date, '2010-12-31'::date, '1 day') as g;
+    dt TIMESTAMP NOT NULL,
+    value INTEGER);
+INSERT INTO range_rel (dt, value) SELECT g, extract(day from g) FROM generate_series('2010-01-01'::date, '2010-12-31'::date, '1 day') as g;
 SELECT create_range_partitions('range_rel', 'dt', '2010-01-01'::date, '1 month'::interval, 12);
 NOTICE:  sequence "range_rel_seq" does not exist, skipping
 NOTICE:  Copying data to partitions...
@@ -817,6 +818,36 @@ EXPLAIN (COSTS OFF) SELECT * FROM range_rel WHERE dt > '2010-12-15';
    ->  Seq Scan on range_rel_14
 (4 rows)
 
+/* Test UPDATE and DELETE */
+EXPLAIN (COSTS OFF) UPDATE range_rel SET value = 111 WHERE dt = '2010-06-15';
+                                   QUERY PLAN                                   
+--------------------------------------------------------------------------------
+ Update on range_rel_6
+   ->  Seq Scan on range_rel_6
+         Filter: (dt = 'Tue Jun 15 00:00:00 2010'::timestamp without time zone)
+(3 rows)
+
+UPDATE range_rel SET value = 111 WHERE dt = '2010-06-15';
+SELECT * FROM range_rel WHERE dt = '2010-06-15';
+ id  |            dt            | value 
+-----+--------------------------+-------
+ 166 | Tue Jun 15 00:00:00 2010 |   111
+(1 row)
+
+EXPLAIN (COSTS OFF) DELETE FROM range_rel WHERE dt = '2010-06-15';
+                                   QUERY PLAN                                   
+--------------------------------------------------------------------------------
+ Delete on range_rel_6
+   ->  Seq Scan on range_rel_6
+         Filter: (dt = 'Tue Jun 15 00:00:00 2010'::timestamp without time zone)
+(3 rows)
+
+DELETE FROM range_rel WHERE dt = '2010-06-15';
+SELECT * FROM range_rel WHERE dt = '2010-06-15';
+ id | dt | value 
+----+----+-------
+(0 rows)
+
 /* Create range partitions from whole range */
 SELECT drop_range_partitions('range_rel');
 NOTICE:  0 rows copied from range_rel_15
@@ -828,7 +859,7 @@ NOTICE:  31 rows copied from range_rel_10
 NOTICE:  30 rows copied from range_rel_9
 NOTICE:  31 rows copied from range_rel_8
 NOTICE:  31 rows copied from range_rel_7
-NOTICE:  30 rows copied from range_rel_6
+NOTICE:  29 rows copied from range_rel_6
 NOTICE:  31 rows copied from range_rel_5
 NOTICE:  30 rows copied from range_rel_4
 NOTICE:  31 rows copied from range_rel_3
diff --git a/contrib/pg_pathman/pg_pathman.c b/contrib/pg_pathman/pg_pathman.c
@@ -196,9 +196,6 @@ pathman_planner_hook(Query *parse, int cursorOptions, ParamListInfo boundParams)
 	}
 
 	inheritance_disabled = false;
-
-	// if (parse->commandType != CMD_SELECT)
-
 	switch(parse->commandType)
 	{
 		case CMD_SELECT:
@@ -275,7 +272,6 @@ handle_modification_query(Query *parse)
 	PartRelationInfo *prel;
 	List	   *ranges,
 			   *wrappers = NIL;
-	// ListCell   *lc;
 	RangeTblEntry *rte;
 	WrapperNode *wrap;
 	bool found;
@@ -286,23 +282,20 @@ handle_modification_query(Query *parse)
 	rte = (RangeTblEntry *) linitial(parse->rtable);
 	prel = get_pathman_relation_info(rte->relid, &found);
 
-	// foreach(lc, parse->jointree->quals)
-	// {
-		// WrapperNode *wrap;
-		// Expr *expr = (Expr *) lfirst(lc);
+	if (!found)
+		return;
 
-		// wrap = walk_expr_tree(expr, prel);
+	/* Parse syntax tree and extract partition ranges */
 	ranges = list_make1_int(make_irange(0, prel->children_count - 1, false));
 	wrap = walk_expr_tree( (Expr *) parse->jointree->quals, prel);
 	wrappers = lappend(wrappers, wrap);
 	ranges = irange_list_intersect(ranges, wrap->rangeset);
-	// }
 
 	/* If only one partition is affected then substitute parent table with partition */
 	if (irange_list_length(ranges) == 1)
 	{
 		IndexRange irange = (IndexRange) linitial_oid(ranges);
-		elog(WARNING, "lower: %d, upper: %d, lossy: %d", irange_lower(irange), irange_upper(irange), irange_is_lossy(irange));
+		// elog(WARNING, "lower: %d, upper: %d, lossy: %d", irange_lower(irange), irange_upper(irange), irange_is_lossy(irange));
 		if (irange_lower(irange) == irange_upper(irange))
 		{
 			Oid *children = (Oid *) dsm_array_get_pointer(&prel->children);
diff --git a/contrib/pg_pathman/sql/pg_pathman.sql b/contrib/pg_pathman/sql/pg_pathman.sql
@@ -191,8 +191,9 @@ EXPLAIN (COSTS OFF) SELECT * FROM hash_rel WHERE id = 1234;
 /* Range */
 CREATE TABLE range_rel (
     id SERIAL PRIMARY KEY,
-    dt TIMESTAMP NOT NULL);
-INSERT INTO range_rel (dt) SELECT g FROM generate_series('2010-01-01'::date, '2010-12-31'::date, '1 day') as g;
+    dt TIMESTAMP NOT NULL,
+    value INTEGER);
+INSERT INTO range_rel (dt, value) SELECT g, extract(day from g) FROM generate_series('2010-01-01'::date, '2010-12-31'::date, '1 day') as g;
 SELECT create_range_partitions('range_rel', 'dt', '2010-01-01'::date, '1 month'::interval, 12);
 SELECT merge_range_partitions('range_rel_1', 'range_rel_2');
 SELECT split_range_partition('range_rel_1', '2010-02-15'::date);
@@ -201,6 +202,14 @@ SELECT prepend_range_partition('range_rel');
 EXPLAIN (COSTS OFF) SELECT * FROM range_rel WHERE dt < '2010-03-01';
 EXPLAIN (COSTS OFF) SELECT * FROM range_rel WHERE dt > '2010-12-15';
 
+/* Test UPDATE and DELETE */
+EXPLAIN (COSTS OFF) UPDATE range_rel SET value = 111 WHERE dt = '2010-06-15';
+UPDATE range_rel SET value = 111 WHERE dt = '2010-06-15';
+SELECT * FROM range_rel WHERE dt = '2010-06-15';
+EXPLAIN (COSTS OFF) DELETE FROM range_rel WHERE dt = '2010-06-15';
+DELETE FROM range_rel WHERE dt = '2010-06-15';
+SELECT * FROM range_rel WHERE dt = '2010-06-15';
+
 /* Create range partitions from whole range */
 SELECT drop_range_partitions('range_rel');
 SELECT create_partitions_from_range('range_rel', 'id', 1, 1000, 100);
