/* 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;

/* Table with which we are replacing partition must have exact same structure */

CREATE TABLE test.hash_rel_wrong(

id INTEGER NOT NULL,

value INTEGER);

SELECT pathman.replace_hash_partition('test.hash_rel_1', 'test.hash_rel_wrong');

ERROR: partition must have the exact same structure as parent

EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel;

QUERY PLAN

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

Append

-> Seq Scan on hash_rel_extern

-> Seq Scan on hash_rel_1

-> Seq Scan on hash_rel_2

(4 rows)

NOTICE: drop cascades to 2 other objects

NOTICE: 2 rows copied from test.hash_rel_extern

NOTICE: drop cascades to 51 other objects

-- v_atttype REGTYPE;

-- v_hashfunc REGPROC;

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

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

CREATE OR REPLACE FUNCTION @extschema@.replace_hash_partition(

old_partition REGCLASS,

new_partition REGCLASS)

RETURNS REGCLASS AS

$$

DECLARE

v_attname TEXT;

rel_persistence CHAR;

v_init_callback REGPROCEDURE;

v_parent_relid REGCLASS;

v_part_count INT;

v_part_num INT;

PERFORM @extschema@.validate_relname(old_partition);

PERFORM @extschema@.validate_relname(new_partition);

/* Parent relation */

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

/* Acquire lock on parent */

PERFORM @extschema@.lock_partitioned_relation(v_parent_relid);

/* Ignore temporary tables */

SELECT relpersistence FROM pg_catalog.pg_class

WHERE oid = 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;

/* Check that new partition has an equal structure as parent does */

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;

/* Get partitioning key */

v_attname := attname FROM @extschema@.pathman_config WHERE partrel = v_parent_relid;

IF v_attname IS NULL THEN

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

END IF;

/* Calculate partitions count and old partition's number */

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

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

/* Detach 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));

/* Attach new one */

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));

/* Fetch init_callback from 'params' table */

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

SELECT coalesce(init_callback, 0::REGPROCEDURE)

FROM stub_callback

LEFT JOIN @extschema@.pathman_config_params AS params

ON params.partrel = v_parent_relid

INTO v_init_callback;

PERFORM @extschema@.invoke_on_partition_created_callback(v_parent_relid,

new_partition,

v_init_callback);

/* Invalidate cache */

PERFORM @extschema@.on_update_partitions(v_parent_relid);

RETURN new_partition;

END

$$

LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION @extschema@.build_hash_condition(

parent_relid REGCLASS,

attname TEXT,

partitions_count INT,

partition_number INT)

RETURNS TEXT AS 'pg_pathman', 'build_hash_condition'

LANGUAGE C;

CREATE OR REPLACE FUNCTION @extschema@.get_partition_hash(

parent_relid REGCLASS,

partition REGCLASS)

RETURNS INT AS 'pg_pathman', 'get_partition_hash'

LANGUAGE C;

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');

\d+ test.hash_rel_0

\d+ test.hash_rel_extern

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

DROP TABLE test.hash_rel_0;

CREATE TABLE test.hash_rel_wrong(

id INTEGER NOT NULL,

value INTEGER);

SELECT pathman.replace_hash_partition('test.hash_rel_1', 'test.hash_rel_wrong');

EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel;

#include "utils/lsyscache.h"

#include "utils/builtins.h"

PG_FUNCTION_INFO_V1( build_hash_condition );

PG_FUNCTION_INFO_V1( get_partition_hash );

build_hash_condition(PG_FUNCTION_ARGS)

{

TypeCacheEntry *tce;

Oid parent = PG_GETARG_OID(0);

text *attname = PG_GETARG_TEXT_P(1);

uint32 partitions_count = PG_GETARG_UINT32(2);

uint32 partition_number = PG_GETARG_UINT32(3);

Oid attyp;

char *result;

if (partition_number >= partitions_count)

elog(ERROR,

"Partition number cannot exceed partitions count");

/* Get attribute type and its hash function oid */

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

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);

/* Create hash condition CSTRING */

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)

{

const PartRelationInfo *prel;

Oid parent = PG_GETARG_OID(0);

Oid partition = PG_GETARG_OID(1);

Oid *children;

int i;

/* Validate partition type */

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));

/* Searching for partition */

children = PrelGetChildrenArray(prel);

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

if (children[i] == partition)

PG_RETURN_UINT32(i);

/* If we get here then there is no such partition for specified parent */

elog(ERROR,

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

get_rel_name(parent),

get_rel_name(partition));

}

# RANGE partitioning test with FDW:

# - create range partitioned table in master

# - create foreign server

# - create foreign table and insert some data into it

# - attach foreign table to partitioned one

# - try inserting data into foreign partition via parent

# - drop partitions

# HASH partitioning with FDW:

# - create hash partitioned table in master

# - create foreign table

# - replace local partition with foreign one

# - insert data

# - drop partitions

master.psql(

'postgres',

'''create table hash_test(id serial, name text);

select create_hash_partitions('hash_test', 'id', 2)''')

fserv.safe_psql('postgres', 'create table f_hash_test(id serial, name text)')

master.safe_psql(

'postgres',

'''import foreign schema public limit to (f_hash_test)

)

master.safe_psql(

'postgres',

'select replace_hash_partition(\'hash_test_1\', \'f_hash_test\')')

master.safe_psql('postgres', 'insert into hash_test select generate_series(1,10)')

self.assertEqual(

master.safe_psql('postgres', 'select * from hash_test'),

'1|\n2|\n5|\n6|\n8|\n9|\n3|\n4|\n7|\n10|\n'

)

master.safe_psql('postgres', 'select drop_partitions(\'hash_test\')')