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

Commit2e6b5cb

Browse files
committed
pathman: DELETE and UPDATE problem solved, UPDATE trigger added
1 parenta7caca9 commit2e6b5cb

File tree

2 files changed

+157
-20
lines changed

2 files changed

+157
-20
lines changed

‎contrib/pathman/pathman--0.1.sql

Lines changed: 63 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -42,15 +42,17 @@ BEGIN
4242
/* Create partitions and update pg_pathman configuration*/
4343
FOR partnumIN0..partitions_count-1
4444
LOOP
45-
EXECUTE format('CREATE TABLE %s_%s (LIKE %1$s INCLUDING ALL)', relation, partnum);
45+
-- EXECUTE format('CREATE TABLE %s_%s (LIKE %1$s INCLUDING ALL)', relation, partnum);
46+
EXECUTE format('CREATE TABLE %s_%s () INHERITS (%1$s)', relation, partnum);
4647
-- child_oid := relfilenode FROM pg_class WHERE relname = format('%s_%s', relation, partnum);
4748
INSERT INTO pg_pathman_hash_rels (parent, hash, child)
4849
VALUES (relation, partnum, format('%s_%s', relation, partnum));
4950
END LOOP;
5051
INSERT INTO pg_pathman_rels (relname, attr, parttype)VALUES (relation, attribute,1);
5152

52-
/* Create trigger*/
53-
PERFORM create_hash_trigger(relation, attribute, partitions_count);
53+
/* Create triggers*/
54+
PERFORM create_hash_insert_trigger(relation, attribute, partitions_count);
55+
PERFORM create_hash_update_trigger(relation, attribute, partitions_count);
5456
/* Notify backend about changes*/
5557
PERFORM pg_pathman_on_create_partitions(relid);
5658
END
@@ -59,22 +61,21 @@ $$ LANGUAGE plpgsql;
5961
/*
6062
* Creates hash trigger for specified relation
6163
*/
62-
CREATE OR REPLACEFUNCTIONpublic.create_hash_trigger(IN relationTEXT,IN attrTEXT,IN partitions_countINTEGER)
64+
CREATE OR REPLACEFUNCTIONpublic.create_hash_insert_trigger(IN relationTEXT,IN attrTEXT,IN partitions_countINTEGER)
6365
RETURNS VOIDAS
6466
$$
6567
DECLARE
66-
funcTEXT :='CREATE OR REPLACE FUNCTION %s_hash_trigger_func()'||
68+
funcTEXT :='CREATE OR REPLACE FUNCTION %s_hash_insert_trigger_func()'||
6769
'RETURNS TRIGGER AS $body$ DECLARE hash INTEGER; BEGIN'||
6870
'hash := NEW.%s %% %s; %s'||
6971
'RETURN NULL; END $body$ LANGUAGE plpgsql;';
70-
triggerTEXT :='CREATE TRIGGER %s_trigger'||
72+
triggerTEXT :='CREATE TRIGGER %s_insert_trigger'||
7173
'BEFORE INSERT ON %1$s'||
72-
'FOR EACH ROW EXECUTE PROCEDURE %1$s_hash_trigger_func();';
74+
'FOR EACH ROW EXECUTE PROCEDURE %1$s_hash_insert_trigger_func();';
7375
relidINTEGER;
7476
fieldsTEXT;
7577
fields_formatTEXT;
7678
insert_stmtTEXT;
77-
execute_stmtTEXT;
7879
BEGIN
7980
/* drop trigger and corresponding function*/
8081
PERFORM drop_hash_trigger(relation);
@@ -111,7 +112,7 @@ DECLARE
111112
qTEXT :='DROP TABLE %s_%s';
112113
BEGIN
113114
/* Drop trigger first*/
114-
PERFORMdrop_hash_trigger(relation);
115+
PERFORMdrop_hash_triggers(relation);
115116

116117
relid := relfilenodeFROM pg_classWHERE relname= relation;
117118
partitions_count :=COUNT(*)FROM pg_pathman_hash_relsWHERE parent= relation;
@@ -132,12 +133,14 @@ $$ LANGUAGE plpgsql;
132133
/*
133134
* Drops hash trigger
134135
*/
135-
CREATE OR REPLACEFUNCTIONpublic.drop_hash_trigger(IN relationTEXT)
136+
CREATE OR REPLACEFUNCTIONpublic.drop_hash_triggers(IN relationTEXT)
136137
RETURNS VOIDAS
137138
$$
138139
BEGIN
139-
EXECUTE format('DROP TRIGGER IF EXISTS %s_trigger ON %1$s', relation);
140-
EXECUTE format('DROP FUNCTION IF EXISTS %s_hash_trigger_func()', relation);
140+
EXECUTE format('DROP TRIGGER IF EXISTS %s_insert_trigger ON %1$s', relation);
141+
EXECUTE format('DROP FUNCTION IF EXISTS %s_hash_insert_trigger_func()', relation);
142+
EXECUTE format('DROP TRIGGER IF EXISTS %s_update_trigger ON %1$s', relation);
143+
EXECUTE format('DROP FUNCTION IF EXISTS %s_hash_update_trigger_func()', relation);
141144
END
142145
$$ LANGUAGE plpgsql;
143146

@@ -148,6 +151,54 @@ CREATE OR REPLACE FUNCTION pg_pathman_on_remove_partitions(relid INTEGER)
148151
RETURNS VOIDAS'pathman','on_partitions_removed' LANGUAGE C STRICT;
149152

150153

154+
CREATE OR REPLACEFUNCTIONpublic.create_hash_update_trigger(IN relationTEXT,IN attrTEXT,IN partitions_countINTEGER)
155+
RETURNS VOIDAS
156+
$$
157+
DECLARE
158+
funcTEXT :='CREATE OR REPLACE FUNCTION %s_update_trigger_func() RETURNS TRIGGER AS'||
159+
'$body$ DECLARE old_hash INTEGER; new_hash INTEGER; q TEXT; BEGIN'||
160+
'old_hash := OLD.%2$s %% %3$s;'||
161+
'new_hash := NEW.%2$s %% %3$s;'||
162+
'IF old_hash = new_hash THEN RETURN NEW; END IF;'||
163+
'q := format(''DELETE FROM %1$s_%%s WHERE %4$s'', old_hash);'||
164+
'EXECUTE q USING %5$s;'||
165+
'q := format(''INSERT INTO %1$s_%%s VALUES (%6$s)'', new_hash);'||
166+
'EXECUTE q USING %7$s;'||
167+
'RETURN NULL;'||
168+
'END $body$ LANGUAGE plpgsql';
169+
triggerTEXT :='CREATE TRIGGER %s_update_trigger'||
170+
'BEFORE UPDATE ON %1$s_%s'||
171+
'FOR EACH ROW EXECUTE PROCEDURE %1$s_update_trigger_func()';
172+
att_namesTEXT;
173+
old_fieldsTEXT;
174+
new_fieldsTEXT;
175+
att_val_fmtTEXT;
176+
att_fmtTEXT;
177+
relidINTEGER;
178+
BEGIN
179+
relid := relfilenodeFROM pg_classWHERE relname= relation;
180+
SELECT string_agg(attname,','),
181+
string_agg('OLD.'|| attname,','),
182+
string_agg('NEW.'|| attname,','),
183+
string_agg(attname||'=$'|| attnum,' AND'),
184+
string_agg('$'|| attnum,',')
185+
FROM pg_attribute
186+
WHERE attrelid=relidAND attnum>0
187+
INTO att_names,
188+
old_fields,
189+
new_fields,
190+
att_val_fmt,
191+
att_fmt;
192+
193+
EXECUTE format(func, relation, attr, partitions_count, att_val_fmt,
194+
old_fields, att_fmt, new_fields);
195+
FOR numIN0..partitions_count-1
196+
LOOP
197+
EXECUTE format(trigger, relation, num);
198+
END LOOP;
199+
END
200+
$$ LANGUAGE plpgsql;
201+
151202
-- CREATE OR REPLACE FUNCTION sample_rel_trigger_func()
152203
-- RETURNS TRIGGER AS $$
153204
-- DECLARE

‎contrib/pathman/pathman.c

Lines changed: 94 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -5,6 +5,7 @@
55
#include"nodes/primnodes.h"
66
#include"optimizer/paths.h"
77
#include"optimizer/pathnode.h"
8+
#include"optimizer/planner.h"
89
#include"utils/hsearch.h"
910
#include"utils/tqual.h"
1011
#include"utils/rel.h"
@@ -72,6 +73,8 @@ void _PG_init(void);
7273
void_PG_fini(void);
7374
staticvoidmy_shmem_startup(void);
7475
staticvoidmy_hook(PlannerInfo*root,RelOptInfo*rel,Indexrti,RangeTblEntry*rte);
76+
staticPlannedStmt*my_planner_hook(Query*parse,intcursorOptions,ParamListInfoboundParams);
77+
7578
staticvoidappend_child_relation(PlannerInfo*root,RelOptInfo*rel,Indexrti,RangeTblEntry*rte,intchildOID);
7679
staticvoidinit(void);
7780
staticvoidcreate_part_relations_hashtable(void);
@@ -85,6 +88,7 @@ static List *handle_opexpr(const OpExpr *expr, const PartRelationInfo *prel);
8588
staticList*handle_boolexpr(constBoolExpr*expr,constPartRelationInfo*prel);
8689
staticList*handle_arrexpr(constScalarArrayOpExpr*expr,constPartRelationInfo*prel);
8790

91+
staticvoidset_plain_rel_pathlist(PlannerInfo*root,RelOptInfo*rel,RangeTblEntry*rte);
8892
staticvoidset_append_rel_pathlist(PlannerInfo*root,RelOptInfo*rel,Indexrti,RangeTblEntry*rte);
8993
staticList*accumulate_append_subpath(List*subpaths,Path*path);
9094

@@ -102,6 +106,10 @@ _PG_init(void)
102106
set_rel_pathlist_hook=my_hook;
103107
shmem_startup_hook_original=shmem_startup_hook;
104108
shmem_startup_hook=my_shmem_startup;
109+
110+
planner_hook=my_planner_hook;
111+
/* TEMP */
112+
// get_relation_info_hook = my_get_relation_info;
105113
}
106114

107115
void
@@ -113,6 +121,38 @@ _PG_fini(void)
113121
hash_destroy(hash_restrictions);
114122
}
115123

124+
PlannedStmt*
125+
my_planner_hook(Query*parse,intcursorOptions,ParamListInfoboundParams)
126+
{
127+
PlannedStmt*result;
128+
RangeTblEntry*rte;
129+
ListCell*lc;
130+
PartRelationInfo*prel;
131+
132+
if (initialization_needed)
133+
init();
134+
135+
/* Disable inheritance for relations covered by pathman (only for SELECT for now) */
136+
if (parse->commandType==CMD_SELECT)
137+
{
138+
foreach(lc,parse->rtable)
139+
{
140+
rte= (RangeTblEntry*)lfirst(lc);
141+
if (rte->inh)
142+
{
143+
/* look up this relation in pathman relations */
144+
prel= (PartRelationInfo*)
145+
hash_search(relations, (constvoid*)&rte->relid,HASH_FIND,0);
146+
if (prel!=NULL)
147+
rte->inh= false;
148+
}
149+
}
150+
}
151+
152+
result=standard_planner(parse,cursorOptions,boundParams);
153+
returnresult;
154+
}
155+
116156
/*
117157
* Initialize hashtables
118158
*/
@@ -141,28 +181,31 @@ my_shmem_startup(void)
141181
void
142182
my_hook(PlannerInfo*root,RelOptInfo*rel,Indexrti,RangeTblEntry*rte)
143183
{
144-
PartRelationInfo*partrel=NULL;
184+
PartRelationInfo*prel=NULL;
145185

146-
if (initialization_needed)
147-
init();
186+
/* This works on for SELECT queries */
187+
if (root->parse->commandType!=CMD_SELECT)
188+
return;
148189

149190
/* Lookup partitioning information for parent relation */
150-
partrel= (PartRelationInfo*)
191+
prel= (PartRelationInfo*)
151192
hash_search(relations, (constvoid*)&rte->relid,HASH_FIND,0);
152193

153-
if (partrel!=NULL)
194+
if (prel!=NULL)
154195
{
155196
List*children=NIL;
156197
ListCell*lc;
157198
intchildOID=-1;
158199
inti;
159200

201+
rte->inh= true;
202+
160203
/* Run over restrictions and collect children partitions */
161204
ereport(LOG, (errmsg("Checking restrictions")));
162205
foreach(lc,rel->baserestrictinfo)
163206
{
164207
RestrictInfo*rinfo= (RestrictInfo*)lfirst(lc);
165-
List*ret=walk_expr_tree(rinfo->clause,partrel);
208+
List*ret=walk_expr_tree(rinfo->clause,prel);
166209
children=list_concat_unique_int(children,ret);
167210
list_free(ret);
168211
}
@@ -172,8 +215,8 @@ my_hook(PlannerInfo *root, RelOptInfo *rel, Index rti, RangeTblEntry *rte)
172215
ereport(LOG, (errmsg("Restrictions empty. Copy children from partrel")));
173216
// children = get_children_oids(partrel);
174217
// children = list_copy(partrel->children);
175-
for (i=0;i<partrel->children_count;i++)
176-
children=lappend_int(children,partrel->children[i]);
218+
for (i=0;i<prel->children_count;i++)
219+
children=lappend_int(children,prel->children[i]);
177220
}
178221

179222
if (length(children)>0)
@@ -210,6 +253,18 @@ my_hook(PlannerInfo *root, RelOptInfo *rel, Index rti, RangeTblEntry *rte)
210253
}
211254

212255
ereport(LOG, (errmsg("Appending children")));
256+
// Добавляем самого себя
257+
// append_child_relation(root, rel, rti, rte, partrel->oid);
258+
// {
259+
// AppendRelInfo *appinfo;
260+
// appinfo = makeNode(AppendRelInfo);
261+
// appinfo->parent_relid = rti;
262+
// appinfo->child_relid = rti;
263+
// appinfo->parent_reloid = rte->relid;
264+
// root->append_rel_list = lappend(root->append_rel_list, appinfo);
265+
// }
266+
// root->hasInheritedTarget = true;
267+
213268
foreach(lc,children)
214269
{
215270
childOID= (Oid)lfirst_int(lc);
@@ -219,7 +274,38 @@ my_hook(PlannerInfo *root, RelOptInfo *rel, Index rti, RangeTblEntry *rte)
219274

220275
/* TODO: clear old path list */
221276
rel->pathlist=NIL;
277+
// if (root->parse->commandType == CMD_SELECT)
222278
set_append_rel_pathlist(root,rel,rti,rte);
279+
// else
280+
// {
281+
// set_plain_rel_pathlist(root, rel, rte);
282+
// /* Set plin pathlist for each child relation */
283+
// intparentRTindex = rti;
284+
// ListCell *l;
285+
// foreach(l, root->append_rel_list)
286+
// {
287+
// AppendRelInfo *appinfo = (AppendRelInfo *) lfirst(l);
288+
// intchildRTindex;
289+
// RangeTblEntry *childRTE;
290+
// RelOptInfo *childrel;
291+
292+
// /* append_rel_list contains all append rels; ignore others */
293+
// if (appinfo->parent_relid != parentRTindex || appinfo->parent_relid == rti)
294+
// continue;
295+
296+
// /* Re-locate the child RTE and RelOptInfo */
297+
// childRTindex = appinfo->child_relid;
298+
// // childRTE = root->simple_rte_array[childRTindex];
299+
// // childrel = root->simple_rel_array[childRTindex];
300+
// root->simple_rel_array[childRTindex] = NULL;
301+
302+
// /*
303+
// * Compute the child's access paths.
304+
// */
305+
// // set_plain_rel_pathlist(root, childrel, childRTE);
306+
// // set_cheapest(childrel);
307+
// }
308+
// }
223309
}
224310

225311
/* Invoke original hook if needed */

0 commit comments

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