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

Commitadaa6e3

Browse files
committed
add tablespace support
1 parent71b348d commitadaa6e3

File tree

6 files changed

+175
-33
lines changed

6 files changed

+175
-33
lines changed

‎hash.sql‎

Lines changed: 9 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -24,6 +24,7 @@ DECLARE
2424
v_plain_schemaTEXT;
2525
v_plain_relnameTEXT;
2626
v_hashfuncTEXT;
27+
v_tablespaceTEXT;
2728

2829
BEGIN
2930
IF partition_data= true THEN
@@ -49,16 +50,21 @@ BEGIN
4950
INSERT INTO @extschema@.pathman_config (partrel, attname, parttype)
5051
VALUES (parent_relid, attribute,1);
5152

53+
/* Determine tablespace of parent table*/
54+
v_tablespace := @extschema@.get_rel_tablespace_name(parent_relid);
55+
5256
/* Create partitions and update pg_pathman configuration*/
5357
FOR partnumIN0..partitions_count-1
5458
LOOP
5559
v_child_relname := format('%s.%s',
5660
quote_ident(v_plain_schema),
5761
quote_ident(v_plain_relname||'_'|| partnum));
5862

59-
EXECUTE format('CREATE TABLE %1$s (LIKE %2$s INCLUDING ALL) INHERITS (%2$s)',
60-
v_child_relname,
61-
parent_relid::TEXT);
63+
EXECUTE format(
64+
'CREATE TABLE %1$s (LIKE %2$s INCLUDING ALL) INHERITS (%2$s) TABLESPACE %s',
65+
v_child_relname,
66+
parent_relid::TEXT,
67+
v_tablespace);
6268

6369
EXECUTE format('ALTER TABLE %s ADD CONSTRAINT %s
6470
CHECK (@extschema@.get_hash_part_idx(%s(%s), %s) = %s)',

‎init.sql‎

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -40,6 +40,14 @@ CREATE TABLE IF NOT EXISTS @extschema@.pathman_config_params (
4040
CREATEUNIQUE INDEXi_pathman_config_params
4141
ON @extschema@.pathman_config_params(partrel);
4242

43+
CREATETYPEpartitionAS (
44+
parentREGCLASS,
45+
parttypeINTEGER,
46+
childREGCLASS,
47+
start_valueTEXT,
48+
end_valueTEXT
49+
);
50+
4351
/*
4452
* Invalidate relcache every time someone changes parameters config.
4553
*/
@@ -695,3 +703,10 @@ LANGUAGE C STRICT;
695703
CREATEOR REPLACE FUNCTION @extschema@.debug_capture()
696704
RETURNS VOIDAS'pg_pathman','debug_capture'
697705
LANGUAGE C STRICT;
706+
707+
/*
708+
* Return tablespace name for specified relation
709+
*/
710+
CREATEOR REPLACE FUNCTION @extschema@.get_rel_tablespace_name(relation REGCLASS)
711+
RETURNSTEXTAS'pg_pathman','get_rel_tablespace_name'
712+
LANGUAGE C STRICT;

‎range.sql‎

Lines changed: 95 additions & 30 deletions
Original file line numberDiff line numberDiff line change
@@ -91,8 +91,8 @@ DECLARE
9191
v_rows_countINTEGER;
9292
v_maxp_start_value%TYPE;
9393
v_cur_valuep_start_value%TYPE := p_start_value;
94+
v_tablespaceTEXT;
9495
iINTEGER;
95-
9696
BEGIN
9797
IF partition_data= true THEN
9898
/* Acquire data modification lock*/
@@ -149,12 +149,20 @@ BEGIN
149149
INSERT INTO @extschema@.pathman_config (partrel, attname, parttype, range_interval)
150150
VALUES (parent_relid, p_attribute,2, p_interval::TEXT);
151151

152+
/* Determine tablespace of parent table*/
153+
v_tablespace := @extschema@.get_rel_tablespace_name(parent_relid);
154+
152155
/* Create first partition*/
153156
FOR iIN1..p_count
154157
LOOP
155-
EXECUTE format('SELECT @extschema@.create_single_range_partition($1, $2, $3::%s)',
156-
pg_typeof(p_start_value))
157-
USING parent_relid, p_start_value, p_start_value+ p_interval;
158+
EXECUTE
159+
format('SELECT @extschema@.create_single_range_partition($1, $2, $3::%s, tablespace:=$4)',
160+
pg_typeof(p_start_value))
161+
USING
162+
parent_relid,
163+
p_start_value,
164+
p_start_value+ p_interval,
165+
v_tablespace;
158166

159167
p_start_value := p_start_value+ p_interval;
160168
END LOOP;
@@ -190,6 +198,7 @@ DECLARE
190198
v_rows_countINTEGER;
191199
v_maxp_start_value%TYPE;
192200
v_cur_valuep_start_value%TYPE := p_start_value;
201+
v_tablespaceTEXT;
193202
iINTEGER;
194203

195204
BEGIN
@@ -250,12 +259,18 @@ BEGIN
250259
INSERT INTO @extschema@.pathman_config (partrel, attname, parttype, range_interval)
251260
VALUES (parent_relid, p_attribute,2, p_interval::TEXT);
252261

262+
/* Determine tablespace of parent table*/
263+
v_tablespace := @extschema@.get_rel_tablespace_name(parent_relid);
264+
253265
/* create first partition*/
254266
FOR iIN1..p_count
255267
LOOP
256-
PERFORM @extschema@.create_single_range_partition(parent_relid,
257-
p_start_value,
258-
p_start_value+ p_interval);
268+
PERFORM @extschema@.create_single_range_partition(
269+
parent_relid,
270+
p_start_value,
271+
p_start_value+ p_interval,
272+
tablespace := v_tablespace);
273+
259274
p_start_value := p_start_value+ p_interval;
260275
END LOOP;
261276

@@ -288,6 +303,7 @@ RETURNS INTEGER AS
288303
$$
289304
DECLARE
290305
part_countINTEGER :=0;
306+
v_tablespaceTEXT;
291307

292308
BEGIN
293309
IF partition_data= true THEN
@@ -320,11 +336,17 @@ BEGIN
320336
INSERT INTO @extschema@.pathman_config (partrel, attname, parttype, range_interval)
321337
VALUES (parent_relid, p_attribute,2, p_interval::TEXT);
322338

339+
/* Determine tablespace of parent table*/
340+
v_tablespace := @extschema@.get_rel_tablespace_name(parent_relid);
341+
323342
WHILE p_start_value<= p_end_value
324343
LOOP
325-
PERFORM @extschema@.create_single_range_partition(parent_relid,
326-
p_start_value,
327-
p_start_value+ p_interval);
344+
PERFORM @extschema@.create_single_range_partition(
345+
parent_relid,
346+
p_start_value,
347+
p_start_value+ p_interval,
348+
tablespace := v_tablespace);
349+
328350
p_start_value := p_start_value+ p_interval;
329351
part_count := part_count+1;
330352
END LOOP;
@@ -358,6 +380,7 @@ RETURNS INTEGER AS
358380
$$
359381
DECLARE
360382
part_countINTEGER :=0;
383+
v_tablespaceTEXT;
361384

362385
BEGIN
363386
IF partition_data= true THEN
@@ -386,11 +409,19 @@ BEGIN
386409
INSERT INTO @extschema@.pathman_config (partrel, attname, parttype, range_interval)
387410
VALUES (parent_relid, p_attribute,2, p_interval::TEXT);
388411

412+
/* Determine tablespace of parent table*/
413+
v_tablespace := @extschema@.get_rel_tablespace_name(parent_relid);
414+
389415
WHILE p_start_value<= p_end_value
390416
LOOP
391-
EXECUTE format('SELECT @extschema@.create_single_range_partition($1, $2, $3::%s);',
392-
pg_typeof(p_start_value))
393-
USING parent_relid, p_start_value, p_start_value+ p_interval;
417+
EXECUTE
418+
format('SELECT @extschema@.create_single_range_partition($1, $2, $3::%s, tablespace:=$4);',
419+
pg_typeof(p_start_value))
420+
USING
421+
parent_relid,
422+
p_start_value,
423+
p_start_value+ p_interval,
424+
v_tablespace;
394425

395426
p_start_value := p_start_value+ p_interval;
396427
part_count := part_count+1;
@@ -419,7 +450,8 @@ CREATE OR REPLACE FUNCTION @extschema@.create_single_range_partition(
419450
parent_relidREGCLASS,
420451
p_start_valueANYELEMENT,
421452
p_end_valueANYELEMENT,
422-
partition_nameTEXT DEFAULTNULL)
453+
partition_nameTEXT DEFAULTNULL,
454+
tablespaceTEXT DEFAULTNULL)
423455
RETURNSTEXTAS
424456
$$
425457
DECLARE
@@ -431,7 +463,7 @@ DECLARE
431463
v_plain_relnameTEXT;
432464
v_child_relname_existsBOOL;
433465
v_seq_nameTEXT;
434-
466+
v_create_table_queryTEXT;
435467
BEGIN
436468
v_attname := attnameFROM @extschema@.pathman_config
437469
WHERE partrel= parent_relid;
@@ -466,7 +498,15 @@ BEGIN
466498
v_child_relname := partition_name;
467499
END IF;
468500

469-
EXECUTE format('CREATE TABLE %1$s (LIKE %2$s INCLUDING ALL) INHERITS (%2$s)',
501+
v_create_table_query :='CREATE TABLE %1$s (LIKE %2$s INCLUDING ALL) INHERITS (%2$s)';
502+
503+
/* If tablespace is specified then add it to a create query*/
504+
if NOT tablespace ISNULL THEN
505+
v_create_table_query := v_create_table_query||' TABLESPACE'||tablespace;
506+
END IF;
507+
RAISE NOTICE'query: %', v_create_table_query;
508+
509+
EXECUTE format(v_create_table_query,
470510
v_child_relname,
471511
parent_relid::TEXT);
472512

@@ -708,7 +748,8 @@ $$ LANGUAGE plpgsql;
708748
*/
709749
CREATEOR REPLACE FUNCTION @extschema@.append_range_partition(
710750
parent_relidREGCLASS,
711-
partition_nameTEXT DEFAULTNULL)
751+
partition_nameTEXT DEFAULTNULL,
752+
tablespaceTEXT DEFAULTNULL)
712753
RETURNSTEXTAS
713754
$$
714755
DECLARE
@@ -734,13 +775,14 @@ BEGIN
734775

735776
EXECUTE
736777
format(
737-
'SELECT @extschema@.append_partition_internal($1, $2, $3, ARRAY[]::%s[], $4)',
778+
'SELECT @extschema@.append_partition_internal($1, $2, $3, ARRAY[]::%s[], $4, $5)',
738779
v_atttype)
739780
USING
740781
parent_relid,
741782
v_atttype,
742783
v_interval,
743-
partition_name
784+
partition_name,
785+
tablespace
744786
INTO
745787
v_part_name;
746788

@@ -762,7 +804,8 @@ CREATE OR REPLACE FUNCTION @extschema@.append_partition_internal(
762804
p_atttypeTEXT,
763805
p_intervalTEXT,
764806
p_rangeANYARRAY DEFAULTNULL,
765-
partition_nameTEXT DEFAULTNULL)
807+
partition_nameTEXT DEFAULTNULL,
808+
tablespaceTEXT DEFAULTNULL)
766809
RETURNSTEXTAS
767810
$$
768811
DECLARE
@@ -773,6 +816,11 @@ BEGIN
773816
RAISE EXCEPTION'Cannot append to empty partitions set';
774817
END IF;
775818

819+
/* If tablespace isn't specified then choose parent's tablespace*/
820+
IF tablespace ISNULL THEN
821+
tablespace := @extschema@.get_rel_tablespace_name(parent_relid);
822+
END IF;
823+
776824
p_range := @extschema@.get_range_by_idx(parent_relid,-1,0);
777825

778826
IF @extschema@.is_date_type(p_atttype::regtype) THEN
@@ -784,13 +832,14 @@ BEGIN
784832
ELSE
785833
EXECUTE
786834
format(
787-
'SELECT @extschema@.create_single_range_partition($1, $2, $2 + $3::%s, $4)',
835+
'SELECT @extschema@.create_single_range_partition($1, $2, $2 + $3::%s, $4, $5)',
788836
p_atttype)
789837
USING
790838
parent_relid,
791839
p_range[2],
792840
p_interval,
793-
partition_name
841+
partition_name,
842+
tablespace
794843
INTO
795844
v_part_name;
796845
END IF;
@@ -806,7 +855,8 @@ LANGUAGE plpgsql;
806855
*/
807856
CREATEOR REPLACE FUNCTION @extschema@.prepend_range_partition(
808857
parent_relidREGCLASS,
809-
partition_nameTEXT DEFAULTNULL)
858+
partition_nameTEXT DEFAULTNULL,
859+
tablespaceTEXT DEFAULTNULL)
810860
RETURNSTEXTAS
811861
$$
812862
DECLARE
@@ -829,13 +879,14 @@ BEGIN
829879

830880
EXECUTE
831881
format(
832-
'SELECT @extschema@.prepend_partition_internal($1, $2, $3, ARRAY[]::%s[], $4)',
882+
'SELECT @extschema@.prepend_partition_internal($1, $2, $3, ARRAY[]::%s[], $4, $5)',
833883
v_atttype)
834884
USING
835885
parent_relid,
836886
v_atttype,
837887
v_interval,
838-
partition_name
888+
partition_name,
889+
tablespace
839890
INTO
840891
v_part_name;
841892

@@ -857,7 +908,8 @@ CREATE OR REPLACE FUNCTION @extschema@.prepend_partition_internal(
857908
p_atttypeTEXT,
858909
p_intervalTEXT,
859910
p_rangeANYARRAY DEFAULTNULL,
860-
partition_nameTEXT DEFAULTNULL)
911+
partition_nameTEXT DEFAULTNULL,
912+
tablespaceTEXT DEFAULTNULL)
861913
RETURNSTEXTAS
862914
$$
863915
DECLARE
@@ -868,6 +920,11 @@ BEGIN
868920
RAISE EXCEPTION'Cannot prepend to empty partitions set';
869921
END IF;
870922

923+
/* If tablespace isn't specified then choose parent's tablespace*/
924+
IF tablespace ISNULL THEN
925+
tablespace := @extschema@.get_rel_tablespace_name(parent_relid);
926+
END IF;
927+
871928
p_range := @extschema@.get_range_by_idx(parent_relid,0,0);
872929

873930
IF @extschema@.is_date_type(p_atttype::regtype) THEN
@@ -879,13 +936,14 @@ BEGIN
879936
ELSE
880937
EXECUTE
881938
format(
882-
'SELECT @extschema@.create_single_range_partition($1, $2 - $3::%s, $2, $4)',
939+
'SELECT @extschema@.create_single_range_partition($1, $2 - $3::%s, $2, $4, $5)',
883940
p_atttype)
884941
USING
885942
parent_relid,
886943
p_range[1],
887944
p_interval,
888-
partition_name
945+
partition_name,
946+
tablespace
889947
INTO
890948
v_part_name;
891949
END IF;
@@ -903,7 +961,8 @@ CREATE OR REPLACE FUNCTION @extschema@.add_range_partition(
903961
parent_relidREGCLASS,
904962
p_start_valueANYELEMENT,
905963
p_end_valueANYELEMENT,
906-
partition_nameTEXT DEFAULTNULL)
964+
partition_nameTEXT DEFAULTNULL,
965+
tablespaceTEXT DEFAULTNULL)
907966
RETURNSTEXTAS
908967
$$
909968
DECLARE
@@ -923,11 +982,17 @@ BEGIN
923982
RAISE EXCEPTION'Specified range overlaps with existing partitions';
924983
END IF;
925984

985+
/* If tablespace isn't specified then choose parent's tablespace*/
986+
IF tablespace ISNULL THEN
987+
tablespace := @extschema@.get_rel_tablespace_name(parent_relid);
988+
END IF;
989+
926990
/* Create new partition*/
927991
v_part_name := @extschema@.create_single_range_partition(parent_relid,
928992
p_start_value,
929993
p_end_value,
930-
partition_name);
994+
partition_name,
995+
tablespace);
931996
PERFORM @extschema@.on_update_partitions(parent_relid);
932997

933998
RETURN v_part_name;

0 commit comments

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