Skip to content

Commit 7e1de7b

Browse files
funbringerfunbringer
committed
UPDATE and DELETE rows in parent if 'enable_parent' is true
1 parent a8ae188 commit 7e1de7b

File tree

3 files changed

+187
-1
lines changed

3 files changed

+187
-1
lines changed

‎expected/pathman_basic.out

Lines changed: 133 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1108,6 +1108,138 @@ SELECT * FROM ttt;
11081108
Filter: (value = 2)
11091109
(5 rows)
11101110

1111+
/*
1112+
* Test CTE query (DELETE) - by @parihaaraka
1113+
*/
1114+
CREATE TABLE test.cte_del_xacts (id BIGSERIAL PRIMARY KEY, pdate DATE NOT NULL);
1115+
INSERT INTO test.cte_del_xacts (pdate) SELECT gen_date FROM generate_series('2016-01-01'::date, '2016-04-9'::date, '1 day') AS gen_date;
1116+
create table test.cte_del_xacts_specdata
1117+
(
1118+
tid BIGINT PRIMARY KEY,
1119+
test_mode SMALLINT,
1120+
state_code SMALLINT NOT NULL DEFAULT 8,
1121+
regtime TIMESTAMP WITHOUT TIME ZONE NOT NULL
1122+
);
1123+
/* create 2 partitions */
1124+
SELECT pathman.create_range_partitions('test.cte_del_xacts'::regclass, 'pdate', '2016-01-01'::date, '50 days'::interval);
1125+
NOTICE: sequence "cte_del_xacts_seq" does not exist, skipping
1126+
create_range_partitions
1127+
-------------------------
1128+
2
1129+
(1 row)
1130+
1131+
EXPLAIN (COSTS OFF)
1132+
WITH tmp AS (
1133+
SELECT tid, test_mode, regtime::DATE AS pdate, state_code
1134+
FROM test.cte_del_xacts_specdata)
1135+
DELETE FROM test.cte_del_xacts t USING tmp
1136+
WHERE t.id = tmp.tid AND t.pdate = tmp.pdate AND tmp.test_mode > 0;
1137+
QUERY PLAN
1138+
--------------------------------------------------------------------------------
1139+
Delete on cte_del_xacts t
1140+
Delete on cte_del_xacts t
1141+
Delete on cte_del_xacts_1 t_1
1142+
Delete on cte_del_xacts_2 t_2
1143+
CTE tmp
1144+
-> Seq Scan on cte_del_xacts_specdata
1145+
-> Hash Join
1146+
Hash Cond: ((tmp.tid = t.id) AND (tmp.pdate = t.pdate))
1147+
-> CTE Scan on tmp
1148+
Filter: (test_mode > 0)
1149+
-> Hash
1150+
-> Index Scan using cte_del_xacts_pkey on cte_del_xacts t
1151+
-> Hash Join
1152+
Hash Cond: ((tmp.tid = t_1.id) AND (tmp.pdate = t_1.pdate))
1153+
-> CTE Scan on tmp
1154+
Filter: (test_mode > 0)
1155+
-> Hash
1156+
-> Index Scan using cte_del_xacts_1_pkey on cte_del_xacts_1 t_1
1157+
-> Hash Join
1158+
Hash Cond: ((tmp.tid = t_2.id) AND (tmp.pdate = t_2.pdate))
1159+
-> CTE Scan on tmp
1160+
Filter: (test_mode > 0)
1161+
-> Hash
1162+
-> Index Scan using cte_del_xacts_2_pkey on cte_del_xacts_2 t_2
1163+
(24 rows)
1164+
1165+
SELECT pathman.drop_partitions('test.cte_del_xacts'); /* now drop partitions */
1166+
NOTICE: function test.cte_del_xacts_upd_trig_func() does not exist, skipping
1167+
NOTICE: 50 rows copied from test.cte_del_xacts_1
1168+
NOTICE: 50 rows copied from test.cte_del_xacts_2
1169+
drop_partitions
1170+
-----------------
1171+
2
1172+
(1 row)
1173+
1174+
/* create 1 partition */
1175+
SELECT pathman.create_range_partitions('test.cte_del_xacts'::regclass, 'pdate', '2016-01-01'::date, '1 year'::interval);
1176+
create_range_partitions
1177+
-------------------------
1178+
1
1179+
(1 row)
1180+
1181+
/* parent enabled! */
1182+
SELECT pathman.set_enable_parent('test.cte_del_xacts', true);
1183+
set_enable_parent
1184+
-------------------
1185+
1186+
(1 row)
1187+
1188+
EXPLAIN (COSTS OFF)
1189+
WITH tmp AS (
1190+
SELECT tid, test_mode, regtime::DATE AS pdate, state_code
1191+
FROM test.cte_del_xacts_specdata)
1192+
DELETE FROM test.cte_del_xacts t USING tmp
1193+
WHERE t.id = tmp.tid AND t.pdate = tmp.pdate AND tmp.test_mode > 0;
1194+
QUERY PLAN
1195+
--------------------------------------------------------------------------------
1196+
Delete on cte_del_xacts t
1197+
Delete on cte_del_xacts t
1198+
Delete on cte_del_xacts_1 t_1
1199+
CTE tmp
1200+
-> Seq Scan on cte_del_xacts_specdata
1201+
-> Hash Join
1202+
Hash Cond: ((tmp.tid = t.id) AND (tmp.pdate = t.pdate))
1203+
-> CTE Scan on tmp
1204+
Filter: (test_mode > 0)
1205+
-> Hash
1206+
-> Index Scan using cte_del_xacts_pkey on cte_del_xacts t
1207+
-> Hash Join
1208+
Hash Cond: ((tmp.tid = t_1.id) AND (tmp.pdate = t_1.pdate))
1209+
-> CTE Scan on tmp
1210+
Filter: (test_mode > 0)
1211+
-> Hash
1212+
-> Index Scan using cte_del_xacts_1_pkey on cte_del_xacts_1 t_1
1213+
(17 rows)
1214+
1215+
/* parent disabled! */
1216+
SELECT pathman.set_enable_parent('test.cte_del_xacts', false);
1217+
set_enable_parent
1218+
-------------------
1219+
1220+
(1 row)
1221+
1222+
EXPLAIN (COSTS OFF)
1223+
WITH tmp AS (
1224+
SELECT tid, test_mode, regtime::DATE AS pdate, state_code
1225+
FROM test.cte_del_xacts_specdata)
1226+
DELETE FROM test.cte_del_xacts t USING tmp
1227+
WHERE t.id = tmp.tid AND t.pdate = tmp.pdate AND tmp.test_mode > 0;
1228+
QUERY PLAN
1229+
------------------------------------------------------------------------------
1230+
Delete on cte_del_xacts_1 t
1231+
CTE tmp
1232+
-> Seq Scan on cte_del_xacts_specdata
1233+
-> Hash Join
1234+
Hash Cond: ((tmp.tid = t.id) AND (tmp.pdate = t.pdate))
1235+
-> CTE Scan on tmp
1236+
Filter: (test_mode > 0)
1237+
-> Hash
1238+
-> Index Scan using cte_del_xacts_1_pkey on cte_del_xacts_1 t
1239+
(9 rows)
1240+
1241+
DROP TABLE test.cte_del_xacts, test.cte_del_xacts_specdata CASCADE;
1242+
NOTICE: drop cascades to table test.cte_del_xacts_1
11111243
/*
11121244
* Test split and merge
11131245
*/
@@ -2027,6 +2159,6 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.index_on_childs WHERE c1 > 100 AND c1 < 2
20272159
(12 rows)
20282160

20292161
DROP SCHEMA test CASCADE;
2030-
NOTICE: drop cascades to 49 other objects
2162+
NOTICE: drop cascades to 50 other objects
20312163
DROP EXTENSION pg_pathman CASCADE;
20322164
DROP SCHEMA pathman CASCADE;

‎sql/pathman_basic.sql

Lines changed: 51 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -275,6 +275,57 @@ EXPLAIN (COSTS OFF)
275275
WITH ttt AS (SELECT * FROM test.hash_rel WHERE value = 2)
276276
SELECT * FROM ttt;
277277

278+
279+
/*
280+
* Test CTE query (DELETE) - by @parihaaraka
281+
*/
282+
CREATE TABLE test.cte_del_xacts (id BIGSERIAL PRIMARY KEY, pdate DATE NOT NULL);
283+
INSERT INTO test.cte_del_xacts (pdate) SELECT gen_date FROM generate_series('2016-01-01'::date, '2016-04-9'::date, '1 day') AS gen_date;
284+
285+
create table test.cte_del_xacts_specdata
286+
(
287+
tid BIGINT PRIMARY KEY,
288+
test_mode SMALLINT,
289+
state_code SMALLINT NOT NULL DEFAULT 8,
290+
regtime TIMESTAMP WITHOUT TIME ZONE NOT NULL
291+
);
292+
293+
/* create 2 partitions */
294+
SELECT pathman.create_range_partitions('test.cte_del_xacts'::regclass, 'pdate', '2016-01-01'::date, '50 days'::interval);
295+
296+
EXPLAIN (COSTS OFF)
297+
WITH tmp AS (
298+
SELECT tid, test_mode, regtime::DATE AS pdate, state_code
299+
FROM test.cte_del_xacts_specdata)
300+
DELETE FROM test.cte_del_xacts t USING tmp
301+
WHERE t.id = tmp.tid AND t.pdate = tmp.pdate AND tmp.test_mode > 0;
302+
303+
SELECT pathman.drop_partitions('test.cte_del_xacts'); /* now drop partitions */
304+
305+
/* create 1 partition */
306+
SELECT pathman.create_range_partitions('test.cte_del_xacts'::regclass, 'pdate', '2016-01-01'::date, '1 year'::interval);
307+
308+
/* parent enabled! */
309+
SELECT pathman.set_enable_parent('test.cte_del_xacts', true);
310+
EXPLAIN (COSTS OFF)
311+
WITH tmp AS (
312+
SELECT tid, test_mode, regtime::DATE AS pdate, state_code
313+
FROM test.cte_del_xacts_specdata)
314+
DELETE FROM test.cte_del_xacts t USING tmp
315+
WHERE t.id = tmp.tid AND t.pdate = tmp.pdate AND tmp.test_mode > 0;
316+
317+
/* parent disabled! */
318+
SELECT pathman.set_enable_parent('test.cte_del_xacts', false);
319+
EXPLAIN (COSTS OFF)
320+
WITH tmp AS (
321+
SELECT tid, test_mode, regtime::DATE AS pdate, state_code
322+
FROM test.cte_del_xacts_specdata)
323+
DELETE FROM test.cte_del_xacts t USING tmp
324+
WHERE t.id = tmp.tid AND t.pdate = tmp.pdate AND tmp.test_mode > 0;
325+
326+
DROP TABLE test.cte_del_xacts, test.cte_del_xacts_specdata CASCADE;
327+
328+
278329
/*
279330
* Test split and merge
280331
*/

‎src/planner_tree_modification.c

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -306,6 +306,9 @@ handle_modification_query(Query *parse)
306306
/* Exit if it's not partitioned */
307307
if (!prel) return;
308308

309+
/* Exit if we must include parent */
310+
if (prel->enable_parent) return;
311+
309312
/* Parse syntax tree and extract partition ranges */
310313
ranges = list_make1_irange(make_irange(0, PrelLastChild(prel), false));
311314
expr = (Expr *) eval_const_expressions(NULL, parse->jointree->quals);

0 commit comments

Comments
 (0)