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

Commit8541cbb

Browse files
committed
pathman: attach, detach, add and drop functions added
1 parentc17fe9b commit8541cbb

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 to6 other objects
561+
NOTICE: drop cascades to7 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-
SELECTappend_partition('range_rel');
674+
SELECTappend_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-
SELECTprepend_partition('range_rel');
682+
SELECTprepend_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-
CREATEOR REPLACE FUNCTION @extschema@.append_partition(
650+
CREATEOR REPLACE FUNCTION @extschema@.append_range_partition(
651651
p_relationTEXT)
652652
RETURNSTEXTAS
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-
CREATEOR REPLACE FUNCTION @extschema@.prepend_partition(p_relationTEXT)
723+
CREATEOR REPLACE FUNCTION @extschema@.prepend_range_partition(p_relationTEXT)
722724
RETURNSTEXTAS
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+
CREATEOR REPLACE FUNCTION @extschema@.add_range_partition(
799+
p_relationTEXT
800+
, p_start_value ANYELEMENT
801+
, p_end_value ANYELEMENT)
802+
RETURNSTEXTAS
803+
$$
804+
DECLARE
805+
v_part_nameTEXT;
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+
CREATEOR REPLACE FUNCTION @extschema@.drop_range_partition(
840+
p_partitionTEXT)
841+
RETURNSTEXTAS
842+
$$
843+
DECLARE
844+
v_part_nameTEXT;
845+
v_parentTEXT;
846+
v_countINTEGER;
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_inheritsWHERE inhrelid= p_partition::regclass::oid;
854+
855+
IF v_parent ISNULL 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+
CREATEOR REPLACE FUNCTION @extschema@.attach_range_partition(
880+
p_relationTEXT
881+
, p_partitionTEXT
882+
, p_start_value ANYELEMENT
883+
, p_end_value ANYELEMENT)
884+
RETURNSTEXTAS
885+
$$
886+
DECLARE
887+
v_attnameTEXT;
888+
v_condTEXT;
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 := attnameFROM @extschema@.pathman_configWHERE 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+
CREATEOR REPLACE FUNCTION @extschema@.attach_range_partition(
927+
p_partitionTEXT)
928+
RETURNSTEXTAS
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)
[8]ページ先頭
©2009-2025 Movatter.jp