Skip to content

Commit 2e6b5cb

Browse files
zilderzilder
committed
pathman: DELETE and UPDATE problem solved, UPDATE trigger added
1 parent a7caca9 commit 2e6b5cb

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 partnum IN 0..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 REPLACE FUNCTION public.create_hash_trigger(IN relation TEXT, IN attr TEXT, IN partitions_count INTEGER)
64+
CREATE OR REPLACE FUNCTION public.create_hash_insert_trigger(IN relation TEXT, IN attr TEXT, IN partitions_count INTEGER)
6365
RETURNS VOID AS
6466
$$
6567
DECLARE
66-
func TEXT := 'CREATE OR REPLACE FUNCTION %s_hash_trigger_func() ' ||
68+
func TEXT := '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-
trigger TEXT := 'CREATE TRIGGER %s_trigger ' ||
72+
trigger TEXT := '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
relid INTEGER;
7476
fields TEXT;
7577
fields_format TEXT;
7678
insert_stmt TEXT;
77-
execute_stmt TEXT;
7879
BEGIN
7980
/* drop trigger and corresponding function */
8081
PERFORM drop_hash_trigger(relation);
@@ -111,7 +112,7 @@ DECLARE
111112
q TEXT := 'DROP TABLE %s_%s';
112113
BEGIN
113114
/* Drop trigger first */
114-
PERFORM drop_hash_trigger(relation);
115+
PERFORM drop_hash_triggers(relation);
115116

116117
relid := relfilenode FROM pg_class WHERE relname = relation;
117118
partitions_count := COUNT(*) FROM pg_pathman_hash_rels WHERE parent = relation;
@@ -132,12 +133,14 @@ $$ LANGUAGE plpgsql;
132133
/*
133134
* Drops hash trigger
134135
*/
135-
CREATE OR REPLACE FUNCTION public.drop_hash_trigger(IN relation TEXT)
136+
CREATE OR REPLACE FUNCTION public.drop_hash_triggers(IN relation TEXT)
136137
RETURNS VOID AS
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 VOID AS 'pathman', 'on_partitions_removed' LANGUAGE C STRICT;
149152

150153

154+
CREATE OR REPLACE FUNCTION public.create_hash_update_trigger(IN relation TEXT, IN attr TEXT, IN partitions_count INTEGER)
155+
RETURNS VOID AS
156+
$$
157+
DECLARE
158+
func TEXT := '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+
trigger TEXT := '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_names TEXT;
173+
old_fields TEXT;
174+
new_fields TEXT;
175+
att_val_fmt TEXT;
176+
att_fmt TEXT;
177+
relid INTEGER;
178+
BEGIN
179+
relid := relfilenode FROM pg_class WHERE 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=relid AND 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 num IN 0..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
static void my_shmem_startup(void);
7475
static void my_hook(PlannerInfo *root, RelOptInfo *rel, Index rti, RangeTblEntry *rte);
76+
static PlannedStmt * my_planner_hook(Query *parse, int cursorOptions, ParamListInfo boundParams);
77+
7578
static void append_child_relation(PlannerInfo *root, RelOptInfo *rel, Index rti, RangeTblEntry *rte, int childOID);
7679
static void init(void);
7780
static void create_part_relations_hashtable(void);
@@ -85,6 +88,7 @@ static List *handle_opexpr(const OpExpr *expr, const PartRelationInfo *prel);
8588
static List *handle_boolexpr(const BoolExpr *expr, const PartRelationInfo *prel);
8689
static List *handle_arrexpr(const ScalarArrayOpExpr *expr, const PartRelationInfo *prel);
8790

91+
static void set_plain_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte);
8892
static void set_append_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, Index rti, RangeTblEntry *rte);
8993
static List *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, int cursorOptions, ParamListInfo boundParams)
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, (const void *) &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+
return result;
154+
}
155+
116156
/*
117157
* Initialize hashtables
118158
*/
@@ -141,28 +181,31 @@ my_shmem_startup(void)
141181
void
142182
my_hook(PlannerInfo *root, RelOptInfo *rel, Index rti, 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, (const void *) &rte->relid, HASH_FIND, 0);
152193

153-
if (partrel != NULL)
194+
if (prel != NULL)
154195
{
155196
List *children = NIL;
156197
ListCell *lc;
157198
int childOID = -1;
158199
int i;
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+
// int parentRTindex = rti;
284+
// ListCell *l;
285+
// foreach(l, root->append_rel_list)
286+
// {
287+
// AppendRelInfo *appinfo = (AppendRelInfo *) lfirst(l);
288+
// int childRTindex;
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)