123 | 456 | 789

(1 row)

/* Test replacing hash partition */

CREATE TABLE test.hash_rel_extern (LIKE test.hash_rel INCLUDING ALL);

SELECT pathman.replace_hash_partition('test.hash_rel_0', 'test.hash_rel_extern');

replace_hash_partition

------------------------

test.hash_rel_extern

(1 row)

\d+ test.hash_rel_0

Table "test.hash_rel_0"

Column | Type | Modifiers | Storage | Stats target | Description

--------+---------+------------------------------------------------------------+---------+--------------+-------------

id | integer | not null default nextval('test.hash_rel_id_seq'::regclass) | plain | |

value | integer | not null | plain | |

abc | integer | | plain | |

Indexes:

"hash_rel_0_pkey" PRIMARY KEY, btree (id)

Triggers:

hash_rel_upd_trig BEFORE UPDATE ON test.hash_rel_0 FOR EACH ROW EXECUTE PROCEDURE test.hash_rel_upd_trig_func()

\d+ test.hash_rel_extern

Table "test.hash_rel_extern"

Column | Type | Modifiers | Storage | Stats target | Description

--------+---------+------------------------------------------------------------+---------+--------------+-------------

id | integer | not null default nextval('test.hash_rel_id_seq'::regclass) | plain | |

value | integer | not null | plain | |

abc | integer | | plain | |

Indexes:

"hash_rel_extern_pkey" PRIMARY KEY, btree (id)

Check constraints:

"pathman_hash_rel_extern_2_check" CHECK (pathman.get_hash_part_idx(hashint4(value), 3) = 0)

Inherits: test.hash_rel

INSERT INTO test.hash_rel SELECT * FROM test.hash_rel_0;

DROP TABLE test.hash_rel_0;

EXPLAIN SELECT * FROM test.hash_rel;

QUERY PLAN

---------------------------------------------------------------------------------------------

Append (cost=10000000000.00..30000000032.44 rows=2044 width=12)

-> Seq Scan on hash_rel_extern (cost=10000000000.00..10000000030.40 rows=2040 width=12)

-> Seq Scan on hash_rel_1 (cost=10000000000.00..10000000001.02 rows=2 width=12)

-> Seq Scan on hash_rel_2 (cost=10000000000.00..10000000001.02 rows=2 width=12)

(4 rows)

/*

* Clean up

*/

SELECT pathman.drop_partitions('test.hash_rel');

NOTICE: drop cascades to 3 other objects

NOTICE: 2 rows copied from test.hash_rel_0

NOTICE: drop cascades to 2 other objects

NOTICE: 3 rows copied from test.hash_rel_1

NOTICE: 2 rows copied from test.hash_rel_2

NOTICE: 2 rows copied from test.hash_rel_extern

drop_partitions

-----------------

3

v_child_relnameTEXT;

v_plain_schemaTEXT;

v_plain_relnameTEXT;

v_atttypeREGTYPE;

v_hashfuncREGPROC;

v_init_callbackREGPROCEDURE;

PERFORM @extschema@.common_relation_checks(parent_relid, attribute);

v_atttype := @extschema@.get_attribute_type(parent_relid, attribute);

v_hashfunc := @extschema@.get_type_hash_func(v_atttype);

SELECT* INTO v_plain_schema, v_plain_relname

FROM @extschema@.get_plain_schema_and_relname(parent_relid);

parent_relid::TEXT,

@extschema@.get_rel_tablespace_name(parent_relid));

EXECUTE format('ALTER TABLE %s ADD CONSTRAINT %s

CHECK (@extschema@.get_hash_part_idx(%s(%s), %s) = %s)',

EXECUTE format('ALTER TABLE %s ADD CONSTRAINT %s CHECK (%s)',

v_child_relname,

@extschema@.build_check_constraint_name(v_child_relname::REGCLASS,

@extschema@.build_check_constraint_name(v_child_relname,

attribute),

v_hashfunc::TEXT,

attribute,

partitions_count,

partnum);

@extschema@.build_hash_condition(v_child_relname,

attribute,

partitions_count,

partnum));

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

$$ LANGUAGE plpgsql

SET client_min_messages= WARNING;

CREATEOR REPLACE FUNCTION @extschema@.replace_hash_partition(

old_partitionREGCLASS,

new_partitionREGCLASS)

RETURNS REGCLASSAS

$$

DECLARE

v_attnameTEXT;

rel_persistenceCHAR;

v_init_callbackREGPROCEDURE;

v_parent_relidREGCLASS;

v_part_countINT;

v_part_numINT;

PERFORM @extschema@.validate_relname(old_partition);

PERFORM @extschema@.validate_relname(new_partition);

v_parent_relid := @extschema@.get_parent_of_partition(old_partition);

PERFORM @extschema@.lock_partitioned_relation(v_parent_relid);

SELECT relpersistenceFROMpg_catalog.pg_class

WHEREoid= new_partition INTO rel_persistence;

IF rel_persistence='t'::CHAR THEN

RAISE EXCEPTION'temporary table "%" cannot be used as a partition',

new_partition::TEXT;

END IF;

IF NOT @extschema@.validate_relations_equality(v_parent_relid, new_partition) THEN

RAISE EXCEPTION'partition must have the exact same structure as parent';

END IF;

v_attname := attnameFROM @extschema@.pathman_configWHERE partrel= v_parent_relid;

IF v_attname ISNULL THEN

RAISE EXCEPTION'table "%" is not partitioned', v_parent_relid::TEXT;

END IF;

v_part_count :=count(*)FROM @extschema@.pathman_partition_listWHERE parent= v_parent_relid;

v_part_num := @extschema@.get_partition_hash(v_parent_relid, old_partition);

EXECUTE format('ALTER TABLE %s NO INHERIT %s', old_partition, v_parent_relid);

EXECUTE format('ALTER TABLE %s DROP CONSTRAINT IF EXISTS %s',

old_partition,

@extschema@.build_check_constraint_name(old_partition::REGCLASS,

v_attname));

EXECUTE format('ALTER TABLE %s INHERIT %s', new_partition, v_parent_relid);

EXECUTE format('ALTER TABLE %s ADD CONSTRAINT %s CHECK (%s)',

new_partition,

@extschema@.build_check_constraint_name(new_partition::regclass,

v_attname),

@extschema@.build_hash_condition(new_partition::regclass,

v_attname,

v_part_count,

v_part_num));

WITH stub_callback(stub)as (values (0))

SELECT coalesce(init_callback,0::REGPROCEDURE)

FROM stub_callback

LEFT JOIN @extschema@.pathman_config_paramsAS params

ONparams.partrel= v_parent_relid

INTO v_init_callback;

PERFORM @extschema@.invoke_on_partition_created_callback(v_parent_relid,

new_partition,

v_init_callback);

PERFORM @extschema@.on_update_partitions(v_parent_relid);

RETURN new_partition;

END

$$

LANGUAGE plpgsql;

init_callbackREGPROCEDURE)

RETURNS VOIDAS'pg_pathman','invoke_on_partition_created_callback'

LANGUAGE C;

CREATEOR REPLACE FUNCTION @extschema@.build_hash_condition(

parent_relidREGCLASS,

attnameTEXT,

partitions_countINT,

partition_numberINT)

RETURNSTEXTAS'pg_pathman','build_hash_condition'

LANGUAGE C;

CREATEOR REPLACE FUNCTION @extschema@.get_partition_hash(

parent_relidREGCLASS,

partitionREGCLASS)

RETURNSINTAS'pg_pathman','get_partition_hash'

LANGUAGE C;

INSERT INTOtest.hash_rel (id, value, abc)VALUES (123,456,789);

SELECT*FROMtest.hash_relWHERE id=123;

CREATETABLEtest.hash_rel_extern (LIKEtest.hash_rel INCLUDING ALL);

SELECTpathman.replace_hash_partition('test.hash_rel_0','test.hash_rel_extern');

\d+test.hash_rel_0

\d+test.hash_rel_extern

INSERT INTOtest.hash_relSELECT*FROMtest.hash_rel_0;

DROPTABLEtest.hash_rel_0;

EXPLAINSELECT*FROMtest.hash_rel;

get_attribute_type_pl(PG_FUNCTION_ARGS)

{

Oidrelid=PG_GETARG_OID(0);

text*attname=PG_GETARG_TEXT_P(1);

char*attname=text_to_cstring(PG_GETARG_TEXT_P(1));

Oidresult;

HeapTupletp;

tp=SearchSysCacheAttName(relid,text_to_cstring(attname));

if (HeapTupleIsValid(tp))

if ((result=get_attribute_type(relid,attname))!=InvalidOid)

{

Form_pg_attributeatt_tup= (Form_pg_attribute)GETSTRUCT(tp);

result=att_tup->atttypid;

ReleaseSysCache(tp);

PG_RETURN_OID(result);

}

{

elog(ERROR,"Cannot find type name for attribute \"%s\" "

"of relation \"%s\"",

text_to_cstring(attname),get_rel_name_or_relid(relid));

attname,get_rel_name_or_relid(relid));

}

PG_RETURN_NULL();/* keep compiler happy */

}

PG_FUNCTION_INFO_V1(get_type_hash_func );

PG_FUNCTION_INFO_V1(get_hash_part_idx );

PG_FUNCTION_INFO_V1(build_hash_condition );

PG_FUNCTION_INFO_V1(get_partition_hash );

PG_RETURN_UINT32(hash_to_part_index(value,part_count));

}

build_hash_condition(PG_FUNCTION_ARGS)

{

TypeCacheEntry*tce;

Oidparent=PG_GETARG_OID(0);

text*attname=PG_GETARG_TEXT_P(1);

uint32partitions_count=PG_GETARG_UINT32(2);

uint32partition_number=PG_GETARG_UINT32(3);

Oidattyp;

char*result;

if (partition_number >=partitions_count)

elog(ERROR,

"Partition number cannot exceed partitions count");

attyp=get_attribute_type(parent,text_to_cstring(attname));

if (attyp==InvalidOid)

elog(ERROR,

"Relation '%s' has no attribute '%s'",

get_rel_name(parent),

text_to_cstring(attname));

tce=lookup_type_cache(attyp,TYPECACHE_HASH_PROC);

result=psprintf("%s.get_hash_part_idx(%s(%s), %u) = %u",

get_namespace_name(get_pathman_schema()),

get_func_name(tce->hash_proc),

text_to_cstring(attname),

partitions_count,

partition_number);

PG_RETURN_TEXT_P(cstring_to_text(result));

}

get_partition_hash(PG_FUNCTION_ARGS)

{

constPartRelationInfo*prel;

Oidparent=PG_GETARG_OID(0);

Oidpartition=PG_GETARG_OID(1);

Oid*children;

inti;

prel=get_pathman_relation_info(parent);

if (!prel||prel->parttype!=PT_HASH)

elog(ERROR,

"Relation '%s' isn't partitioned by hash",

get_rel_name(parent));

children=PrelGetChildrenArray(prel);

for (i=0;i<prel->children_count;i++)

if (children[i]==partition)

PG_RETURN_UINT32(i);

elog(ERROR,

"Relation '%s' isn't a part of partitioned table '%s'",

get_rel_name(parent),

get_rel_name(partition));

}