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

Commit637ad38

Browse files
committed
pathman: range partitioning by timestamp
1 parent3ca814c commit637ad38

File tree

6 files changed

+29
-25
lines changed

6 files changed

+29
-25
lines changed

‎contrib/pathman/.gitignore‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1,2 +1,3 @@
11
*.o
22
*.so
3+
pathman--*.sql

‎contrib/pathman/init.c‎

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -204,7 +204,9 @@ load_range_restrictions(Oid parent_oid)
204204

205205
// SPI_connect();
206206
ret=SPI_execute_with_args("SELECT p.relfilenode, c.relfilenode, "
207-
"rr.min_int, rr.max_int, rr.min_dt, rr.max_dt "
207+
"rr.min_int, rr.max_int, "
208+
"rr.min_dt - '1 microsecond'::INTERVAL, "
209+
"rr.max_dt - '1 microsecond'::INTERVAL "
208210
"FROM pg_pathman_range_rels rr "
209211
"JOIN pg_class p ON p.relname = rr.parent "
210212
"JOIN pg_class c ON c.relname = rr.child "

‎contrib/pathman/pathman.c‎

Lines changed: 11 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -18,6 +18,7 @@
1818
#include"access/heapam.h"
1919
#include"storage/ipc.h"
2020
#include"catalog/pg_operator.h"
21+
#include"catalog/pg_type.h"
2122

2223
PG_MODULE_MAGIC;
2324

@@ -367,7 +368,10 @@ handle_binary_opexpr(const PartRelationInfo *prel, const OpExpr *expr,
367368
returnALL;
368369
}
369370
casePT_RANGE:
370-
value=c->constvalue;
371+
if (c->consttype==DATEOID)
372+
value=TimeTzADTPGetDatum(date2timestamp_no_overflow(c->constvalue));
373+
else
374+
value=c->constvalue;
371375
rangerel= (RangeRelation*)
372376
hash_search(range_restrictions, (constvoid*)&prel->oid,HASH_FIND,NULL);
373377
if (rangerel!=NULL)
@@ -391,16 +395,19 @@ handle_binary_opexpr(const PartRelationInfo *prel, const OpExpr *expr,
391395
if (i >=0&&i<rangerel->nranges)
392396
{
393397
re=&rangerel->ranges[i];
394-
if (re->min <=value&&value<re->max)
398+
if (re->min <=value&&value <=re->max)
395399
{
396400
found= true;
397401
break;
398402
}
399403
elseif (value<re->min)
400404
endidx=i-1;
401-
elseif (value >=re->max)
405+
// else if (value >= re->max)
406+
elseif (value>re->max)
402407
startidx=i+1;
403408
}
409+
else
410+
break;
404411
/* for debug's sake */
405412
Assert(++counter<100);
406413
}
@@ -425,7 +432,7 @@ handle_binary_opexpr(const PartRelationInfo *prel, const OpExpr *expr,
425432
endidx=rangerel->nranges-1;
426433
break;
427434
caseOP_STRATEGY_GT:
428-
startidx= (re->min==value) ?i+1 :i;
435+
startidx= (re->max==value) ?i+1 :i;
429436
endidx=rangerel->nranges-1;
430437
}
431438
for (j=startidx;j<=endidx;j++)
@@ -690,11 +697,7 @@ Datum
690697
on_partitions_created(PG_FUNCTION_ARGS) {
691698
/* Reload config */
692699
LWLockAcquire(AddinShmemInitLock,LW_EXCLUSIVE);
693-
694700
load_part_relations_hashtable();
695-
// load_hash_restrictions_hashtable();
696-
// load_range_restrictions_hashtable();
697-
698701
LWLockRelease(AddinShmemInitLock);
699702

700703
PG_RETURN_NULL();
@@ -708,15 +711,12 @@ on_partitions_updated(PG_FUNCTION_ARGS) {
708711
inti;
709712

710713
LWLockAcquire(AddinShmemInitLock,LW_EXCLUSIVE);
711-
712714
/* parent relation oid */
713715
relid=DatumGetInt32(PG_GETARG_DATUM(0));
714-
715716
prel= (PartRelationInfo*)
716717
hash_search(relations, (constvoid*)&relid,HASH_FIND,0);
717718
prel->children_count=0;
718719
load_part_relations_hashtable();
719-
720720
LWLockRelease(AddinShmemInitLock);
721721

722722
PG_RETURN_NULL();
@@ -733,7 +733,6 @@ on_partitions_removed(PG_FUNCTION_ARGS) {
733733

734734
/* parent relation oid */
735735
relid=DatumGetInt32(PG_GETARG_DATUM(0));
736-
737736
prel= (PartRelationInfo*)
738737
hash_search(relations, (constvoid*)&relid,HASH_FIND,0);
739738

‎contrib/pathman/sql/hash.sql‎

Lines changed: 10 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -82,16 +82,18 @@ BEGIN
8282
PERFORM drop_hash_triggers(relation);
8383

8484
/* determine fields for INSERT*/
85-
--relid := relfilenode FROM pg_class WHERE relname = relation;
86-
--SELECT string_agg('NEW.' || attname, ', '), string_agg('$' || attnum, ', ')
87-
--FROM pg_attribute
88-
--WHERE attrelid=relid AND attnum>0
89-
--INTO fields, fields_format;
85+
relid := relfilenodeFROM pg_classWHERE relname= relation;
86+
SELECT string_agg('NEW.'|| attname,','), string_agg('$'|| attnum,',')
87+
FROM pg_attribute
88+
WHERE attrelid=relidAND attnum>0
89+
INTO fields, fields_format;
9090

9191
/* generate INSERT statement for trigger*/
92-
-- insert_stmt = format('EXECUTE format(''INSERT INTO %s_%%s VALUES (%s)'', hash) USING %s;',
93-
-- relation, fields_format, fields);
94-
insert_stmt= format('EXECUTE format(''INSERT INTO %s_%%s VALUES (NEW.*)'', hash);', relation);
92+
insert_stmt= format('EXECUTE format(''INSERT INTO %s_%%s VALUES (%s)'', hash) USING %s;'
93+
, relation
94+
, fields_format
95+
, fields);
96+
-- insert_stmt = format('EXECUTE format(''INSERT INTO %s_%%s VALUES (NEW.*)'', hash);', relation);
9597

9698
/* format and create new trigger for relation*/
9799
func := format(func, relation, attr, partitions_count, insert_stmt);

‎contrib/pathman/sql/init.sql‎

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -27,8 +27,8 @@ CREATE TABLE IF NOT EXISTS @extschema@.pg_pathman_range_rels (
2727
parentVARCHAR(127),
2828
min_intINTEGER,
2929
max_intINTEGER,
30-
min_dtDATE,
31-
max_dtDATE,
30+
min_dtTIMESTAMPTZ,
31+
max_dtTIMESTAMPTZ,
3232
childVARCHAR(127)
3333
);
3434

@@ -160,7 +160,7 @@ $$ LANGUAGE plpgsql;
160160
CREATE OR REPLACEFUNCTIONpg_pathman_on_create_partitions(relidINTEGER)
161161
RETURNS VOIDAS'pathman','on_partitions_created' LANGUAGE C STRICT;
162162

163-
CREATE OR REPLACEFUNCTIONpg_pathman_on_create_partitions(relidINTEGER)
163+
CREATE OR REPLACEFUNCTIONpg_pathman_on_update_partitions(relidINTEGER)
164164
RETURNS VOIDAS'pathman','on_partitions_updated' LANGUAGE C STRICT;
165165

166166
CREATE OR REPLACEFUNCTIONpg_pathman_on_remove_partitions(relidINTEGER)

‎contrib/pathman/sql/range.sql‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -92,7 +92,7 @@ DECLARE
9292
BEGIN
9393
v_child_relname := format('%s_%s'
9494
, v_parent_relname
95-
, to_char(v_start_timestamp,'YYYY_MM_DD'));
95+
, to_char(v_start_timestamp,'YYYY_MM_DD_HH24'));
9696

9797
/* Skip existing partitions*/
9898
IF EXISTS (SELECT*FROM pg_tablesWHERE tablename= v_child_relname) THEN

0 commit comments

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