Skip to content

Commit 3018919

Browse files
zilderzilder
committed
pathman: create partitions on insert trigger (updated)
1 parent 61c84bd commit 3018919

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
bool initialization_needed = true;
@@ -23,7 +21,6 @@ static FmgrInfo *qsort_type_cmp_func;
2321
static bool validate_range_constraint(Expr *, PartRelationInfo *, Datum *, Datum *);
2422
static bool validate_hash_constraint(Expr *expr, PartRelationInfo *prel, int *hash);
2523
static int cmp_range_entries(const void *p1, const void *p2);
26-
static char *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-
static char *
45+
char *
4946
get_extension_schema()
5047
{
5148
int ret;
@@ -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
case PT_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 validate CHECK constraints
180177
*/
181178
void
182-
load_check_constraints(Oid parent_oid)
179+
load_check_constraints(Oid parent_oid, Snapshot snapshot)
183180
{
184-
bool found;
185181
PartRelationInfo *prel;
186182
RangeRelation *rangerel;
187-
int ret;
188-
int i;
189-
int proc;
190-
191-
Datum vals[1];
192-
Oid oids[1] = {INT4OID};
193-
bool nulls[1] = {false};
194-
vals[0] = Int32GetDatum(parent_oid);
195183
SPIPlanPtr plan;
196-
197-
// char sql[] = "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+
bool found;
185+
int ret,
186+
i,
187+
proc;
188+
Datum vals[1];
189+
Oid oids[1] = {INT4OID};
190+
bool nulls[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
void create_hash_restrictions_hashtable(void);
149150
void create_range_restrictions_hashtable(void);
150151
void load_relations_hashtable(bool reinitialize);
151-
void load_check_constraints(Oid parent_oid);
152+
void load_check_constraints(Oid parent_oid, Snapshot snapshot);
152153
void remove_relation_info(Oid relid);
153154

154155
/* utility functions */
155156
PartRelationInfo *get_pathman_relation_info(Oid relid, bool *found);
156157
RangeRelation *get_pathman_range_relation(Oid relid, bool *found);
157158
int range_binary_search(const RangeRelation *rangerel, FmgrInfo *cmp_func, Datum value, 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-
// int relid = DatumGetInt32(PG_GETARG_DATUM(0));
80-
// Datum value = PG_GETARG_DATUM(1);
81-
// Oid value_type = get_fn_expr_argtype(fcinfo->flinfo, 1);
82-
// int pos;
83-
// bool found;
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
int relid = DatumGetInt32(PG_GETARG_DATUM(0));
12482
Datum value = 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-
int ret;
162-
Datum vals[4];
163-
Oid oids[] = {OIDOID, value_type, value_type, value_type};
164-
bool nulls[] = {false, false, false, false};
119+
int ret;
120+
Datum vals[4];
121+
Oid oids[] = {OIDOID, value_type, value_type, value_type};
122+
bool nulls[] = {false, false, false, false};
165123
RangeEntry *re = &ranges[rangerel->ranges.length-1];
166-
int cmp_upper = FunctionCall2(&cmp_func, value, ranges[rangerel->ranges.length-1].max);
167-
int cmp_lower = FunctionCall2(&cmp_func, value, ranges[0].min);
124+
int cmp_upper = FunctionCall2(&cmp_func, value, ranges[rangerel->ranges.length-1].max);
125+
int cmp_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
else if (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
CREATE OR REPLACE FUNCTION @extschema@.on_remove_partitions(relid OID)
1919
RETURNS VOID AS 'pg_pathman', 'on_partitions_removed' LANGUAGE C STRICT;
2020

21-
CREATE OR REPLACE FUNCTION @extschema@.find_range_partition(relid OID, value ANYELEMENT)
22-
RETURNS OID AS 'pg_pathman', 'find_range_partition' LANGUAGE C STRICT;
21+
CREATE OR REPLACE FUNCTION @extschema@.find_or_create_range_partition(relid OID, value ANYELEMENT)
22+
RETURNS OID AS '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
DROP TABLE test.range_rel CASCADE;
116116

117+
/* Test automatic partition creation */
118+
CREATE TABLE test.range_rel (
119+
id SERIAL PRIMARY KEY,
120+
dt TIMESTAMP);
121+
SELECT pathman.create_range_partitions('test.range_rel', 'dt', '2015-01-01'::DATE, '10 days'::INTERVAL, 1);
122+
INSERT INTO test.range_rel (dt)
123+
SELECT generate_series('2015-01-01', '2015-04-30', '1 day'::interval);
124+
125+
INSERT INTO test.range_rel (dt)
126+
SELECT generate_series('2014-12-31', '2014-12-01', '-1 day'::interval);
127+
128+
EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt = '2014-12-15';
129+
SELECT * FROM test.range_rel WHERE dt = '2014-12-15';
130+
EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt = '2015-03-15';
131+
SELECT * FROM test.range_rel WHERE dt = '2015-03-15';
132+
133+
DROP TABLE test.range_rel CASCADE;
117134
SELECT * FROM pathman.pathman_config;
118135

119136
DROP EXTENSION pg_pathman;

0 commit comments

Comments
 (0)