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

Commit0dead52

Browse files
committed
replace_hash_partition() function
1 parentd586756 commit0dead52

File tree

8 files changed

+288
-23
lines changed

8 files changed

+288
-23
lines changed

‎expected/pathman_basic.out

Lines changed: 46 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -885,14 +885,58 @@ SELECT * FROM test.hash_rel WHERE id = 123;
885885
123 | 456 | 789
886886
(1 row)
887887

888+
/* Test replacing hash partition */
889+
CREATE TABLE test.hash_rel_extern (LIKE test.hash_rel INCLUDING ALL);
890+
SELECT pathman.replace_hash_partition('test.hash_rel_0', 'test.hash_rel_extern');
891+
replace_hash_partition
892+
------------------------
893+
test.hash_rel_extern
894+
(1 row)
895+
896+
\d+ test.hash_rel_0
897+
Table "test.hash_rel_0"
898+
Column | Type | Modifiers | Storage | Stats target | Description
899+
--------+---------+------------------------------------------------------------+---------+--------------+-------------
900+
id | integer | not null default nextval('test.hash_rel_id_seq'::regclass) | plain | |
901+
value | integer | not null | plain | |
902+
abc | integer | | plain | |
903+
Indexes:
904+
"hash_rel_0_pkey" PRIMARY KEY, btree (id)
905+
Triggers:
906+
hash_rel_upd_trig BEFORE UPDATE ON test.hash_rel_0 FOR EACH ROW EXECUTE PROCEDURE test.hash_rel_upd_trig_func()
907+
908+
\d+ test.hash_rel_extern
909+
Table "test.hash_rel_extern"
910+
Column | Type | Modifiers | Storage | Stats target | Description
911+
--------+---------+------------------------------------------------------------+---------+--------------+-------------
912+
id | integer | not null default nextval('test.hash_rel_id_seq'::regclass) | plain | |
913+
value | integer | not null | plain | |
914+
abc | integer | | plain | |
915+
Indexes:
916+
"hash_rel_extern_pkey" PRIMARY KEY, btree (id)
917+
Check constraints:
918+
"pathman_hash_rel_extern_2_check" CHECK (pathman.get_hash_part_idx(hashint4(value), 3) = 0)
919+
Inherits: test.hash_rel
920+
921+
INSERT INTO test.hash_rel SELECT * FROM test.hash_rel_0;
922+
DROP TABLE test.hash_rel_0;
923+
EXPLAIN SELECT * FROM test.hash_rel;
924+
QUERY PLAN
925+
---------------------------------------------------------------------------------------------
926+
Append (cost=10000000000.00..30000000032.44 rows=2044 width=12)
927+
-> Seq Scan on hash_rel_extern (cost=10000000000.00..10000000030.40 rows=2040 width=12)
928+
-> Seq Scan on hash_rel_1 (cost=10000000000.00..10000000001.02 rows=2 width=12)
929+
-> Seq Scan on hash_rel_2 (cost=10000000000.00..10000000001.02 rows=2 width=12)
930+
(4 rows)
931+
888932
/*
889933
* Clean up
890934
*/
891935
SELECT pathman.drop_partitions('test.hash_rel');
892-
NOTICE: drop cascades to 3 other objects
893-
NOTICE: 2 rows copied from test.hash_rel_0
936+
NOTICE: drop cascades to 2 other objects
894937
NOTICE: 3 rows copied from test.hash_rel_1
895938
NOTICE: 2 rows copied from test.hash_rel_2
939+
NOTICE: 2 rows copied from test.hash_rel_extern
896940
drop_partitions
897941
-----------------
898942
3

‎hash.sql

Lines changed: 107 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -22,8 +22,8 @@ DECLARE
2222
v_child_relnameTEXT;
2323
v_plain_schemaTEXT;
2424
v_plain_relnameTEXT;
25-
v_atttypeREGTYPE;
26-
v_hashfuncREGPROC;
25+
--v_atttypeREGTYPE;
26+
--v_hashfuncREGPROC;
2727
v_init_callbackREGPROCEDURE;
2828

2929
BEGIN
@@ -41,8 +41,8 @@ BEGIN
4141
PERFORM @extschema@.common_relation_checks(parent_relid, attribute);
4242

4343
/* Fetch atttype and its hash function*/
44-
v_atttype := @extschema@.get_attribute_type(parent_relid, attribute);
45-
v_hashfunc := @extschema@.get_type_hash_func(v_atttype);
44+
--v_atttype := @extschema@.get_attribute_type(parent_relid, attribute);
45+
--v_hashfunc := @extschema@.get_type_hash_func(v_atttype);
4646

4747
SELECT* INTO v_plain_schema, v_plain_relname
4848
FROM @extschema@.get_plain_schema_and_relname(parent_relid);
@@ -64,15 +64,23 @@ BEGIN
6464
parent_relid::TEXT,
6565
@extschema@.get_rel_tablespace_name(parent_relid));
6666

67-
EXECUTE format('ALTER TABLE %s ADD CONSTRAINT %s
68-
CHECK (@extschema@.get_hash_part_idx(%s(%s), %s) = %s)',
67+
-- EXECUTE format('ALTER TABLE %s ADD CONSTRAINT %s
68+
-- CHECK (@extschema@.get_hash_part_idx(%s(%s), %s) = %s)',
69+
-- v_child_relname,
70+
-- @extschema@.build_check_constraint_name(v_child_relname::REGCLASS,
71+
-- attribute),
72+
-- v_hashfunc::TEXT,
73+
-- attribute,
74+
-- partitions_count,
75+
-- partnum);
76+
EXECUTE format('ALTER TABLE %s ADD CONSTRAINT %s CHECK (%s)',
6977
v_child_relname,
70-
@extschema@.build_check_constraint_name(v_child_relname::REGCLASS,
78+
@extschema@.build_check_constraint_name(v_child_relname,
7179
attribute),
72-
v_hashfunc::TEXT,
73-
attribute,
74-
partitions_count,
75-
partnum);
80+
@extschema@.build_hash_condition(v_child_relname,
81+
attribute,
82+
partitions_count,
83+
partnum));
7684

7785
PERFORM @extschema@.copy_foreign_keys(parent_relid, v_child_relname::REGCLASS);
7886

@@ -105,6 +113,94 @@ END
105113
$$ LANGUAGE plpgsql
106114
SET client_min_messages= WARNING;
107115

116+
/*
117+
* Replace hash partition with another one. It could be useful in case when
118+
* someone wants to attach foreign table as a partition
119+
*/
120+
CREATEOR REPLACE FUNCTION @extschema@.replace_hash_partition(
121+
old_partitionREGCLASS,
122+
new_partitionREGCLASS)
123+
RETURNS REGCLASSAS
124+
$$
125+
DECLARE
126+
v_attnameTEXT;
127+
rel_persistenceCHAR;
128+
v_init_callbackREGPROCEDURE;
129+
v_parent_relidREGCLASS;
130+
v_part_countINT;
131+
v_part_numINT;
132+
BEGIN
133+
PERFORM @extschema@.validate_relname(old_partition);
134+
PERFORM @extschema@.validate_relname(new_partition);
135+
136+
/* Parent relation*/
137+
v_parent_relid := @extschema@.get_parent_of_partition(old_partition);
138+
139+
/* Acquire lock on parent*/
140+
PERFORM @extschema@.lock_partitioned_relation(v_parent_relid);
141+
142+
/* Ignore temporary tables*/
143+
SELECT relpersistenceFROMpg_catalog.pg_class
144+
WHEREoid= new_partition INTO rel_persistence;
145+
146+
IF rel_persistence='t'::CHAR THEN
147+
RAISE EXCEPTION'temporary table "%" cannot be used as a partition',
148+
new_partition::TEXT;
149+
END IF;
150+
151+
/* Check that new partition has an equal structure as parent does*/
152+
IF NOT @extschema@.validate_relations_equality(v_parent_relid, new_partition) THEN
153+
RAISE EXCEPTION'partition must have the exact same structure as parent';
154+
END IF;
155+
156+
/* Get partitioning key*/
157+
v_attname := attnameFROM @extschema@.pathman_configWHERE partrel= v_parent_relid;
158+
IF v_attname ISNULL THEN
159+
RAISE EXCEPTION'table "%" is not partitioned', v_parent_relid::TEXT;
160+
END IF;
161+
162+
/* Calculate partitions count and old partition's number*/
163+
v_part_count :=count(*)FROM @extschema@.pathman_partition_listWHERE parent= v_parent_relid;
164+
v_part_num := @extschema@.get_partition_hash(v_parent_relid, old_partition);
165+
166+
/* Detach old partition*/
167+
EXECUTE format('ALTER TABLE %s NO INHERIT %s', old_partition, v_parent_relid);
168+
EXECUTE format('ALTER TABLE %s DROP CONSTRAINT IF EXISTS %s',
169+
old_partition,
170+
@extschema@.build_check_constraint_name(old_partition::REGCLASS,
171+
v_attname));
172+
173+
/* Attach new one*/
174+
EXECUTE format('ALTER TABLE %s INHERIT %s', new_partition, v_parent_relid);
175+
EXECUTE format('ALTER TABLE %s ADD CONSTRAINT %s CHECK (%s)',
176+
new_partition,
177+
@extschema@.build_check_constraint_name(new_partition::regclass,
178+
v_attname),
179+
@extschema@.build_hash_condition(new_partition::regclass,
180+
v_attname,
181+
v_part_count,
182+
v_part_num));
183+
184+
/* Fetch init_callback from 'params' table*/
185+
WITH stub_callback(stub)as (values (0))
186+
SELECT coalesce(init_callback,0::REGPROCEDURE)
187+
FROM stub_callback
188+
LEFT JOIN @extschema@.pathman_config_paramsAS params
189+
ONparams.partrel= v_parent_relid
190+
INTO v_init_callback;
191+
192+
PERFORM @extschema@.invoke_on_partition_created_callback(v_parent_relid,
193+
new_partition,
194+
v_init_callback);
195+
196+
/* Invalidate cache*/
197+
PERFORM @extschema@.on_update_partitions(v_parent_relid);
198+
199+
RETURN new_partition;
200+
END
201+
$$
202+
LANGUAGE plpgsql;
203+
108204
/*
109205
* Creates an update trigger
110206
*/

‎init.sql

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -803,3 +803,23 @@ CREATE OR REPLACE FUNCTION @extschema@.invoke_on_partition_created_callback(
803803
init_callbackREGPROCEDURE)
804804
RETURNS VOIDAS'pg_pathman','invoke_on_partition_created_callback'
805805
LANGUAGE C;
806+
807+
/*
808+
* Build hash condition for a CHECK CONSTRAINT
809+
*/
810+
CREATEOR REPLACE FUNCTION @extschema@.build_hash_condition(
811+
parent_relidREGCLASS,
812+
attnameTEXT,
813+
partitions_countINT,
814+
partition_numberINT)
815+
RETURNSTEXTAS'pg_pathman','build_hash_condition'
816+
LANGUAGE C;
817+
818+
/*
819+
* Returns hash value for specified partition (0..N)
820+
*/
821+
CREATEOR REPLACE FUNCTION @extschema@.get_partition_hash(
822+
parent_relidREGCLASS,
823+
partitionREGCLASS)
824+
RETURNSINTAS'pg_pathman','get_partition_hash'
825+
LANGUAGE C;

‎sql/pathman_basic.sql

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -236,6 +236,15 @@ ALTER TABLE test.hash_rel ADD COLUMN abc int;
236236
INSERT INTOtest.hash_rel (id, value, abc)VALUES (123,456,789);
237237
SELECT*FROMtest.hash_relWHERE id=123;
238238

239+
/* Test replacing hash partition*/
240+
CREATETABLEtest.hash_rel_extern (LIKEtest.hash_rel INCLUDING ALL);
241+
SELECTpathman.replace_hash_partition('test.hash_rel_0','test.hash_rel_extern');
242+
\d+test.hash_rel_0
243+
\d+test.hash_rel_extern
244+
INSERT INTOtest.hash_relSELECT*FROMtest.hash_rel_0;
245+
DROPTABLEtest.hash_rel_0;
246+
EXPLAINSELECT*FROMtest.hash_rel;
247+
239248
/*
240249
* Clean up
241250
*/

‎src/pl_funcs.c

Lines changed: 5 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -201,24 +201,19 @@ Datum
201201
get_attribute_type_pl(PG_FUNCTION_ARGS)
202202
{
203203
Oidrelid=PG_GETARG_OID(0);
204-
text*attname=PG_GETARG_TEXT_P(1);
204+
char*attname=text_to_cstring(PG_GETARG_TEXT_P(1));
205205
Oidresult;
206-
HeapTupletp;
207206

208-
/* NOTE: for now it's the most efficient way */
209-
tp=SearchSysCacheAttName(relid,text_to_cstring(attname));
210-
if (HeapTupleIsValid(tp))
207+
if ((result=get_attribute_type(relid,attname))!=InvalidOid)
211208
{
212-
Form_pg_attributeatt_tup= (Form_pg_attribute)GETSTRUCT(tp);
213-
result=att_tup->atttypid;
214-
ReleaseSysCache(tp);
215-
216209
PG_RETURN_OID(result);
217210
}
218211
else
212+
{
219213
elog(ERROR,"Cannot find type name for attribute \"%s\" "
220214
"of relation \"%s\"",
221-
text_to_cstring(attname),get_rel_name_or_relid(relid));
215+
attname,get_rel_name_or_relid(relid));
216+
}
222217

223218
PG_RETURN_NULL();/* keep compiler happy */
224219
}

‎src/pl_hash_funcs.c

Lines changed: 77 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -9,14 +9,19 @@
99
*/
1010

1111
#include"pathman.h"
12+
#include"utils.h"
1213

1314
#include"utils/typcache.h"
15+
#include"utils/lsyscache.h"
16+
#include"utils/builtins.h"
1417

1518

1619
/* Function declarations */
1720

1821
PG_FUNCTION_INFO_V1(get_type_hash_func );
1922
PG_FUNCTION_INFO_V1(get_hash_part_idx );
23+
PG_FUNCTION_INFO_V1(build_hash_condition );
24+
PG_FUNCTION_INFO_V1(get_partition_hash );
2025

2126

2227
/*
@@ -44,3 +49,75 @@ get_hash_part_idx(PG_FUNCTION_ARGS)
4449

4550
PG_RETURN_UINT32(hash_to_part_index(value,part_count));
4651
}
52+
53+
/*
54+
* Build hash condition for a CHECK CONSTRAINT
55+
*/
56+
Datum
57+
build_hash_condition(PG_FUNCTION_ARGS)
58+
{
59+
TypeCacheEntry*tce;
60+
61+
Oidparent=PG_GETARG_OID(0);
62+
text*attname=PG_GETARG_TEXT_P(1);
63+
uint32partitions_count=PG_GETARG_UINT32(2);
64+
uint32partition_number=PG_GETARG_UINT32(3);
65+
Oidattyp;
66+
char*result;
67+
68+
if (partition_number >=partitions_count)
69+
elog(ERROR,
70+
"Partition number cannot exceed partitions count");
71+
72+
/* Get attribute type and its hash function oid */
73+
attyp=get_attribute_type(parent,text_to_cstring(attname));
74+
if (attyp==InvalidOid)
75+
elog(ERROR,
76+
"Relation '%s' has no attribute '%s'",
77+
get_rel_name(parent),
78+
text_to_cstring(attname));
79+
80+
tce=lookup_type_cache(attyp,TYPECACHE_HASH_PROC);
81+
82+
/* Create hash condition CSTRING */
83+
result=psprintf("%s.get_hash_part_idx(%s(%s), %u) = %u",
84+
get_namespace_name(get_pathman_schema()),
85+
get_func_name(tce->hash_proc),
86+
text_to_cstring(attname),
87+
partitions_count,
88+
partition_number);
89+
90+
PG_RETURN_TEXT_P(cstring_to_text(result));
91+
}
92+
93+
/*
94+
* Returns hash value for specified partition (0..N)
95+
*/
96+
Datum
97+
get_partition_hash(PG_FUNCTION_ARGS)
98+
{
99+
constPartRelationInfo*prel;
100+
Oidparent=PG_GETARG_OID(0);
101+
Oidpartition=PG_GETARG_OID(1);
102+
Oid*children;
103+
inti;
104+
105+
/* Validate partition type */
106+
prel=get_pathman_relation_info(parent);
107+
if (!prel||prel->parttype!=PT_HASH)
108+
elog(ERROR,
109+
"Relation '%s' isn't partitioned by hash",
110+
get_rel_name(parent));
111+
112+
/* Searching for partition */
113+
children=PrelGetChildrenArray(prel);
114+
for (i=0;i<prel->children_count;i++)
115+
if (children[i]==partition)
116+
PG_RETURN_UINT32(i);
117+
118+
/* If we get here then there is no such partition for specified parent */
119+
elog(ERROR,
120+
"Relation '%s' isn't a part of partitioned table '%s'",
121+
get_rel_name(parent),
122+
get_rel_name(partition));
123+
}

0 commit comments

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