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

Commit1ee2456

Browse files
committed
Merge branch 'master' into picky_nodes
2 parents999c710 +c9b5097 commit1ee2456

File tree

3 files changed

+93
-31
lines changed

3 files changed

+93
-31
lines changed

‎README.md

Lines changed: 11 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -120,7 +120,7 @@ Merge two adjacent RANGE partitions. Data from `partition2` is copied to `partit
120120
```
121121
append_range_partition(p_relation TEXT)
122122
```
123-
Appends new RANGE partition and returns
123+
Appends new RANGE partition and returns
124124
```
125125
prepend_range_partition(p_relation TEXT)
126126
```
@@ -160,6 +160,13 @@ disable_partitioning(relation TEXT)
160160
Disables`pg_pathman` partitioning mechanism for the specified parent table and removes an insert trigger. Partitions itself remain unchanged.
161161

162162
##Examples
163+
164+
###Common tips
165+
You can easily add**_partition_** column containing the names of the underlying partitions using the system attribute called**_tableoid_**:
166+
```
167+
SELECT tableoid::regclass, * AS partition FROM partitioned_table;
168+
```
169+
163170
###HASH
164171
Consider an example of HASH partitioning. First create a table with some integer column:
165172
```
@@ -180,13 +187,13 @@ This will create new partitions and move the data from parent to partitions.
180187
Here is an example of the query with filtering by partitioning key and its plan:
181188
```
182189
SELECT * FROM items WHERE id = 1234;
183-
id | name | code
190+
id | name | code
184191
------+----------------------------------+------
185192
1234 | 81dc9bdb52d04dc20036dbd8313ed055 | 1855
186193
(1 row)
187194
188195
EXPLAIN SELECT * FROM items WHERE id = 1234;
189-
QUERY PLAN
196+
QUERY PLAN
190197
------------------------------------------------------------------------------------
191198
Append (cost=0.28..8.29 rows=0 width=0)
192199
-> Index Scan using items_34_pkey on items_34 (cost=0.28..8.29 rows=0 width=0)
@@ -195,7 +202,7 @@ EXPLAIN SELECT * FROM items WHERE id = 1234;
195202
Note that pg_pathman excludes parent table from the query plan. To access parent table use ONLY modifier:
196203
```
197204
EXPLAIN SELECT * FROM ONLY items;
198-
QUERY PLAN
205+
QUERY PLAN
199206
------------------------------------------------------
200207
Seq Scan on items (cost=0.00..0.00 rows=1 width=45)
201208
```

‎hash.sql

Lines changed: 6 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -122,12 +122,10 @@ DECLARE
122122
atttypeTEXT;
123123
hashfuncTEXT;
124124
BEGIN
125-
relation := @extschema@.validate_relname(relation);
126-
127125
SELECT* INTO plain_schema, plain_relname
128126
FROM @extschema@.get_plain_schema_and_relname(relation);
129127

130-
relid := relation::regclass::oid;
128+
relid := relation::oid;
131129
SELECT string_agg(attname,','),
132130
string_agg('OLD.'|| attname,','),
133131
string_agg('NEW.'|| attname,','),
@@ -143,6 +141,11 @@ BEGIN
143141
att_fmt;
144142

145143
attr := attnameFROM @extschema@.pathman_configWHERE relname::regclass= relation;
144+
145+
IF attr ISNULL THEN
146+
RAISE EXCEPTION'Table % is not partitioned', quote_ident(relation::TEXT);
147+
END IF;
148+
146149
partitions_count :=COUNT(*)FROM pg_inheritsWHERE inhparent= relation::oid;
147150

148151
/* Function name, trigger name and child relname template*/

‎range.sql

Lines changed: 76 additions & 24 deletions
Original file line numberDiff line numberDiff line change
@@ -417,6 +417,10 @@ BEGIN
417417
v_attname := attnameFROM @extschema@.pathman_config
418418
WHERE relname::regclass= p_parent;
419419

420+
IF v_attname ISNULL THEN
421+
RAISE EXCEPTION'Table % is not partitioned', quote_ident(p_parent::TEXT);
422+
END IF;
423+
420424
SELECT* INTO v_plain_schema, v_plain_relname
421425
FROM @extschema@.get_plain_schema_and_relname(p_parent);
422426

@@ -488,9 +492,15 @@ BEGIN
488492
FROM pg_inherits
489493
WHERE inhrelid= v_child_relid;
490494

491-
SELECT attname, parttype INTO v_attname, v_part_type
495+
SELECT attname, parttype
492496
FROM @extschema@.pathman_config
493-
WHERE relname::regclass= v_parent_relid::regclass;
497+
WHERE relname::regclass= v_parent_relid::regclass
498+
INTO v_attname, v_part_type;
499+
500+
IF v_attname ISNULL THEN
501+
RAISE EXCEPTION'Table % is not partitioned',
502+
quote_ident(v_parent_relid::regclass::text);
503+
END IF;
494504

495505
SELECT* INTO v_plain_schema, v_plain_relname
496506
FROM @extschema@.get_plain_schema_and_relname(p_partition);
@@ -585,9 +595,15 @@ BEGIN
585595
RAISE EXCEPTION'Cannot merge partitions having different parents';
586596
END IF;
587597

588-
SELECT attname, parttype INTO v_attname, v_part_type
598+
SELECT attname, parttype
589599
FROM @extschema@.pathman_config
590-
WHERE relname::regclass= v_parent_relid1::regclass;
600+
WHERE relname::regclass= v_parent_relid1::regclass
601+
INTO v_attname, v_part_type;
602+
603+
IF v_attname ISNULL THEN
604+
RAISE EXCEPTION'Table % is not partitioned',
605+
quote_ident(v_parent_relid1::regclass::text);
606+
END IF;
591607

592608
/* Check if this is RANGE partition*/
593609
IF v_part_type!=2 THEN
@@ -631,8 +647,14 @@ DECLARE
631647
v_child_relnameTEXT;
632648
v_check_nameTEXT;
633649
BEGIN
634-
SELECT attname INTO v_attnameFROM @extschema@.pathman_config
635-
WHERE relname::regclass= p_parent_relid::regclass;
650+
SELECT attnameFROM @extschema@.pathman_config
651+
WHERE relname::regclass= p_parent_relid::regclass
652+
INTO v_attname;
653+
654+
IF v_attname ISNULL THEN
655+
RAISE EXCEPTION'Table % is not partitioned',
656+
quote_ident(p_parent_relid::regclass::text);
657+
END IF;
636658

637659
SELECT* INTO v_plain_schema, v_plain_relname
638660
FROM @extschema@.get_plain_schema_and_relname(p_part1);
@@ -688,16 +710,22 @@ CREATE OR REPLACE FUNCTION @extschema@.append_range_partition(
688710
RETURNSTEXTAS
689711
$$
690712
DECLARE
691-
v_attnameTEXT;
692-
v_atttypeTEXT;
693-
v_part_nameTEXT;
694-
v_intervalTEXT;
713+
v_attnameTEXT;
714+
v_atttypeTEXT;
715+
v_part_nameTEXT;
716+
v_intervalTEXT;
695717
BEGIN
696718
/* Prevent concurrent partition creation*/
697719
PERFORM @extschema@.acquire_partitions_lock();
698720

699-
SELECT attname, range_interval INTO v_attname, v_interval
700-
FROM @extschema@.pathman_configWHERE relname::regclass= p_relation;
721+
SELECT attname, range_interval
722+
FROM @extschema@.pathman_config
723+
WHERE relname::regclass= p_relation
724+
INTO v_attname, v_interval;
725+
726+
IF v_attname ISNULL THEN
727+
RAISE EXCEPTION'Table % is not partitioned', quote_ident(p_relation::TEXT);
728+
END IF;
701729

702730
v_atttype := @extschema@.get_attribute_type_name(p_relation, v_attname);
703731

@@ -764,8 +792,15 @@ BEGIN
764792
/* Prevent concurrent partition creation*/
765793
PERFORM @extschema@.acquire_partitions_lock();
766794

767-
SELECT attname, range_interval INTO v_attname, v_interval
768-
FROM @extschema@.pathman_configWHERE relname::regclass= p_relation;
795+
SELECT attname, range_interval
796+
FROM @extschema@.pathman_config
797+
WHERE relname::regclass= p_relation
798+
INTO v_attname, v_interval;
799+
800+
IF v_attname ISNULL THEN
801+
RAISE EXCEPTION'Table % is not partitioned', quote_ident(p_relation::TEXT);
802+
END IF;
803+
769804
v_atttype := @extschema@.get_attribute_type_name(p_relation, v_attname);
770805

771806
EXECUTE format('SELECT @extschema@.prepend_partition_internal($1, $2, $3, ARRAY[]::%s[])', v_atttype)
@@ -940,7 +975,14 @@ BEGIN
940975
, p_relation);
941976

942977
/* Set check constraint*/
943-
v_attname := attnameFROM @extschema@.pathman_configWHERE relname::regclass= p_relation;
978+
v_attname := attname
979+
FROM @extschema@.pathman_config
980+
WHERE relname::regclass= p_relation;
981+
982+
IF v_attname ISNULL THEN
983+
RAISE EXCEPTION'Table % is not partitioned', quote_ident(p_relation::TEXT);
984+
END IF;
985+
944986
v_cond := @extschema@.get_range_condition(v_attname, p_start_value, p_end_value);
945987

946988
/* Plain partition name and schema*/
@@ -1010,7 +1052,7 @@ LANGUAGE plpgsql;
10101052
* Creates an update trigger
10111053
*/
10121054
CREATEOR REPLACE FUNCTION @extschema@.create_range_update_trigger(
1013-
IN relationTEXT)
1055+
IN relationREGCLASS)
10141056
RETURNSTEXTAS
10151057
$$
10161058
DECLARE
@@ -1045,8 +1087,7 @@ DECLARE
10451087
numINTEGER :=0;
10461088
attrTEXT;
10471089
BEGIN
1048-
relation := @extschema@.validate_relname(relation);
1049-
relid := relation::regclass::oid;
1090+
relid := relation::oid;
10501091
SELECT string_agg(attname,','),
10511092
string_agg('OLD.'|| attname,','),
10521093
string_agg('NEW.'|| attname,','),
@@ -1061,7 +1102,14 @@ BEGIN
10611102
att_val_fmt,
10621103
att_fmt;
10631104

1064-
attr := attnameFROM @extschema@.pathman_configWHERE relname= relation;
1105+
attr := attname
1106+
FROM @extschema@.pathman_config
1107+
WHERE relname::regclass= relation;
1108+
1109+
IF attr ISNULL THEN
1110+
RAISE EXCEPTION'Table % is not partitioned', quote_ident(relation::TEXT);
1111+
END IF;
1112+
10651113
EXECUTE format(func, relation, attr,0, att_val_fmt,
10661114
old_fields, att_fmt, new_fields);
10671115
FOR recin (SELECT*FROM pg_inheritsWHERE inhparent= relation::regclass::oid)
@@ -1087,7 +1135,6 @@ CREATE OR REPLACE FUNCTION @extschema@.append_partitions_on_demand_internal(
10871135
RETURNSOIDAS
10881136
$$
10891137
DECLARE
1090-
v_relationTEXT;
10911138
v_cntINTEGER :=0;
10921139
iINTEGER :=0;
10931140
v_partTEXT;
@@ -1099,11 +1146,16 @@ DECLARE
10991146
v_next_value p_new_value%TYPE;
11001147
v_is_dateBOOLEAN;
11011148
BEGIN
1102-
v_relation := @extschema@.validate_relname(p_relid::regclass::text);
1103-
11041149
/* get attribute name and interval*/
1105-
SELECT attname, range_interval INTO v_attname, v_interval
1106-
FROM @extschema@.pathman_configWHERE relname= v_relation;
1150+
SELECT attname, range_interval
1151+
FROM @extschema@.pathman_config
1152+
WHERE relname::regclass= p_relid::regclass
1153+
INTO v_attname, v_interval;
1154+
1155+
IF v_attname ISNULL THEN
1156+
RAISE EXCEPTION'Table % is not partitioned',
1157+
quote_ident(p_relid::regclass::text);
1158+
END IF;
11071159

11081160
v_min := @extschema@.get_min_range_value(p_relid::regclass::oid, p_new_value);
11091161
v_max := @extschema@.get_max_range_value(p_relid::regclass::oid, p_new_value);

0 commit comments

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