Skip to content

Commit 8541cbb

Browse files
zilderzilder
committed
pathman: attach, detach, add and drop functions added
1 parent c17fe9b commit 8541cbb

File tree

5 files changed

+264
-40
lines changed

5 files changed

+264
-40
lines changed

‎contrib/pg_pathman/expected/pg_pathman.out

Lines changed: 82 additions & 20 deletions
Original file line numberDiff line numberDiff line change
@@ -438,38 +438,101 @@ NOTICE: Done!
438438
(1 row)
439439

440440
/* Append and prepend partitions */
441-
SELECT pathman.append_partition('test.num_range_rel');
441+
SELECT pathman.append_range_partition('test.num_range_rel');
442442
NOTICE: Appending new partition...
443443
NOTICE: Done!
444-
append_partition
445-
----------------------
444+
append_range_partition
445+
------------------------
446446
test.num_range_rel_6
447447
(1 row)
448448

449-
SELECT pathman.prepend_partition('test.num_range_rel');
449+
SELECT pathman.prepend_range_partition('test.num_range_rel');
450450
NOTICE: Prepending new partition...
451451
NOTICE: Done!
452-
prepend_partition
452+
prepend_range_partition
453+
-------------------------
454+
test.num_range_rel_7
455+
(1 row)
456+
457+
SELECT pathman.drop_range_partition('test.num_range_rel_7');
458+
drop_range_partition
453459
----------------------
454460
test.num_range_rel_7
455461
(1 row)
456462

457-
SELECT pathman.append_partition('test.range_rel');
463+
SELECT pathman.append_range_partition('test.range_rel');
458464
NOTICE: Appending new partition...
459465
NOTICE: Done!
460-
append_partition
461-
------------------
466+
append_range_partition
467+
------------------------
462468
test.range_rel_6
463469
(1 row)
464470

465-
SELECT pathman.prepend_partition('test.range_rel');
471+
SELECT pathman.prepend_range_partition('test.range_rel');
466472
NOTICE: Prepending new partition...
467473
NOTICE: Done!
468-
prepend_partition
469-
-------------------
474+
prepend_range_partition
475+
-------------------------
476+
test.range_rel_7
477+
(1 row)
478+
479+
EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt BETWEEN '2014-12-15' AND '2015-01-15';
480+
QUERY PLAN
481+
-------------------------------------------------------------------------------------
482+
Append
483+
-> Index Scan using range_rel_1_dt_idx on range_rel_1
484+
Index Cond: (dt <= 'Thu Jan 15 00:00:00 2015'::timestamp without time zone)
485+
(3 rows)
486+
487+
SELECT pathman.drop_range_partition('test.range_rel_7');
488+
drop_range_partition
489+
----------------------
470490
test.range_rel_7
471491
(1 row)
472492

493+
EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt BETWEEN '2014-12-15' AND '2015-01-15';
494+
QUERY PLAN
495+
-------------------------------------------------------------------------------------
496+
Append
497+
-> Index Scan using range_rel_1_dt_idx on range_rel_1
498+
Index Cond: (dt <= 'Thu Jan 15 00:00:00 2015'::timestamp without time zone)
499+
(3 rows)
500+
501+
SELECT pathman.add_range_partition('test.range_rel', '2014-12-01'::DATE, '2015-01-01'::DATE);
502+
NOTICE: Done!
503+
add_range_partition
504+
---------------------
505+
test.range_rel_8
506+
(1 row)
507+
508+
EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt BETWEEN '2014-12-15' AND '2015-01-15';
509+
QUERY PLAN
510+
-------------------------------------------------------------------------------------
511+
Append
512+
-> Index Scan using range_rel_8_dt_idx on range_rel_8
513+
Index Cond: (dt >= 'Mon Dec 15 00:00:00 2014'::timestamp without time zone)
514+
-> Index Scan using range_rel_1_dt_idx on range_rel_1
515+
Index Cond: (dt <= 'Thu Jan 15 00:00:00 2015'::timestamp without time zone)
516+
(5 rows)
517+
518+
CREATE TABLE test.range_rel_archive (LIKE test.range_rel INCLUDING ALL);
519+
SELECT pathman.attach_range_partition('test.range_rel', 'test.range_rel_archive', '2014-01-01'::DATE, '2014-12-01'::DATE);
520+
attach_range_partition
521+
------------------------
522+
test.range_rel_archive
523+
(1 row)
524+
525+
EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt BETWEEN '2014-11-15' AND '2015-01-15';
526+
QUERY PLAN
527+
-------------------------------------------------------------------------------------
528+
Append
529+
-> Index Scan using range_rel_archive_dt_idx on range_rel_archive
530+
Index Cond: (dt >= 'Sat Nov 15 00:00:00 2014'::timestamp without time zone)
531+
-> Seq Scan on range_rel_8
532+
-> Index Scan using range_rel_1_dt_idx on range_rel_1
533+
Index Cond: (dt <= 'Thu Jan 15 00:00:00 2015'::timestamp without time zone)
534+
(6 rows)
535+
473536
/*
474537
* Clean up
475538
*/
@@ -483,20 +546,19 @@ NOTICE: function test.hash_rel_hash_update_trigger_func() does not exist, skipp
483546

484547
DROP TABLE test.hash_rel CASCADE;
485548
SELECT pathman.drop_range_partitions('test.num_range_rel');
486-
NOTICE: 0 rows copied from test.num_range_rel_7
487549
NOTICE: 0 rows copied from test.num_range_rel_6
488550
NOTICE: 2 rows copied from test.num_range_rel_4
489551
NOTICE: 1000 rows copied from test.num_range_rel_3
490552
NOTICE: 1000 rows copied from test.num_range_rel_2
491553
NOTICE: 998 rows copied from test.num_range_rel_1
492554
drop_range_partitions
493555
-----------------------
494-
6
556+
5
495557
(1 row)
496558

497559
DROP TABLE test.num_range_rel CASCADE;
498560
DROP TABLE test.range_rel CASCADE;
499-
NOTICE: drop cascades to 6 other objects
561+
NOTICE: drop cascades to 7 other objects
500562
/* Test automatic partition creation */
501563
CREATE TABLE test.range_rel (
502564
id SERIAL PRIMARY KEY,
@@ -609,19 +671,19 @@ NOTICE: Done!
609671
{01-01-2010,03-01-2010}
610672
(1 row)
611673

612-
SELECT append_partition('range_rel');
674+
SELECT append_range_partition('range_rel');
613675
NOTICE: Appending new partition...
614676
NOTICE: Done!
615-
append_partition
616-
---------------------
677+
append_range_partition
678+
------------------------
617679
public.range_rel_14
618680
(1 row)
619681

620-
SELECT prepend_partition('range_rel');
682+
SELECT prepend_range_partition('range_rel');
621683
NOTICE: Prepending new partition...
622684
NOTICE: Done!
623-
prepend_partition
624-
---------------------
685+
prepend_range_partition
686+
-------------------------
625687
public.range_rel_15
626688
(1 row)
627689

‎contrib/pg_pathman/pl_funcs.c

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -125,6 +125,9 @@ find_or_create_range_partition(PG_FUNCTION_ARGS)
125125
/* Lock config before appending new partitions */
126126
LWLockAcquire(load_config_lock, LW_EXCLUSIVE);
127127

128+
/* Restrict concurrent partition creation */
129+
LWLockAcquire(edit_partitions_lock, LW_EXCLUSIVE);
130+
128131
/*
129132
* Check if someone else has already created partition.
130133
*/
@@ -144,7 +147,8 @@ find_or_create_range_partition(PG_FUNCTION_ARGS)
144147
// SPI_finish();
145148
// elog(WARNING, "Worker finished");
146149

147-
/* Release lock */
150+
/* Release locks */
151+
LWLockRelease(edit_partitions_lock);
148152
LWLockRelease(load_config_lock);
149153

150154
/* Repeat binary search */

‎contrib/pg_pathman/range.sql

Lines changed: 161 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -647,7 +647,7 @@ $$ LANGUAGE plpgsql;
647647
/*
648648
* Append new partition
649649
*/
650-
CREATE OR REPLACE FUNCTION @extschema@.append_partition(
650+
CREATE OR REPLACE FUNCTION @extschema@.append_range_partition(
651651
p_relation TEXT)
652652
RETURNS TEXT AS
653653
$$
@@ -671,11 +671,13 @@ BEGIN
671671
INTO v_part_name
672672
USING p_relation, v_atttype, v_interval;
673673

674+
/* Tell backend to reload configuration */
675+
PERFORM @extschema@.on_create_partitions(p_relation::regclass::oid);
676+
-- PERFORM @extschema@.on_update_partitions(p_relation::regclass::oid);
677+
674678
/* Release lock */
675679
PERFORM @extschema@.release_partitions_lock();
676680

677-
/* Tell backend to reload configuration */
678-
PERFORM @extschema@.on_create_partitions(p_relation::regclass::oid);
679681
RAISE NOTICE 'Done!';
680682
RETURN v_part_name;
681683

@@ -718,7 +720,7 @@ LANGUAGE plpgsql;
718720
/*
719721
* Prepend new partition
720722
*/
721-
CREATE OR REPLACE FUNCTION @extschema@.prepend_partition(p_relation TEXT)
723+
CREATE OR REPLACE FUNCTION @extschema@.prepend_range_partition(p_relation TEXT)
722724
RETURNS TEXT AS
723725
$$
724726
DECLARE
@@ -740,11 +742,13 @@ BEGIN
740742
INTO v_part_name
741743
USING p_relation, v_atttype, v_interval;
742744

745+
/* Tell backend to reload configuration */
746+
PERFORM @extschema@.on_create_partitions(p_relation::regclass::oid);
747+
-- PERFORM @extschema@.on_update_partitions(p_relation::regclass::oid);
748+
743749
/* Release lock */
744750
PERFORM @extschema@.release_partitions_lock();
745751

746-
/* Tell backend to reload configuration */
747-
PERFORM @extschema@.on_create_partitions(p_relation::regclass::oid);
748752
RAISE NOTICE 'Done!';
749753
RETURN v_part_name;
750754

@@ -788,6 +792,157 @@ $$
788792
LANGUAGE plpgsql;
789793

790794

795+
/*
796+
* Add new partition
797+
*/
798+
CREATE OR REPLACE FUNCTION @extschema@.add_range_partition(
799+
p_relation TEXT
800+
, p_start_value ANYELEMENT
801+
, p_end_value ANYELEMENT)
802+
RETURNS TEXT AS
803+
$$
804+
DECLARE
805+
v_part_name TEXT;
806+
BEGIN
807+
/* Prevent concurrent partition creation */
808+
PERFORM @extschema@.acquire_partitions_lock();
809+
810+
p_relation := @extschema@.validate_relname(p_relation);
811+
812+
/* TODO: check range overlap */
813+
814+
IF p_start_value >= p_end_value THEN
815+
RAISE EXCEPTION 'Failed to create partition: p_start_value is greater than p_end_value';
816+
END IF;
817+
818+
/* Create new partition */
819+
v_part_name := @extschema@.create_single_range_partition(p_relation, p_start_value, p_end_value);
820+
PERFORM @extschema@.on_update_partitions(p_relation::regclass::oid);
821+
822+
/* Release lock */
823+
PERFORM @extschema@.release_partitions_lock();
824+
825+
RAISE NOTICE 'Done!';
826+
RETURN v_part_name;
827+
828+
EXCEPTION WHEN others THEN
829+
RAISE EXCEPTION '% %', SQLERRM, SQLSTATE;
830+
PERFORM @extschema@.release_partitions_lock();
831+
END
832+
$$
833+
LANGUAGE plpgsql;
834+
835+
836+
/*
837+
* Drop range partition
838+
*/
839+
CREATE OR REPLACE FUNCTION @extschema@.drop_range_partition(
840+
p_partition TEXT)
841+
RETURNS TEXT AS
842+
$$
843+
DECLARE
844+
v_part_name TEXT;
845+
v_parent TEXT;
846+
v_count INTEGER;
847+
BEGIN
848+
/* Prevent concurrent partition management */
849+
PERFORM @extschema@.acquire_partitions_lock();
850+
851+
/* Parent table name */
852+
SELECT inhparent::regclass INTO v_parent
853+
FROM pg_inherits WHERE inhrelid = p_partition::regclass::oid;
854+
855+
IF v_parent IS NULL THEN
856+
RAISE EXCEPTION 'Partition ''%'' not found', p_partition;
857+
END IF;
858+
859+
/* Drop table and update cache */
860+
EXECUTE format('DROP TABLE %s', p_partition);
861+
PERFORM @extschema@.on_update_partitions(v_parent::regclass::oid);
862+
863+
/* Release lock */
864+
PERFORM @extschema@.release_partitions_lock();
865+
866+
RETURN p_partition;
867+
868+
EXCEPTION WHEN others THEN
869+
RAISE EXCEPTION '% %', SQLERRM, SQLSTATE;
870+
PERFORM @extschema@.release_partitions_lock();
871+
END
872+
$$
873+
LANGUAGE plpgsql;
874+
875+
876+
/*
877+
* Attach range partition
878+
*/
879+
CREATE OR REPLACE FUNCTION @extschema@.attach_range_partition(
880+
p_relation TEXT
881+
, p_partition TEXT
882+
, p_start_value ANYELEMENT
883+
, p_end_value ANYELEMENT)
884+
RETURNS TEXT AS
885+
$$
886+
DECLARE
887+
v_attname TEXT;
888+
v_cond TEXT;
889+
BEGIN
890+
/* Prevent concurrent partition management */
891+
PERFORM @extschema@.acquire_partitions_lock();
892+
893+
p_relation := @extschema@.validate_relname(p_relation);
894+
895+
/* Set inheritance */
896+
EXECUTE format('ALTER TABLE %s INHERIT %s'
897+
, p_partition
898+
, p_relation);
899+
900+
/* Set check constraint */
901+
v_attname := attname FROM @extschema@.pathman_config WHERE relname = p_relation;
902+
v_cond := @extschema@.get_range_condition(v_attname, p_start_value, p_end_value);
903+
EXECUTE format('ALTER TABLE %s ADD CONSTRAINT %s_check CHECK (%s)'
904+
, p_partition
905+
, @extschema@.get_schema_qualified_name(p_partition::regclass)
906+
, v_cond);
907+
908+
/* Invalidate cache */
909+
PERFORM @extschema@.on_update_partitions(p_relation::regclass::oid);
910+
911+
/* Release lock */
912+
PERFORM @extschema@.release_partitions_lock();
913+
RETURN p_partition;
914+
915+
EXCEPTION WHEN others THEN
916+
RAISE EXCEPTION '% %', SQLERRM, SQLSTATE;
917+
PERFORM @extschema@.release_partitions_lock();
918+
END
919+
$$
920+
LANGUAGE plpgsql;
921+
922+
923+
/*
924+
* Detach range partition
925+
*/
926+
CREATE OR REPLACE FUNCTION @extschema@.attach_range_partition(
927+
p_partition TEXT)
928+
RETURNS TEXT AS
929+
$$
930+
BEGIN
931+
/* Prevent concurrent partition management */
932+
PERFORM @extschema@.acquire_partitions_lock();
933+
934+
/* Release lock */
935+
PERFORM @extschema@.release_partitions_lock();
936+
RETURN p_partition;
937+
938+
EXCEPTION WHEN others THEN
939+
RAISE EXCEPTION '% %', SQLERRM, SQLSTATE;
940+
PERFORM @extschema@.release_partitions_lock();
941+
END
942+
$$
943+
LANGUAGE plpgsql;
944+
945+
791946
/*
792947
* Creates range partitioning insert trigger
793948
*/

0 commit comments

Comments
 (0)