Skip to content

Commit 1ee2456

Browse files
funbringerfunbringer
committed
Merge branch 'master' into picky_nodes
2 parents 999c710 + c9b5097 commit 1ee2456

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
atttype TEXT;
123123
hashfunc TEXT;
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 := attname FROM @extschema@.pathman_config WHERE relname::regclass = relation;
144+
145+
IF attr IS NULL THEN
146+
RAISE EXCEPTION 'Table % is not partitioned', quote_ident(relation::TEXT);
147+
END IF;
148+
146149
partitions_count := COUNT(*) FROM pg_inherits WHERE 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 := attname FROM @extschema@.pathman_config
418418
WHERE relname::regclass = p_parent;
419419

420+
IF v_attname IS NULL 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 IS NULL 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 IS NULL 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_relname TEXT;
632648
v_check_name TEXT;
633649
BEGIN
634-
SELECT attname INTO v_attname FROM @extschema@.pathman_config
635-
WHERE relname::regclass = p_parent_relid::regclass;
650+
SELECT attname FROM @extschema@.pathman_config
651+
WHERE relname::regclass = p_parent_relid::regclass
652+
INTO v_attname;
653+
654+
IF v_attname IS NULL 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
RETURNS TEXT AS
689711
$$
690712
DECLARE
691-
v_attname TEXT;
692-
v_atttype TEXT;
693-
v_part_name TEXT;
694-
v_interval TEXT;
713+
v_attname TEXT;
714+
v_atttype TEXT;
715+
v_part_name TEXT;
716+
v_interval TEXT;
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_config WHERE 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 IS NULL 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_config WHERE 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 IS NULL 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 := attname FROM @extschema@.pathman_config WHERE relname::regclass = p_relation;
978+
v_attname := attname
979+
FROM @extschema@.pathman_config
980+
WHERE relname::regclass = p_relation;
981+
982+
IF v_attname IS NULL 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
CREATE OR REPLACE FUNCTION @extschema@.create_range_update_trigger(
1013-
IN relation TEXT)
1055+
IN relation REGCLASS)
10141056
RETURNS TEXT AS
10151057
$$
10161058
DECLARE
@@ -1045,8 +1087,7 @@ DECLARE
10451087
num INTEGER := 0;
10461088
attr TEXT;
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 := attname FROM @extschema@.pathman_config WHERE relname = relation;
1105+
attr := attname
1106+
FROM @extschema@.pathman_config
1107+
WHERE relname::regclass = relation;
1108+
1109+
IF attr IS NULL 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 rec in (SELECT * FROM pg_inherits WHERE inhparent = relation::regclass::oid)
@@ -1087,7 +1135,6 @@ CREATE OR REPLACE FUNCTION @extschema@.append_partitions_on_demand_internal(
10871135
RETURNS OID AS
10881136
$$
10891137
DECLARE
1090-
v_relation TEXT;
10911138
v_cnt INTEGER := 0;
10921139
i INTEGER := 0;
10931140
v_part TEXT;
@@ -1099,11 +1146,16 @@ DECLARE
10991146
v_next_value p_new_value%TYPE;
11001147
v_is_date BOOLEAN;
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_config WHERE 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 IS NULL 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)