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

Commit7e1de7b

Browse files
committed
UPDATE and DELETE rows in parent if 'enable_parent' is true
1 parenta8ae188 commit7e1de7b

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 to49 other objects
2162+
NOTICE: drop cascades to50 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 tttAS (SELECT*FROMtest.hash_relWHERE value=2)
276276
SELECT*FROM ttt;
277277

278+
279+
/*
280+
* Test CTE query (DELETE) - by @parihaaraka
281+
*/
282+
CREATETABLEtest.cte_del_xacts (idBIGSERIALPRIMARY KEY, pdateDATENOT NULL);
283+
INSERT INTOtest.cte_del_xacts (pdate)SELECT gen_dateFROM generate_series('2016-01-01'::date,'2016-04-9'::date,'1 day')AS gen_date;
284+
285+
createtabletest.cte_del_xacts_specdata
286+
(
287+
tidBIGINTPRIMARY KEY,
288+
test_modeSMALLINT,
289+
state_codeSMALLINTNOT NULL DEFAULT8,
290+
regtimeTIMESTAMP WITHOUT TIME ZONENOT NULL
291+
);
292+
293+
/* create 2 partitions*/
294+
SELECTpathman.create_range_partitions('test.cte_del_xacts'::regclass,'pdate','2016-01-01'::date,'50 days'::interval);
295+
296+
EXPLAIN (COSTS OFF)
297+
WITH tmpAS (
298+
SELECT tid, test_mode, regtime::DATEAS pdate, state_code
299+
FROMtest.cte_del_xacts_specdata)
300+
DELETEFROMtest.cte_del_xacts t USING tmp
301+
WHEREt.id=tmp.tidANDt.pdate=tmp.pdateANDtmp.test_mode>0;
302+
303+
SELECTpathman.drop_partitions('test.cte_del_xacts');/* now drop partitions*/
304+
305+
/* create 1 partition*/
306+
SELECTpathman.create_range_partitions('test.cte_del_xacts'::regclass,'pdate','2016-01-01'::date,'1 year'::interval);
307+
308+
/* parent enabled!*/
309+
SELECTpathman.set_enable_parent('test.cte_del_xacts', true);
310+
EXPLAIN (COSTS OFF)
311+
WITH tmpAS (
312+
SELECT tid, test_mode, regtime::DATEAS pdate, state_code
313+
FROMtest.cte_del_xacts_specdata)
314+
DELETEFROMtest.cte_del_xacts t USING tmp
315+
WHEREt.id=tmp.tidANDt.pdate=tmp.pdateANDtmp.test_mode>0;
316+
317+
/* parent disabled!*/
318+
SELECTpathman.set_enable_parent('test.cte_del_xacts', false);
319+
EXPLAIN (COSTS OFF)
320+
WITH tmpAS (
321+
SELECT tid, test_mode, regtime::DATEAS pdate, state_code
322+
FROMtest.cte_del_xacts_specdata)
323+
DELETEFROMtest.cte_del_xacts t USING tmp
324+
WHEREt.id=tmp.tidANDt.pdate=tmp.pdateANDtmp.test_mode>0;
325+
326+
DROPTABLEtest.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)
[8]ページ先頭
©2009-2025 Movatter.jp