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

Commit3018919

Browse files
committed
pathman: create partitions on insert trigger (updated)
1 parent61c84bd commit3018919

File tree

7 files changed

+159
-116
lines changed

7 files changed

+159
-116
lines changed

‎contrib/pg_pathman/expected/pg_pathman.out

Lines changed: 59 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -436,6 +436,65 @@ SELECT pathman.drop_range_partitions('test.num_range_rel');
436436
DROP TABLE test.num_range_rel CASCADE;
437437
DROP TABLE test.range_rel CASCADE;
438438
NOTICE: drop cascades to 6 other objects
439+
/* Test automatic partition creation */
440+
CREATE TABLE test.range_rel (
441+
id SERIAL PRIMARY KEY,
442+
dt TIMESTAMP);
443+
SELECT pathman.create_range_partitions('test.range_rel', 'dt', '2015-01-01'::DATE, '10 days'::INTERVAL, 1);
444+
create_range_partitions
445+
-------------------------
446+
1
447+
(1 row)
448+
449+
INSERT INTO test.range_rel (dt)
450+
SELECT generate_series('2015-01-01', '2015-04-30', '1 day'::interval);
451+
NOTICE: partition test.range_rel_2 created
452+
NOTICE: partition test.range_rel_3 created
453+
NOTICE: partition test.range_rel_4 created
454+
NOTICE: partition test.range_rel_5 created
455+
NOTICE: partition test.range_rel_6 created
456+
NOTICE: partition test.range_rel_7 created
457+
NOTICE: partition test.range_rel_8 created
458+
NOTICE: partition test.range_rel_9 created
459+
NOTICE: partition test.range_rel_10 created
460+
NOTICE: partition test.range_rel_11 created
461+
NOTICE: partition test.range_rel_12 created
462+
INSERT INTO test.range_rel (dt)
463+
SELECT generate_series('2014-12-31', '2014-12-01', '-1 day'::interval);
464+
NOTICE: partition test.range_rel_13 created
465+
NOTICE: partition test.range_rel_14 created
466+
NOTICE: partition test.range_rel_15 created
467+
NOTICE: partition test.range_rel_16 created
468+
EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt = '2014-12-15';
469+
QUERY PLAN
470+
--------------------------------------------------------------------------------
471+
Append
472+
-> Seq Scan on range_rel_14
473+
Filter: (dt = 'Mon Dec 15 00:00:00 2014'::timestamp without time zone)
474+
(3 rows)
475+
476+
SELECT * FROM test.range_rel WHERE dt = '2014-12-15';
477+
id | dt
478+
-----+--------------------------
479+
137 | Mon Dec 15 00:00:00 2014
480+
(1 row)
481+
482+
EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt = '2015-03-15';
483+
QUERY PLAN
484+
--------------------------------------------------------------------------------
485+
Append
486+
-> Seq Scan on range_rel_8
487+
Filter: (dt = 'Sun Mar 15 00:00:00 2015'::timestamp without time zone)
488+
(3 rows)
489+
490+
SELECT * FROM test.range_rel WHERE dt = '2015-03-15';
491+
id | dt
492+
----+--------------------------
493+
74 | Sun Mar 15 00:00:00 2015
494+
(1 row)
495+
496+
DROP TABLE test.range_rel CASCADE;
497+
NOTICE: drop cascades to 16 other objects
439498
SELECT * FROM pathman.pathman_config;
440499
id | relname | attname | parttype
441500
----+---------+---------+----------

‎contrib/pg_pathman/init.c

Lines changed: 15 additions & 31 deletions
Original file line numberDiff line numberDiff line change
@@ -12,8 +12,6 @@
1212
#include"utils/lsyscache.h"
1313
#include"utils/bytea.h"
1414

15-
#include"utils/snapmgr.h"
16-
1715
HTAB*relations=NULL;
1816
HTAB*range_restrictions=NULL;
1917
boolinitialization_needed= true;
@@ -23,7 +21,6 @@ static FmgrInfo *qsort_type_cmp_func;
2321
staticboolvalidate_range_constraint(Expr*,PartRelationInfo*,Datum*,Datum*);
2422
staticboolvalidate_hash_constraint(Expr*expr,PartRelationInfo*prel,int*hash);
2523
staticintcmp_range_entries(constvoid*p1,constvoid*p2);
26-
staticchar*get_extension_schema();
2724

2825
/*
2926
* Initialize hashtables
@@ -45,7 +42,7 @@ load_config(void)
4542
* Returns extension schema name or NULL. Caller is responsible for freeing
4643
* the memory.
4744
*/
48-
staticchar*
45+
char*
4946
get_extension_schema()
5047
{
5148
intret;
@@ -144,15 +141,15 @@ load_relations_hashtable(bool reinitialize)
144141
free_dsm_array(&rangerel->ranges);
145142
prel->children_count=0;
146143
}
147-
load_check_constraints(oid);
144+
load_check_constraints(oid,InvalidSnapshot);
148145
break;
149146
casePT_HASH:
150147
if (reinitialize&&prel->children.length>0)
151148
{
152149
free_dsm_array(&prel->children);
153150
prel->children_count=0;
154151
}
155-
load_check_constraints(oid);
152+
load_check_constraints(oid,InvalidSnapshot);
156153
break;
157154
}
158155
}
@@ -176,49 +173,36 @@ create_relations_hashtable()
176173
}
177174

178175
/*
179-
* Load and validate constraints
176+
* Load and validateCHECKconstraints
180177
*/
181178
void
182-
load_check_constraints(Oidparent_oid)
179+
load_check_constraints(Oidparent_oid,Snapshotsnapshot)
183180
{
184-
boolfound;
185181
PartRelationInfo*prel;
186182
RangeRelation*rangerel;
187-
intret;
188-
inti;
189-
intproc;
190-
191-
Datumvals[1];
192-
Oidoids[1]= {INT4OID};
193-
boolnulls[1]= {false};
194-
vals[0]=Int32GetDatum(parent_oid);
195183
SPIPlanPtrplan;
196-
197-
// charsql[] = "select pg_constraint.* "
198-
// "from pg_constraint "
199-
// "join pg_inherits on inhrelid = conrelid "
200-
// "where inhparent = %d and contype='c'";
201-
// char *query;
202-
184+
boolfound;
185+
intret,
186+
i,
187+
proc;
188+
Datumvals[1];
189+
Oidoids[1]= {INT4OID};
190+
boolnulls[1]= {false};
191+
192+
vals[0]=Int32GetDatum(parent_oid);
203193
prel=get_pathman_relation_info(parent_oid,NULL);
204194

205195
/* Skip if already loaded */
206196
if (prel->children.length>0)
207197
return;
208198

209-
// ret = SPI_execute_with_args("select pg_constraint.* "
210-
// "from pg_constraint "
211-
// "join pg_inherits on inhrelid = conrelid "
212-
// "where inhparent = $1 and contype='c';",
213-
// 1, oids, vals, nulls, true, 0);
214-
215199
plan=SPI_prepare("select pg_constraint.* "
216200
"from pg_constraint "
217201
"join pg_inherits on inhrelid = conrelid "
218202
"where inhparent = $1 and contype='c';",
219203
1,oids);
220204
ret=SPI_execute_snapshot(plan,vals,nulls,
221-
GetCatalogSnapshot(parent_oid),InvalidSnapshot, true, false,0);
205+
snapshot,InvalidSnapshot, true, false,0);
222206

223207
proc=SPI_processed;
224208

‎contrib/pg_pathman/pathman.h

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -4,6 +4,7 @@
44
#include"postgres.h"
55
#include"utils/date.h"
66
#include"utils/hsearch.h"
7+
#include"utils/snapshot.h"
78
#include"nodes/pg_list.h"
89
#include"storage/dsm.h"
910
#include"storage/lwlock.h"
@@ -148,12 +149,13 @@ void create_relations_hashtable(void);
148149
voidcreate_hash_restrictions_hashtable(void);
149150
voidcreate_range_restrictions_hashtable(void);
150151
voidload_relations_hashtable(boolreinitialize);
151-
voidload_check_constraints(Oidparent_oid);
152+
voidload_check_constraints(Oidparent_oid,Snapshotsnapshot);
152153
voidremove_relation_info(Oidrelid);
153154

154155
/* utility functions */
155156
PartRelationInfo*get_pathman_relation_info(Oidrelid,bool*found);
156157
RangeRelation*get_pathman_range_relation(Oidrelid,bool*found);
157158
intrange_binary_search(constRangeRelation*rangerel,FmgrInfo*cmp_func,Datumvalue,bool*fountPtr);
159+
char*get_extension_schema(void);
158160

159161
#endif/* PATHMAN_H */

‎contrib/pg_pathman/pl_funcs.c

Lines changed: 38 additions & 65 deletions
Original file line numberDiff line numberDiff line change
@@ -2,17 +2,18 @@
22
#include"utils/lsyscache.h"
33
#include"utils/typcache.h"
44
#include"utils/array.h"
5+
#include"utils/snapmgr.h"
56
#include"access/nbtree.h"
67
#include"access/xact.h"
78
#include"catalog/pg_type.h"
89
#include"executor/spi.h"
10+
#include"storage/lmgr.h"
911

1012

1113
/* declarations */
1214
PG_FUNCTION_INFO_V1(on_partitions_created );
1315
PG_FUNCTION_INFO_V1(on_partitions_updated );
1416
PG_FUNCTION_INFO_V1(on_partitions_removed );
15-
PG_FUNCTION_INFO_V1(find_range_partition );
1617
PG_FUNCTION_INFO_V1(find_or_create_range_partition);
1718
PG_FUNCTION_INFO_V1(get_range_by_idx );
1819
PG_FUNCTION_INFO_V1(get_partition_range );
@@ -71,54 +72,11 @@ on_partitions_removed(PG_FUNCTION_ARGS)
7172
}
7273

7374
/*
74-
* Returns partition oid for specified parent relid and value
75+
* Returns partition oid for specified parent relid and value.
76+
* In case when partition isn't exist try to create one.
7577
*/
76-
// Datum
77-
// find_range_partition(PG_FUNCTION_ARGS)
78-
// {
79-
// intrelid = DatumGetInt32(PG_GETARG_DATUM(0));
80-
// Datumvalue = PG_GETARG_DATUM(1);
81-
// Oidvalue_type = get_fn_expr_argtype(fcinfo->flinfo, 1);
82-
// intpos;
83-
// boolfound;
84-
// RangeRelation*rangerel;
85-
// RangeEntry*ranges;
86-
// TypeCacheEntry*tce;
87-
// PartRelationInfo *prel;
88-
// Oid cmp_proc_oid;
89-
// FmgrInfo cmp_func;
90-
91-
// tce = lookup_type_cache(value_type,
92-
// TYPECACHE_EQ_OPR | TYPECACHE_LT_OPR | TYPECACHE_GT_OPR |
93-
// TYPECACHE_CMP_PROC | TYPECACHE_CMP_PROC_FINFO);
94-
95-
// prel = (PartRelationInfo *)
96-
// hash_search(relations, (const void *) &relid, HASH_FIND, NULL);
97-
98-
// cmp_proc_oid = get_opfamily_proc(tce->btree_opf,
99-
// value_type,
100-
// prel->atttype,
101-
// BTORDER_PROC);
102-
// fmgr_info(cmp_proc_oid, &cmp_func);
103-
104-
// rangerel = (RangeRelation *)
105-
// hash_search(range_restrictions, (const void *) &relid, HASH_FIND, NULL);
106-
107-
// if (!rangerel)
108-
// PG_RETURN_NULL();
109-
110-
// ranges = dsm_array_get_pointer(&rangerel->ranges);
111-
// pos = range_binary_search(rangerel, &cmp_func, value, &found);
112-
113-
// if (found)
114-
// PG_RETURN_OID(ranges[pos].child_oid);
115-
116-
// PG_RETURN_NULL();
117-
// }
118-
11978
Datum
120-
find_range_partition(PG_FUNCTION_ARGS)
121-
// find_or_create_range_partition(PG_FUNCTION_ARGS)
79+
find_or_create_range_partition(PG_FUNCTION_ARGS)
12280
{
12381
intrelid=DatumGetInt32(PG_GETARG_DATUM(0));
12482
Datumvalue=PG_GETARG_DATUM(1);
@@ -158,14 +116,30 @@ find_range_partition(PG_FUNCTION_ARGS)
158116
PG_RETURN_OID(ranges[pos].child_oid);
159117
else
160118
{
161-
intret;
162-
Datumvals[4];
163-
Oidoids[]= {OIDOID,value_type,value_type,value_type};
164-
boolnulls[]= {false, false, false, false};
119+
intret;
120+
Datumvals[4];
121+
Oidoids[]= {OIDOID,value_type,value_type,value_type};
122+
boolnulls[]= {false, false, false, false};
165123
RangeEntry*re=&ranges[rangerel->ranges.length-1];
166-
intcmp_upper=FunctionCall2(&cmp_func,value,ranges[rangerel->ranges.length-1].max);
167-
intcmp_lower=FunctionCall2(&cmp_func,value,ranges[0].min);
124+
intcmp_upper=FunctionCall2(&cmp_func,value,ranges[rangerel->ranges.length-1].max);
125+
intcmp_lower=FunctionCall2(&cmp_func,value,ranges[0].min);
126+
char*sql;
127+
128+
/* Lock relation before appending new partitions */
129+
LWLockAcquire(load_config_lock,LW_EXCLUSIVE);
130+
131+
/*
132+
* Check if someone else has already created partition.
133+
*/
134+
ranges=dsm_array_get_pointer(&rangerel->ranges);
135+
pos=range_binary_search(rangerel,&cmp_func,value,&found);
136+
if (found)
137+
{
138+
LWLockRelease(load_config_lock);
139+
PG_RETURN_OID(ranges[pos].child_oid);
140+
}
168141

142+
/* Determine nearest range partition */
169143
if (cmp_upper>0)
170144
re=&ranges[rangerel->ranges.length-1];
171145
elseif (cmp_lower<0)
@@ -176,35 +150,34 @@ find_range_partition(PG_FUNCTION_ARGS)
176150
vals[2]=re->max;
177151
vals[3]=value;
178152

179-
LWLockAcquire(load_config_lock,LW_EXCLUSIVE);
180-
181-
/* create new partitions */
153+
/* Create new partitions */
182154
SPI_connect();
183-
ret=SPI_execute_with_args("select append_partitions_on_demand_internal($1, $2, $3, $4)",
184-
4,oids,vals,nulls, false,0);
155+
sql=psprintf("SELECT %s.append_partitions_on_demand_internal($1, $2, $3, $4)",
156+
get_extension_schema());
157+
ret=SPI_execute_with_args(sql,4,oids,vals,nulls, false,0);
158+
// ret = SPI_execute_with_args("SELECT append_partitions_on_demand_internal($1, $2, $3, $4)",
159+
// 4, oids, vals, nulls, false, 0);
185160
if (ret>0)
186161
{
162+
/* Update relation info */
187163
free_dsm_array(&rangerel->ranges);
188164
free_dsm_array(&prel->children);
189-
load_check_constraints(relid);
165+
load_check_constraints(relid,GetCatalogSnapshot(relid));
190166
}
191167
else
192-
{
193168
elog(WARNING,"Attempt to create new partitions failed");
194-
}
195169

196-
/* update relation info */
197170
SPI_finish();
198171

172+
/* Release locks */
199173
LWLockRelease(load_config_lock);
174+
// pfree(sql);
200175

201-
/*repeat binary search */
176+
/*Repeat binary search */
202177
ranges=dsm_array_get_pointer(&rangerel->ranges);
203178
pos=range_binary_search(rangerel,&cmp_func,value,&found);
204179
if (found)
205-
{
206180
PG_RETURN_OID(ranges[pos].child_oid);
207-
}
208181
}
209182

210183
PG_RETURN_NULL();

‎contrib/pg_pathman/sql/init.sql

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -18,8 +18,8 @@ RETURNS VOID AS 'pg_pathman', 'on_partitions_updated' LANGUAGE C STRICT;
1818
CREATEOR REPLACE FUNCTION @extschema@.on_remove_partitions(relidOID)
1919
RETURNS VOIDAS'pg_pathman','on_partitions_removed' LANGUAGE C STRICT;
2020

21-
CREATEOR REPLACE FUNCTION @extschema@.find_range_partition(relidOID, value ANYELEMENT)
22-
RETURNSOIDAS'pg_pathman','find_range_partition' LANGUAGE C STRICT;
21+
CREATEOR REPLACE FUNCTION @extschema@.find_or_create_range_partition(relidOID, value ANYELEMENT)
22+
RETURNSOIDAS'pg_pathman','find_or_create_range_partition' LANGUAGE C STRICT;
2323

2424

2525
/*

‎contrib/pg_pathman/sql/pg_pathman.sql

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -114,6 +114,23 @@ DROP TABLE test.num_range_rel CASCADE;
114114

115115
DROPTABLEtest.range_rel CASCADE;
116116

117+
/* Test automatic partition creation*/
118+
CREATETABLEtest.range_rel (
119+
idSERIALPRIMARY KEY,
120+
dtTIMESTAMP);
121+
SELECTpathman.create_range_partitions('test.range_rel','dt','2015-01-01'::DATE,'10 days'::INTERVAL,1);
122+
INSERT INTOtest.range_rel (dt)
123+
SELECT generate_series('2015-01-01','2015-04-30','1 day'::interval);
124+
125+
INSERT INTOtest.range_rel (dt)
126+
SELECT generate_series('2014-12-31','2014-12-01','-1 day'::interval);
127+
128+
EXPLAIN (COSTS OFF)SELECT*FROMtest.range_relWHERE dt='2014-12-15';
129+
SELECT*FROMtest.range_relWHERE dt='2014-12-15';
130+
EXPLAIN (COSTS OFF)SELECT*FROMtest.range_relWHERE dt='2015-03-15';
131+
SELECT*FROMtest.range_relWHERE dt='2015-03-15';
132+
133+
DROPTABLEtest.range_rel CASCADE;
117134
SELECT*FROMpathman.pathman_config;
118135

119136
DROP EXTENSION pg_pathman;

0 commit comments

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