Skip to content

Commit 637ad38

Browse files
zilderzilder
committed
pathman: range partitioning by timestamp
1 parent 3ca814c commit 637ad38

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
return ALL;
368369
}
369370
case PT_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, (const void *)&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
else if (value < re->min)
400404
endidx = i - 1;
401-
else if (value >= re->max)
405+
// else if (value >= re->max)
406+
else if (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
case OP_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
int i;
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, (const void *) &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, (const void *) &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 := 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;
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
parent VARCHAR(127),
2828
min_int INTEGER,
2929
max_int INTEGER,
30-
min_dt DATE,
31-
max_dt DATE,
30+
min_dt TIMESTAMPTZ,
31+
max_dt TIMESTAMPTZ,
3232
child VARCHAR(127)
3333
);
3434

@@ -160,7 +160,7 @@ $$ LANGUAGE plpgsql;
160160
CREATE OR REPLACE FUNCTION pg_pathman_on_create_partitions(relid INTEGER)
161161
RETURNS VOID AS 'pathman', 'on_partitions_created' LANGUAGE C STRICT;
162162

163-
CREATE OR REPLACE FUNCTION pg_pathman_on_create_partitions(relid INTEGER)
163+
CREATE OR REPLACE FUNCTION pg_pathman_on_update_partitions(relid INTEGER)
164164
RETURNS VOID AS 'pathman', 'on_partitions_updated' LANGUAGE C STRICT;
165165

166166
CREATE OR REPLACE FUNCTION pg_pathman_on_remove_partitions(relid INTEGER)

‎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_tables WHERE tablename = v_child_relname) THEN

0 commit comments

Comments
 (0)