Skip to content

Commit 8c34e54

Browse files
zilderzilder
committed
pathman:
* tests updated
1 parent eb38a47 commit 8c34e54

File tree

3 files changed

+34
-25
lines changed

3 files changed

+34
-25
lines changed

‎contrib/pathman/sql/hash.sql

Lines changed: 6 additions & 19 deletions
Original file line numberDiff line numberDiff line change
@@ -41,11 +41,11 @@ BEGIN
4141

4242
-- EXECUTE format('CREATE TABLE %s_%s () INHERITS (%1$s)', relation, partnum);
4343
-- child_oid := relfilenode FROM pg_class WHERE relname = format('%s_%s', relation, partnum);
44-
INSERT INTO pg_pathman_hash_rels (parent, hash, child)
45-
VALUES (relation, partnum, format('%s_%s', relation, partnum));
44+
-- INSERT INTO pg_pathman_hash_rels (parent, hash, child)
45+
-- VALUES (relation, partnum, format('%s_%s', relation, partnum));
4646
END LOOP;
47-
INSERT INTO pg_pathman_rels (relname, attname, atttype, parttype)
48-
VALUES (relation, attribute, 1, 1);
47+
INSERT INTO pg_pathman_rels (relname, attname, parttype)
48+
VALUES (relation, attribute, 1);
4949

5050
/* Create triggers */
5151
PERFORM create_hash_insert_trigger(relation, attribute, partitions_count);
@@ -124,24 +124,11 @@ DECLARE
124124
BEGIN
125125
/* Drop trigger first */
126126
PERFORM drop_hash_triggers(relation);
127-
128-
relid := relfilenode FROM pg_class WHERE relname = relation;
129-
partitions_count := COUNT(*) FROM pg_pathman_hash_rels WHERE parent = relation;
130-
131127
DELETE FROM pg_pathman_rels WHERE relname = relation;
132-
DELETE FROM pg_pathman_hash_rels WHERE parent = relation;
133-
134-
IF partitions_count > 0 THEN
135-
RETURN;
136-
END IF;
137-
138-
FOR partnum IN 0..partitions_count-1
139-
LOOP
140-
EXECUTE format(q, relation, partnum);
141-
END LOOP;
128+
-- EXECUTE format('DROP TABLE %s CASCADE', relation);
142129

143130
/* Notify backend about changes */
144-
PERFORM pg_pathman_on_remove_partitions(relid);
131+
PERFORM pg_pathman_on_remove_partitions(relation::regclass::integer);
145132
END
146133
$$ LANGUAGE plpgsql;
147134

‎contrib/pathman/sql/pathman.sql

Lines changed: 16 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@ SELECT create_hash_partitions('hash_rel', 'value', 3);
88
CREATE TABLE num_range_rel (
99
id SERIAL PRIMARY KEY,
1010
txt TEXT);
11-
SELECT create_range_partitions('num_range_rel', 'id', 'num', 0, 1000, 3);
11+
SELECT create_range_partitions('num_range_rel', 'id', 0, 1000, 3);
1212

1313
INSERT INTO hash_rel VALUES (1, 1);
1414
INSERT INTO hash_rel VALUES (2, 2);
@@ -44,6 +44,21 @@ EXPLAIN (COSTS OFF) SELECT * FROM num_range_rel WHERE id >= 1000 AND id < 3000;
4444
EXPLAIN (COSTS OFF) SELECT * FROM num_range_rel WHERE id >= 1500 AND id < 2500;
4545
EXPLAIN (COSTS OFF) SELECT * FROM num_range_rel WHERE (id >= 500 AND id < 1500) OR (id > 2500);
4646

47+
/*
48+
* Test split and merge
49+
*/
50+
51+
/* Split first partition in half */
52+
SELECT split_range_partition('num_range_rel_1', 500);
53+
EXPLAIN (COSTS OFF) SELECT * FROM num_range_rel WHERE id BETWEEN 100 AND 700;
54+
55+
/* Merge two partitions into one */
56+
SELECT merge_range_partitions('num_range_rel_1', 'num_range_rel_' || currval('num_range_rel_seq'));
57+
EXPLAIN (COSTS OFF) SELECT * FROM num_range_rel WHERE id BETWEEN 100 AND 700;
58+
59+
/*
60+
* Clean up
61+
*/
4762
SELECT drop_hash_partitions('hash_rel');
4863
DROP TABLE hash_rel CASCADE;
4964

‎contrib/pathman/sql/range.sql

Lines changed: 12 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -299,6 +299,7 @@ BEGIN
299299
, v_new_partition);
300300

301301
/* Alter original partition */
302+
RAISE NOTICE 'Altering original partition...';
302303
v_cond := get_range_condition(v_attname, p_range[1], p_value - p_range[1]);
303304
EXECUTE format('ALTER TABLE %s DROP CONSTRAINT %s_%s_check'
304305
, p_partition
@@ -310,6 +311,8 @@ BEGIN
310311

311312
/* Tell backend to reload configuration */
312313
PERFORM pg_pathman_on_update_partitions(v_parent_relid::INTEGER);
314+
315+
RAISE NOTICE 'Done!';
313316
END
314317
$$
315318
LANGUAGE plpgsql;
@@ -362,6 +365,11 @@ BEGIN
362365

363366
EXECUTE format('SELECT merge_range_partitions_internal($1, $2 , $3, NULL::%s)', v_atttype)
364367
USING v_parent_relid1, v_part1_relid , v_part2_relid;
368+
369+
/* Tell backend to reload configuration */
370+
PERFORM pg_pathman_on_update_partitions(v_parent_relid1::INTEGER);
371+
372+
RAISE NOTICE 'Done!';
365373
END
366374
$$
367375
LANGUAGE plpgsql;
@@ -396,9 +404,6 @@ BEGIN
396404
*/
397405
p_range := get_partition_range(p_parent_relid, p_part1_relid) ||
398406
get_partition_range(p_parent_relid, p_part2_relid);
399-
RAISE NOTICE 'type: %', pg_typeof(p_range[1]);
400-
RAISE NOTICE 'min %, max %', pg_typeof(least(p_range[1], p_range[3])),
401-
pg_typeof(greatest(p_range[2], p_range[4]));
402407

403408
/* Check if ranges are adjacent */
404409
IF p_range[1] != p_range[4] AND p_range[2] != p_range[3] THEN
@@ -409,9 +414,9 @@ BEGIN
409414
v_cond := get_range_condition(v_attname
410415
, least(p_range[1], p_range[3])
411416
, greatest(p_range[2], p_range[4]) - least(p_range[1], p_range[3]));
412-
RAISE NOTICE 'cond: %', v_cond;
413417

414-
/* Alter first table */
418+
/* Alter first partition */
419+
RAISE NOTICE 'Altering first partition...';
415420
EXECUTE format('ALTER TABLE %s DROP CONSTRAINT %s_%s_check'
416421
, p_part1_relid::regclass::text
417422
, p_part1_relid::regclass::text
@@ -421,12 +426,14 @@ BEGIN
421426
, v_cond);
422427

423428
/* Copy data from second partition to the first one */
429+
RAISE NOTICE 'Copying data...';
424430
EXECUTE format('WITH part_data AS (DELETE FROM %s RETURNING *)
425431
INSERT INTO %s SELECT * FROM part_data'
426432
, p_part2_relid::regclass::text
427433
, p_part1_relid::regclass::text);
428434

429435
/* Remove second partition */
436+
RAISE NOTICE 'Dropping second partition...';
430437
EXECUTE format('DROP TABLE %s', p_part2_relid::regclass::text);
431438
END
432439
$$ LANGUAGE plpgsql;

0 commit comments

Comments
 (0)