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

Commit3ea8474

Browse files
committed
hash_replace branch merged int rel_1_2_beta
2 parents984f2f4 +862874b commit3ea8474

File tree

6 files changed

+291
-7
lines changed

6 files changed

+291
-7
lines changed

‎expected/pathman_basic.out‎

Lines changed: 53 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1565,14 +1565,64 @@ SELECT * FROM test.hash_rel WHERE id = 123;
15651565
123 | 456 | 789
15661566
(1 row)
15671567

1568+
/* Test replacing hash partition */
1569+
CREATE TABLE test.hash_rel_extern (LIKE test.hash_rel INCLUDING ALL);
1570+
SELECT pathman.replace_hash_partition('test.hash_rel_0', 'test.hash_rel_extern');
1571+
replace_hash_partition
1572+
------------------------
1573+
test.hash_rel_extern
1574+
(1 row)
1575+
1576+
\d+ test.hash_rel_0
1577+
Table "test.hash_rel_0"
1578+
Column | Type | Modifiers | Storage | Stats target | Description
1579+
--------+---------+------------------------------------------------------------+---------+--------------+-------------
1580+
id | integer | not null default nextval('test.hash_rel_id_seq'::regclass) | plain | |
1581+
value | integer | not null | plain | |
1582+
abc | integer | | plain | |
1583+
Indexes:
1584+
"hash_rel_0_pkey" PRIMARY KEY, btree (id)
1585+
Triggers:
1586+
hash_rel_upd_trig BEFORE UPDATE ON test.hash_rel_0 FOR EACH ROW EXECUTE PROCEDURE test.hash_rel_upd_trig_func()
1587+
1588+
\d+ test.hash_rel_extern
1589+
Table "test.hash_rel_extern"
1590+
Column | Type | Modifiers | Storage | Stats target | Description
1591+
--------+---------+------------------------------------------------------------+---------+--------------+-------------
1592+
id | integer | not null default nextval('test.hash_rel_id_seq'::regclass) | plain | |
1593+
value | integer | not null | plain | |
1594+
abc | integer | | plain | |
1595+
Indexes:
1596+
"hash_rel_extern_pkey" PRIMARY KEY, btree (id)
1597+
Check constraints:
1598+
"pathman_hash_rel_extern_2_check" CHECK (pathman.get_hash_part_idx(hashint4(value), 3) = 0)
1599+
Inherits: test.hash_rel
1600+
1601+
INSERT INTO test.hash_rel SELECT * FROM test.hash_rel_0;
1602+
DROP TABLE test.hash_rel_0;
1603+
/* Table with which we are replacing partition must have exact same structure */
1604+
CREATE TABLE test.hash_rel_wrong(
1605+
idINTEGER NOT NULL,
1606+
valueINTEGER);
1607+
SELECT pathman.replace_hash_partition('test.hash_rel_1', 'test.hash_rel_wrong');
1608+
ERROR: partition must have the exact same structure as parent
1609+
EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel;
1610+
QUERY PLAN
1611+
-----------------------------------
1612+
Append
1613+
-> Seq Scan on hash_rel_extern
1614+
-> Seq Scan on hash_rel_1
1615+
-> Seq Scan on hash_rel_2
1616+
(4 rows)
1617+
15681618
/*
15691619
* Clean up
15701620
*/
15711621
SELECT pathman.drop_partitions('test.hash_rel');
1572-
NOTICE: drop cascades to 3 other objects
1573-
NOTICE: 2 rows copied from test.hash_rel_0
1622+
NOTICE: drop cascades to 2 other objects
15741623
NOTICE: 3 rows copied from test.hash_rel_1
15751624
NOTICE: 2 rows copied from test.hash_rel_2
1625+
NOTICE: 2 rows copied from test.hash_rel_extern
15761626
drop_partitions
15771627
-----------------
15781628
3
@@ -2248,6 +2298,6 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.index_on_childs WHERE c1 > 100 AND c1 < 2
22482298
(12 rows)
22492299

22502300
DROP SCHEMA test CASCADE;
2251-
NOTICE: drop cascades to50 other objects
2301+
NOTICE: drop cascades to51 other objects
22522302
DROP EXTENSION pg_pathman CASCADE;
22532303
DROP SCHEMA pathman CASCADE;

‎hash.sql‎

Lines changed: 92 additions & 4 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);
@@ -72,6 +72,94 @@ END
7272
$$ LANGUAGE plpgsql
7373
SET client_min_messages= WARNING;
7474

75+
/*
76+
* Replace hash partition with another one. It could be useful in case when
77+
* someone wants to attach foreign table as a partition
78+
*/
79+
CREATEOR REPLACE FUNCTION @extschema@.replace_hash_partition(
80+
old_partitionREGCLASS,
81+
new_partitionREGCLASS)
82+
RETURNS REGCLASSAS
83+
$$
84+
DECLARE
85+
v_attnameTEXT;
86+
rel_persistenceCHAR;
87+
v_init_callbackREGPROCEDURE;
88+
v_parent_relidREGCLASS;
89+
v_part_countINT;
90+
v_part_numINT;
91+
BEGIN
92+
PERFORM @extschema@.validate_relname(old_partition);
93+
PERFORM @extschema@.validate_relname(new_partition);
94+
95+
/* Parent relation*/
96+
v_parent_relid := @extschema@.get_parent_of_partition(old_partition);
97+
98+
/* Acquire lock on parent*/
99+
PERFORM @extschema@.lock_partitioned_relation(v_parent_relid);
100+
101+
/* Ignore temporary tables*/
102+
SELECT relpersistenceFROMpg_catalog.pg_class
103+
WHEREoid= new_partition INTO rel_persistence;
104+
105+
IF rel_persistence='t'::CHAR THEN
106+
RAISE EXCEPTION'temporary table "%" cannot be used as a partition',
107+
new_partition::TEXT;
108+
END IF;
109+
110+
/* Check that new partition has an equal structure as parent does*/
111+
IF NOT @extschema@.validate_relations_equality(v_parent_relid, new_partition) THEN
112+
RAISE EXCEPTION'partition must have the exact same structure as parent';
113+
END IF;
114+
115+
/* Get partitioning key*/
116+
v_attname := attnameFROM @extschema@.pathman_configWHERE partrel= v_parent_relid;
117+
IF v_attname ISNULL THEN
118+
RAISE EXCEPTION'table "%" is not partitioned', v_parent_relid::TEXT;
119+
END IF;
120+
121+
/* Calculate partitions count and old partition's number*/
122+
v_part_count :=count(*)FROM @extschema@.pathman_partition_listWHERE parent= v_parent_relid;
123+
v_part_num := @extschema@.get_partition_hash(v_parent_relid, old_partition);
124+
125+
/* Detach old partition*/
126+
EXECUTE format('ALTER TABLE %s NO INHERIT %s', old_partition, v_parent_relid);
127+
EXECUTE format('ALTER TABLE %s DROP CONSTRAINT IF EXISTS %s',
128+
old_partition,
129+
@extschema@.build_check_constraint_name(old_partition::REGCLASS,
130+
v_attname));
131+
132+
/* Attach new one*/
133+
EXECUTE format('ALTER TABLE %s INHERIT %s', new_partition, v_parent_relid);
134+
EXECUTE format('ALTER TABLE %s ADD CONSTRAINT %s CHECK (%s)',
135+
new_partition,
136+
@extschema@.build_check_constraint_name(new_partition::regclass,
137+
v_attname),
138+
@extschema@.build_hash_condition(new_partition::regclass,
139+
v_attname,
140+
v_part_count,
141+
v_part_num));
142+
143+
/* Fetch init_callback from 'params' table*/
144+
WITH stub_callback(stub)as (values (0))
145+
SELECT coalesce(init_callback,0::REGPROCEDURE)
146+
FROM stub_callback
147+
LEFT JOIN @extschema@.pathman_config_paramsAS params
148+
ONparams.partrel= v_parent_relid
149+
INTO v_init_callback;
150+
151+
PERFORM @extschema@.invoke_on_partition_created_callback(v_parent_relid,
152+
new_partition,
153+
v_init_callback);
154+
155+
/* Invalidate cache*/
156+
PERFORM @extschema@.on_update_partitions(v_parent_relid);
157+
158+
RETURN new_partition;
159+
END
160+
$$
161+
LANGUAGE plpgsql;
162+
75163
/*
76164
* Creates an update trigger
77165
*/

‎init.sql‎

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -783,3 +783,23 @@ CREATE OR REPLACE FUNCTION @extschema@.invoke_on_partition_created_callback(
783783
init_callbackREGPROCEDURE)
784784
RETURNS VOIDAS'pg_pathman','invoke_on_partition_created_callback'
785785
LANGUAGE C;
786+
787+
/*
788+
* Build hash condition for a CHECK CONSTRAINT
789+
*/
790+
CREATEOR REPLACE FUNCTION @extschema@.build_hash_condition(
791+
parent_relidREGCLASS,
792+
attnameTEXT,
793+
partitions_countINT,
794+
partition_numberINT)
795+
RETURNSTEXTAS'pg_pathman','build_hash_condition'
796+
LANGUAGE C;
797+
798+
/*
799+
* Returns hash value for specified partition (0..N)
800+
*/
801+
CREATEOR REPLACE FUNCTION @extschema@.get_partition_hash(
802+
parent_relidREGCLASS,
803+
partitionREGCLASS)
804+
RETURNSINTAS'pg_pathman','get_partition_hash'
805+
LANGUAGE C;

‎sql/pathman_basic.sql‎

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -448,6 +448,20 @@ ALTER TABLE test.hash_rel ADD COLUMN abc int;
448448
INSERT INTOtest.hash_rel (id, value, abc)VALUES (123,456,789);
449449
SELECT*FROMtest.hash_relWHERE id=123;
450450

451+
/* Test replacing hash partition*/
452+
CREATETABLEtest.hash_rel_extern (LIKEtest.hash_rel INCLUDING ALL);
453+
SELECTpathman.replace_hash_partition('test.hash_rel_0','test.hash_rel_extern');
454+
\d+test.hash_rel_0
455+
\d+test.hash_rel_extern
456+
INSERT INTOtest.hash_relSELECT*FROMtest.hash_rel_0;
457+
DROPTABLEtest.hash_rel_0;
458+
/* Table with which we are replacing partition must have exact same structure*/
459+
CREATETABLEtest.hash_rel_wrong(
460+
idINTEGERNOT NULL,
461+
valueINTEGER);
462+
SELECTpathman.replace_hash_partition('test.hash_rel_1','test.hash_rel_wrong');
463+
EXPLAIN (COSTS OFF)SELECT*FROMtest.hash_rel;
464+
451465
/*
452466
* Clean up
453467
*/

‎src/pl_hash_funcs.c‎

Lines changed: 76 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -15,13 +15,17 @@
1515

1616
#include"utils/builtins.h"
1717
#include"utils/typcache.h"
18+
#include"utils/lsyscache.h"
19+
#include"utils/builtins.h"
1820

1921

2022
/* Function declarations */
2123

2224
PG_FUNCTION_INFO_V1(create_hash_partitions_internal );
2325
PG_FUNCTION_INFO_V1(get_type_hash_func );
2426
PG_FUNCTION_INFO_V1(get_hash_part_idx );
27+
PG_FUNCTION_INFO_V1(build_hash_condition );
28+
PG_FUNCTION_INFO_V1(get_partition_hash );
2529

2630

2731
/*
@@ -79,3 +83,75 @@ get_hash_part_idx(PG_FUNCTION_ARGS)
7983

8084
PG_RETURN_UINT32(hash_to_part_index(value,part_count));
8185
}
86+
87+
/*
88+
* Build hash condition for a CHECK CONSTRAINT
89+
*/
90+
Datum
91+
build_hash_condition(PG_FUNCTION_ARGS)
92+
{
93+
TypeCacheEntry*tce;
94+
95+
Oidparent=PG_GETARG_OID(0);
96+
text*attname=PG_GETARG_TEXT_P(1);
97+
uint32partitions_count=PG_GETARG_UINT32(2);
98+
uint32partition_number=PG_GETARG_UINT32(3);
99+
Oidattyp;
100+
char*result;
101+
102+
if (partition_number >=partitions_count)
103+
elog(ERROR,
104+
"Partition number cannot exceed partitions count");
105+
106+
/* Get attribute type and its hash function oid */
107+
attyp=get_attribute_type(parent,text_to_cstring(attname), false);
108+
if (attyp==InvalidOid)
109+
elog(ERROR,
110+
"Relation '%s' has no attribute '%s'",
111+
get_rel_name(parent),
112+
text_to_cstring(attname));
113+
114+
tce=lookup_type_cache(attyp,TYPECACHE_HASH_PROC);
115+
116+
/* Create hash condition CSTRING */
117+
result=psprintf("%s.get_hash_part_idx(%s(%s), %u) = %u",
118+
get_namespace_name(get_pathman_schema()),
119+
get_func_name(tce->hash_proc),
120+
text_to_cstring(attname),
121+
partitions_count,
122+
partition_number);
123+
124+
PG_RETURN_TEXT_P(cstring_to_text(result));
125+
}
126+
127+
/*
128+
* Returns hash value for specified partition (0..N)
129+
*/
130+
Datum
131+
get_partition_hash(PG_FUNCTION_ARGS)
132+
{
133+
constPartRelationInfo*prel;
134+
Oidparent=PG_GETARG_OID(0);
135+
Oidpartition=PG_GETARG_OID(1);
136+
Oid*children;
137+
inti;
138+
139+
/* Validate partition type */
140+
prel=get_pathman_relation_info(parent);
141+
if (!prel||prel->parttype!=PT_HASH)
142+
elog(ERROR,
143+
"Relation '%s' isn't partitioned by hash",
144+
get_rel_name(parent));
145+
146+
/* Searching for partition */
147+
children=PrelGetChildrenArray(prel);
148+
for (i=0;i<prel->children_count;i++)
149+
if (children[i]==partition)
150+
PG_RETURN_UINT32(i);
151+
152+
/* If we get here then there is no such partition for specified parent */
153+
elog(ERROR,
154+
"Relation '%s' isn't a part of partitioned table '%s'",
155+
get_rel_name(parent),
156+
get_rel_name(partition));
157+
}

‎tests/python/partitioning_test.py‎

Lines changed: 36 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -353,6 +353,14 @@ def test_foreign_table(self):
353353
master.start()
354354
master.psql('postgres','create extension pg_pathman')
355355
master.psql('postgres','create extension postgres_fdw')
356+
357+
# RANGE partitioning test with FDW:
358+
# - create range partitioned table in master
359+
# - create foreign server
360+
# - create foreign table and insert some data into it
361+
# - attach foreign table to partitioned one
362+
# - try inserting data into foreign partition via parent
363+
# - drop partitions
356364
master.psql(
357365
'postgres',
358366
'''create table abc(id serial, name text);
@@ -405,6 +413,34 @@ def test_foreign_table(self):
405413
# Testing drop partitions (including foreign partitions)
406414
master.safe_psql('postgres','select drop_partitions(\'abc\')')
407415

416+
# HASH partitioning with FDW:
417+
# - create hash partitioned table in master
418+
# - create foreign table
419+
# - replace local partition with foreign one
420+
# - insert data
421+
# - drop partitions
422+
master.psql(
423+
'postgres',
424+
'''create table hash_test(id serial, name text);
425+
select create_hash_partitions('hash_test', 'id', 2)''')
426+
fserv.safe_psql('postgres','create table f_hash_test(id serial, name text)')
427+
428+
master.safe_psql(
429+
'postgres',
430+
'''import foreign schema public limit to (f_hash_test)
431+
from server fserv into public'''
432+
)
433+
master.safe_psql(
434+
'postgres',
435+
'select replace_hash_partition(\'hash_test_1\',\'f_hash_test\')')
436+
master.safe_psql('postgres','insert into hash_test select generate_series(1,10)')
437+
438+
self.assertEqual(
439+
master.safe_psql('postgres','select * from hash_test'),
440+
'1|\n2|\n5|\n6|\n8|\n9|\n3|\n4|\n7|\n10|\n'
441+
)
442+
master.safe_psql('postgres','select drop_partitions(\'hash_test\')')
443+
408444
deftest_parallel_nodes(self):
409445
"""Test parallel queries under partitions"""
410446

0 commit comments

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