\set VERBOSITY terse
SET search_path = 'public';
CREATE EXTENSION pg_pathman;
CREATE SCHEMA callbacks;
/* callback #1 */
CREATE OR REPLACE FUNCTION callbacks.abc_on_part_created_callback(args JSONB)
RETURNS VOID AS $$
BEGIN
	RAISE WARNING 'callback arg: %', args::TEXT;
END
$$ language plpgsql;
/* callback #2 */
CREATE OR REPLACE FUNCTION public.dummy_cb(args JSONB)
RETURNS VOID AS $$
BEGIN
END
$$ language plpgsql;
CREATE TABLE callbacks.abc(a serial, b int);
SELECT create_range_partitions('callbacks.abc', 'a', 1, 100, 2);
 create_range_partitions 
-------------------------
                       2
(1 row)

SELECT set_init_callback('callbacks.abc', 'public.dummy_cb(jsonb)');
 set_init_callback 
-------------------
 
(1 row)

/* check that callback is schema-qualified */
SELECT init_callback FROM pathman_config_params
WHERE partrel = 'callbacks.abc'::REGCLASS;
     init_callback      
------------------------
 public.dummy_cb(jsonb)
(1 row)

/* reset callback */
SELECT set_init_callback('callbacks.abc');
 set_init_callback 
-------------------
 
(1 row)

/* should return NULL */
SELECT init_callback FROM pathman_config_params
WHERE partrel = 'callbacks.abc'::REGCLASS;
 init_callback 
---------------
 
(1 row)

SELECT set_init_callback('callbacks.abc',
						 'callbacks.abc_on_part_created_callback(jsonb)');
 set_init_callback 
-------------------
 
(1 row)

/* check that callback is schema-qualified */
SELECT init_callback FROM pathman_config_params
WHERE partrel = 'callbacks.abc'::REGCLASS;
                 init_callback                 
-----------------------------------------------
 callbacks.abc_on_part_created_callback(jsonb)
(1 row)

DROP TABLE callbacks.abc CASCADE;
NOTICE:  drop cascades to 3 other objects
/* set callback to be called on RANGE partitions */
CREATE TABLE callbacks.abc(a serial, b int);
SELECT create_range_partitions('callbacks.abc', 'a', 1, 100, 2);
 create_range_partitions 
-------------------------
                       2
(1 row)

SELECT set_init_callback('callbacks.abc',
						 'callbacks.abc_on_part_created_callback(jsonb)');
 set_init_callback 
-------------------
 
(1 row)

INSERT INTO callbacks.abc VALUES (123, 1);
INSERT INTO callbacks.abc VALUES (223, 1); /* show warning */
WARNING:  callback arg: {"parent": "abc", "parttype": "2", "partition": "abc_3", "range_max": "301", "range_min": "201", "parent_schema": "callbacks", "partition_schema": "callbacks"}
SELECT set_spawn_using_bgw('callbacks.abc', true);
 set_spawn_using_bgw 
---------------------
 
(1 row)

SELECT get_number_of_partitions('callbacks.abc');
 get_number_of_partitions 
--------------------------
                        3
(1 row)

INSERT INTO callbacks.abc VALUES (323, 1);
SELECT get_number_of_partitions('callbacks.abc'); /* +1 partition (created by BGW) */
 get_number_of_partitions 
--------------------------
                        4
(1 row)

SELECT set_spawn_using_bgw('callbacks.abc', false);
 set_spawn_using_bgw 
---------------------
 
(1 row)

SELECT append_range_partition('callbacks.abc');
WARNING:  callback arg: {"parent": "abc", "parttype": "2", "partition": "abc_5", "range_max": "501", "range_min": "401", "parent_schema": "callbacks", "partition_schema": "callbacks"}
 append_range_partition 
------------------------
 callbacks.abc_5
(1 row)

SELECT prepend_range_partition('callbacks.abc');
WARNING:  callback arg: {"parent": "abc", "parttype": "2", "partition": "abc_6", "range_max": "1", "range_min": "-99", "parent_schema": "callbacks", "partition_schema": "callbacks"}
 prepend_range_partition 
-------------------------
 callbacks.abc_6
(1 row)

SELECT add_range_partition('callbacks.abc', 501, 602);
WARNING:  callback arg: {"parent": "abc", "parttype": "2", "partition": "abc_7", "range_max": "602", "range_min": "501", "parent_schema": "callbacks", "partition_schema": "callbacks"}
 add_range_partition 
---------------------
 callbacks.abc_7
(1 row)

SELECT drop_partitions('callbacks.abc');
NOTICE:  0 rows copied from callbacks.abc_1
NOTICE:  1 rows copied from callbacks.abc_2
NOTICE:  1 rows copied from callbacks.abc_3
NOTICE:  1 rows copied from callbacks.abc_4
NOTICE:  0 rows copied from callbacks.abc_5
NOTICE:  0 rows copied from callbacks.abc_6
NOTICE:  0 rows copied from callbacks.abc_7
 drop_partitions 
-----------------
               7
(1 row)

/* set callback to be called on HASH partitions */
SELECT set_init_callback('callbacks.abc',
						 'callbacks.abc_on_part_created_callback(jsonb)');
 set_init_callback 
-------------------
 
(1 row)

SELECT create_hash_partitions('callbacks.abc', 'a', 5);
WARNING:  callback arg: {"parent": "abc", "parttype": "1", "partition": "abc_0", "parent_schema": "callbacks", "partition_schema": "callbacks"}
WARNING:  callback arg: {"parent": "abc", "parttype": "1", "partition": "abc_1", "parent_schema": "callbacks", "partition_schema": "callbacks"}
WARNING:  callback arg: {"parent": "abc", "parttype": "1", "partition": "abc_2", "parent_schema": "callbacks", "partition_schema": "callbacks"}
WARNING:  callback arg: {"parent": "abc", "parttype": "1", "partition": "abc_3", "parent_schema": "callbacks", "partition_schema": "callbacks"}
WARNING:  callback arg: {"parent": "abc", "parttype": "1", "partition": "abc_4", "parent_schema": "callbacks", "partition_schema": "callbacks"}
 create_hash_partitions 
------------------------
                      5
(1 row)

DROP TABLE callbacks.abc CASCADE;
NOTICE:  drop cascades to 5 other objects
/* test the temprary deletion of callback function */
CREATE TABLE callbacks.abc(a serial, b int);
SELECT set_init_callback('callbacks.abc',
						 'callbacks.abc_on_part_created_callback(jsonb)');
 set_init_callback 
-------------------
 
(1 row)

SELECT create_range_partitions('callbacks.abc', 'a', 1, 100, 2);
WARNING:  callback arg: {"parent": "abc", "parttype": "2", "partition": "abc_1", "range_max": "101", "range_min": "1", "parent_schema": "callbacks", "partition_schema": "callbacks"}
WARNING:  callback arg: {"parent": "abc", "parttype": "2", "partition": "abc_2", "range_max": "201", "range_min": "101", "parent_schema": "callbacks", "partition_schema": "callbacks"}
 create_range_partitions 
-------------------------
                       2
(1 row)

INSERT INTO callbacks.abc VALUES (201, 0); /* +1 new partition */
WARNING:  callback arg: {"parent": "abc", "parttype": "2", "partition": "abc_3", "range_max": "301", "range_min": "201", "parent_schema": "callbacks", "partition_schema": "callbacks"}
BEGIN;
DROP FUNCTION callbacks.abc_on_part_created_callback(jsonb);
INSERT INTO callbacks.abc VALUES (301, 0); /* +0 new partitions (ERROR) */
ERROR:  callback function "callbacks.abc_on_part_created_callback(jsonb)" does not exist
ROLLBACK;
INSERT INTO callbacks.abc VALUES (301, 0); /* +1 new partition */
WARNING:  callback arg: {"parent": "abc", "parttype": "2", "partition": "abc_5", "range_max": "401", "range_min": "301", "parent_schema": "callbacks", "partition_schema": "callbacks"}
DROP TABLE callbacks.abc CASCADE;
NOTICE:  drop cascades to 5 other objects
/* more complex test using rotation of tables */
CREATE TABLE callbacks.abc(a INT4 NOT NULL);
INSERT INTO callbacks.abc
	SELECT a FROM generate_series(1, 100) a;
SELECT create_range_partitions('callbacks.abc', 'a', 1, 10, 10);
 create_range_partitions 
-------------------------
                      10
(1 row)

CREATE OR REPLACE FUNCTION callbacks.rotation_callback(params jsonb)
RETURNS VOID AS
$$
DECLARE
	relation	regclass;
	parent_rel	regclass;
BEGIN
	parent_rel := concat(params->>'partition_schema', '.', params->>'parent')::regclass;

	-- drop "old" partitions
	FOR relation IN (SELECT partition FROM
						(SELECT partition, range_min::INT4 FROM pathman_partition_list
						 WHERE parent = parent_rel
						 ORDER BY range_min::INT4 DESC
						 OFFSET 4) t  -- remain 4 last partitions
					 ORDER BY range_min)
	LOOP
		RAISE NOTICE 'dropping partition %', relation;
		PERFORM drop_range_partition(relation);
	END LOOP;
END
$$ LANGUAGE plpgsql;
SELECT * FROM pathman_partition_list
WHERE parent = 'callbacks.abc'::REGCLASS
ORDER BY range_min::INT4;
    parent     |    partition     | parttype | expr | range_min | range_max 
---------------+------------------+----------+------+-----------+-----------
 callbacks.abc | callbacks.abc_1  |        2 | a    | 1         | 11
 callbacks.abc | callbacks.abc_2  |        2 | a    | 11        | 21
 callbacks.abc | callbacks.abc_3  |        2 | a    | 21        | 31
 callbacks.abc | callbacks.abc_4  |        2 | a    | 31        | 41
 callbacks.abc | callbacks.abc_5  |        2 | a    | 41        | 51
 callbacks.abc | callbacks.abc_6  |        2 | a    | 51        | 61
 callbacks.abc | callbacks.abc_7  |        2 | a    | 61        | 71
 callbacks.abc | callbacks.abc_8  |        2 | a    | 71        | 81
 callbacks.abc | callbacks.abc_9  |        2 | a    | 81        | 91
 callbacks.abc | callbacks.abc_10 |        2 | a    | 91        | 101
(10 rows)

SELECT set_init_callback('callbacks.abc',
						 'callbacks.rotation_callback(jsonb)');
 set_init_callback 
-------------------
 
(1 row)

INSERT INTO callbacks.abc VALUES (1000);
NOTICE:  dropping partition callbacks.abc_1
NOTICE:  dropping partition callbacks.abc_2
NOTICE:  dropping partition callbacks.abc_3
NOTICE:  dropping partition callbacks.abc_4
NOTICE:  dropping partition callbacks.abc_5
NOTICE:  dropping partition callbacks.abc_6
NOTICE:  dropping partition callbacks.abc_7
NOTICE:  dropping partition callbacks.abc_8
NOTICE:  dropping partition callbacks.abc_9
NOTICE:  dropping partition callbacks.abc_10
NOTICE:  dropping partition callbacks.abc_11
NOTICE:  dropping partition callbacks.abc_12
NOTICE:  dropping partition callbacks.abc_13
NOTICE:  dropping partition callbacks.abc_14
NOTICE:  dropping partition callbacks.abc_15
NOTICE:  dropping partition callbacks.abc_16
NOTICE:  dropping partition callbacks.abc_17
NOTICE:  dropping partition callbacks.abc_18
NOTICE:  dropping partition callbacks.abc_19
NOTICE:  dropping partition callbacks.abc_20
NOTICE:  dropping partition callbacks.abc_21
NOTICE:  dropping partition callbacks.abc_22
NOTICE:  dropping partition callbacks.abc_23
NOTICE:  dropping partition callbacks.abc_24
NOTICE:  dropping partition callbacks.abc_25
NOTICE:  dropping partition callbacks.abc_26
NOTICE:  dropping partition callbacks.abc_27
NOTICE:  dropping partition callbacks.abc_28
NOTICE:  dropping partition callbacks.abc_29
NOTICE:  dropping partition callbacks.abc_30
NOTICE:  dropping partition callbacks.abc_31
NOTICE:  dropping partition callbacks.abc_32
NOTICE:  dropping partition callbacks.abc_33
NOTICE:  dropping partition callbacks.abc_34
NOTICE:  dropping partition callbacks.abc_35
NOTICE:  dropping partition callbacks.abc_36
NOTICE:  dropping partition callbacks.abc_37
NOTICE:  dropping partition callbacks.abc_38
NOTICE:  dropping partition callbacks.abc_39
NOTICE:  dropping partition callbacks.abc_40
NOTICE:  dropping partition callbacks.abc_41
NOTICE:  dropping partition callbacks.abc_42
NOTICE:  dropping partition callbacks.abc_43
NOTICE:  dropping partition callbacks.abc_44
NOTICE:  dropping partition callbacks.abc_45
NOTICE:  dropping partition callbacks.abc_46
NOTICE:  dropping partition callbacks.abc_47
NOTICE:  dropping partition callbacks.abc_48
NOTICE:  dropping partition callbacks.abc_49
NOTICE:  dropping partition callbacks.abc_50
NOTICE:  dropping partition callbacks.abc_51
NOTICE:  dropping partition callbacks.abc_52
NOTICE:  dropping partition callbacks.abc_53
NOTICE:  dropping partition callbacks.abc_54
NOTICE:  dropping partition callbacks.abc_55
NOTICE:  dropping partition callbacks.abc_56
NOTICE:  dropping partition callbacks.abc_57
NOTICE:  dropping partition callbacks.abc_58
NOTICE:  dropping partition callbacks.abc_59
NOTICE:  dropping partition callbacks.abc_60
NOTICE:  dropping partition callbacks.abc_61
NOTICE:  dropping partition callbacks.abc_62
NOTICE:  dropping partition callbacks.abc_63
NOTICE:  dropping partition callbacks.abc_64
NOTICE:  dropping partition callbacks.abc_65
NOTICE:  dropping partition callbacks.abc_66
NOTICE:  dropping partition callbacks.abc_67
NOTICE:  dropping partition callbacks.abc_68
NOTICE:  dropping partition callbacks.abc_69
NOTICE:  dropping partition callbacks.abc_70
NOTICE:  dropping partition callbacks.abc_71
NOTICE:  dropping partition callbacks.abc_72
NOTICE:  dropping partition callbacks.abc_73
NOTICE:  dropping partition callbacks.abc_74
NOTICE:  dropping partition callbacks.abc_75
NOTICE:  dropping partition callbacks.abc_76
NOTICE:  dropping partition callbacks.abc_77
NOTICE:  dropping partition callbacks.abc_78
NOTICE:  dropping partition callbacks.abc_79
NOTICE:  dropping partition callbacks.abc_80
NOTICE:  dropping partition callbacks.abc_81
NOTICE:  dropping partition callbacks.abc_82
NOTICE:  dropping partition callbacks.abc_83
NOTICE:  dropping partition callbacks.abc_84
NOTICE:  dropping partition callbacks.abc_85
NOTICE:  dropping partition callbacks.abc_86
NOTICE:  dropping partition callbacks.abc_87
NOTICE:  dropping partition callbacks.abc_88
NOTICE:  dropping partition callbacks.abc_89
NOTICE:  dropping partition callbacks.abc_90
NOTICE:  dropping partition callbacks.abc_91
NOTICE:  dropping partition callbacks.abc_92
NOTICE:  dropping partition callbacks.abc_93
NOTICE:  dropping partition callbacks.abc_94
NOTICE:  dropping partition callbacks.abc_95
NOTICE:  dropping partition callbacks.abc_96
INSERT INTO callbacks.abc VALUES (1500);
NOTICE:  dropping partition callbacks.abc_97
NOTICE:  dropping partition callbacks.abc_98
NOTICE:  dropping partition callbacks.abc_99
NOTICE:  dropping partition callbacks.abc_100
NOTICE:  dropping partition callbacks.abc_101
NOTICE:  dropping partition callbacks.abc_102
NOTICE:  dropping partition callbacks.abc_103
NOTICE:  dropping partition callbacks.abc_104
NOTICE:  dropping partition callbacks.abc_105
NOTICE:  dropping partition callbacks.abc_106
NOTICE:  dropping partition callbacks.abc_107
NOTICE:  dropping partition callbacks.abc_108
NOTICE:  dropping partition callbacks.abc_109
NOTICE:  dropping partition callbacks.abc_110
NOTICE:  dropping partition callbacks.abc_111
NOTICE:  dropping partition callbacks.abc_112
NOTICE:  dropping partition callbacks.abc_113
NOTICE:  dropping partition callbacks.abc_114
NOTICE:  dropping partition callbacks.abc_115
NOTICE:  dropping partition callbacks.abc_116
NOTICE:  dropping partition callbacks.abc_117
NOTICE:  dropping partition callbacks.abc_118
NOTICE:  dropping partition callbacks.abc_119
NOTICE:  dropping partition callbacks.abc_120
NOTICE:  dropping partition callbacks.abc_121
NOTICE:  dropping partition callbacks.abc_122
NOTICE:  dropping partition callbacks.abc_123
NOTICE:  dropping partition callbacks.abc_124
NOTICE:  dropping partition callbacks.abc_125
NOTICE:  dropping partition callbacks.abc_126
NOTICE:  dropping partition callbacks.abc_127
NOTICE:  dropping partition callbacks.abc_128
NOTICE:  dropping partition callbacks.abc_129
NOTICE:  dropping partition callbacks.abc_130
NOTICE:  dropping partition callbacks.abc_131
NOTICE:  dropping partition callbacks.abc_132
NOTICE:  dropping partition callbacks.abc_133
NOTICE:  dropping partition callbacks.abc_134
NOTICE:  dropping partition callbacks.abc_135
NOTICE:  dropping partition callbacks.abc_136
NOTICE:  dropping partition callbacks.abc_137
NOTICE:  dropping partition callbacks.abc_138
NOTICE:  dropping partition callbacks.abc_139
NOTICE:  dropping partition callbacks.abc_140
NOTICE:  dropping partition callbacks.abc_141
NOTICE:  dropping partition callbacks.abc_142
NOTICE:  dropping partition callbacks.abc_143
NOTICE:  dropping partition callbacks.abc_144
NOTICE:  dropping partition callbacks.abc_145
NOTICE:  dropping partition callbacks.abc_146
SELECT * FROM pathman_partition_list
WHERE parent = 'callbacks.abc'::REGCLASS
ORDER BY range_min::INT4;
    parent     |     partition     | parttype | expr | range_min | range_max 
---------------+-------------------+----------+------+-----------+-----------
 callbacks.abc | callbacks.abc_147 |        2 | a    | 1461      | 1471
 callbacks.abc | callbacks.abc_148 |        2 | a    | 1471      | 1481
 callbacks.abc | callbacks.abc_149 |        2 | a    | 1481      | 1491
 callbacks.abc | callbacks.abc_150 |        2 | a    | 1491      | 1501
(4 rows)

DROP TABLE callbacks.abc CASCADE;
NOTICE:  drop cascades to 5 other objects
DROP SCHEMA callbacks CASCADE;
NOTICE:  drop cascades to 2 other objects
DROP EXTENSION pg_pathman CASCADE;