\set VERBOSITY terse
SET search_path = 'public';
CREATE EXTENSION pg_pathman;
CREATE SCHEMA test_column_type;
/*
 * RANGE partitioning.
 */
/* create new table (val int) */
CREATE TABLE test_column_type.test(val INT4 NOT NULL);
SELECT create_range_partitions('test_column_type.test', 'val', 1, 10, 10);
 create_range_partitions 
-------------------------
                      10
(1 row)

/* make sure that bounds and dispatch info has been cached */
SELECT * FROM test_column_type.test;
 val 
-----
(0 rows)

SELECT context, entries FROM pathman_cache_stats ORDER BY context;
         context         | entries 
-------------------------+---------
 maintenance             |       0
 partition bounds cache  |      10
 partition parents cache |      10
 partition status cache  |       3
(4 rows)

/* change column's type (should flush caches) */
SELECT get_partition_cooked_key('test_column_type.test'::REGCLASS);
                                               get_partition_cooked_key                                                
-----------------------------------------------------------------------------------------------------------------------
 {VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 1 :varoattno 1 :location 8}
(1 row)

SELECT get_partition_key_type('test_column_type.test'::REGCLASS);
 get_partition_key_type 
------------------------
 integer
(1 row)

ALTER TABLE test_column_type.test ALTER val TYPE NUMERIC;
/* check that expression has been built */
SELECT get_partition_key_type('test_column_type.test'::REGCLASS);
 get_partition_key_type 
------------------------
 numeric
(1 row)

SELECT get_partition_cooked_key('test_column_type.test'::REGCLASS);
                                                get_partition_cooked_key                                                 
-------------------------------------------------------------------------------------------------------------------------
 {VAR :varno 1 :varattno 1 :vartype 1700 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 1 :varoattno 1 :location 8}
(1 row)

/* make sure that everything works properly */
SELECT * FROM test_column_type.test;
 val 
-----
(0 rows)

SELECT context, entries FROM pathman_cache_stats ORDER BY context;
         context         | entries 
-------------------------+---------
 maintenance             |       0
 partition bounds cache  |      10
 partition parents cache |      10
 partition status cache  |       3
(4 rows)

/* check insert dispatching */
INSERT INTO test_column_type.test VALUES (1);
SELECT tableoid::regclass, * FROM test_column_type.test;
        tableoid         | val 
-------------------------+-----
 test_column_type.test_1 |   1
(1 row)

SELECT drop_partitions('test_column_type.test');
NOTICE:  1 rows copied from test_column_type.test_1
NOTICE:  0 rows copied from test_column_type.test_2
NOTICE:  0 rows copied from test_column_type.test_3
NOTICE:  0 rows copied from test_column_type.test_4
NOTICE:  0 rows copied from test_column_type.test_5
NOTICE:  0 rows copied from test_column_type.test_6
NOTICE:  0 rows copied from test_column_type.test_7
NOTICE:  0 rows copied from test_column_type.test_8
NOTICE:  0 rows copied from test_column_type.test_9
NOTICE:  0 rows copied from test_column_type.test_10
 drop_partitions 
-----------------
              10
(1 row)

DROP TABLE test_column_type.test CASCADE;
/*
 * HASH partitioning.
 */
/* create new table (id int, val int) */
CREATE TABLE test_column_type.test(id INT4 NOT NULL, val INT4);
SELECT create_hash_partitions('test_column_type.test', 'id', 5);
 create_hash_partitions 
------------------------
                      5
(1 row)

/* make sure that bounds and dispatch info has been cached */
SELECT * FROM test_column_type.test;
 id | val 
----+-----
(0 rows)

SELECT context, entries FROM pathman_cache_stats ORDER BY context;
         context         | entries 
-------------------------+---------
 maintenance             |       0
 partition bounds cache  |       5
 partition parents cache |       5
 partition status cache  |       3
(4 rows)

/* change column's type (should NOT work) */
ALTER TABLE test_column_type.test ALTER id TYPE NUMERIC;
ERROR:  cannot change type of column "id" of table "test" partitioned by HASH
/* make sure that everything works properly */
SELECT * FROM test_column_type.test;
 id | val 
----+-----
(0 rows)

SELECT context, entries FROM pathman_cache_stats ORDER BY context;
         context         | entries 
-------------------------+---------
 maintenance             |       0
 partition bounds cache  |       5
 partition parents cache |       5
 partition status cache  |       3
(4 rows)

/* change column's type (should flush caches) */
ALTER TABLE test_column_type.test ALTER val TYPE NUMERIC;
/* make sure that everything works properly */
SELECT * FROM test_column_type.test;
 id | val 
----+-----
(0 rows)

SELECT context, entries FROM pathman_cache_stats ORDER BY context;
         context         | entries 
-------------------------+---------
 maintenance             |       0
 partition bounds cache  |       5
 partition parents cache |       5
 partition status cache  |       3
(4 rows)

/* check insert dispatching */
INSERT INTO test_column_type.test VALUES (1);
SELECT tableoid::regclass, * FROM test_column_type.test;
        tableoid         | id | val 
-------------------------+----+-----
 test_column_type.test_0 |  1 |    
(1 row)

SELECT drop_partitions('test_column_type.test');
NOTICE:  1 rows copied from test_column_type.test_0
NOTICE:  0 rows copied from test_column_type.test_1
NOTICE:  0 rows copied from test_column_type.test_2
NOTICE:  0 rows copied from test_column_type.test_3
NOTICE:  0 rows copied from test_column_type.test_4
 drop_partitions 
-----------------
               5
(1 row)

DROP TABLE test_column_type.test CASCADE;
DROP SCHEMA test_column_type CASCADE;
DROP EXTENSION pg_pathman;