1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201
|
SELECT setting::integer < 90600 AS pre_96
FROM pg_settings WHERE name = 'server_version_num';
pre_96
--------
f
(1 row)
/* Run tests as unprivileged user */
SET ROLE TO periods_unprivileged_user;
/*
* If anything we store as "name" is renamed, we need to update our catalogs or
* throw an error.
*/
/* periods */
CREATE TABLE rename_test(col1 text, col2 bigint, col3 time, s integer, e integer);
SELECT periods.add_period('rename_test', 'p', 's', 'e');
add_period
------------
t
(1 row)
TABLE periods.periods;
table_name | period_name | start_column_name | end_column_name | range_type | bounds_check_constraint
-------------+-------------+-------------------+-----------------+------------+-------------------------
rename_test | p | s | e | int4range | rename_test_p_check
(1 row)
ALTER TABLE rename_test RENAME s TO start;
ALTER TABLE rename_test RENAME e TO "end";
TABLE periods.periods;
table_name | period_name | start_column_name | end_column_name | range_type | bounds_check_constraint
-------------+-------------+-------------------+-----------------+------------+-------------------------
rename_test | p | start | end | int4range | rename_test_p_check
(1 row)
ALTER TABLE rename_test RENAME start TO "s < e";
TABLE periods.periods;
table_name | period_name | start_column_name | end_column_name | range_type | bounds_check_constraint
-------------+-------------+-------------------+-----------------+------------+-------------------------
rename_test | p | s < e | end | int4range | rename_test_p_check
(1 row)
ALTER TABLE rename_test RENAME "end" TO "embedded "" symbols";
TABLE periods.periods;
table_name | period_name | start_column_name | end_column_name | range_type | bounds_check_constraint
-------------+-------------+-------------------+--------------------+------------+-------------------------
rename_test | p | s < e | embedded " symbols | int4range | rename_test_p_check
(1 row)
ALTER TABLE rename_test RENAME CONSTRAINT rename_test_p_check TO start_before_end;
TABLE periods.periods;
table_name | period_name | start_column_name | end_column_name | range_type | bounds_check_constraint
-------------+-------------+-------------------+--------------------+------------+-------------------------
rename_test | p | s < e | embedded " symbols | int4range | start_before_end
(1 row)
/* system_time_periods */
SELECT periods.add_system_time_period('rename_test', excluded_column_names => ARRAY['col3']);
add_system_time_period
------------------------
t
(1 row)
TABLE periods.system_time_periods;
table_name | period_name | infinity_check_constraint | generated_always_trigger | write_history_trigger | truncate_trigger | excluded_column_names
-------------+-------------+--------------------------------------------+------------------------------------------+---------------------------------------+----------------------+-----------------------
rename_test | system_time | rename_test_system_time_end_infinity_check | rename_test_system_time_generated_always | rename_test_system_time_write_history | rename_test_truncate | {col3}
(1 row)
ALTER TABLE rename_test RENAME col3 TO "COLUMN3";
ERROR: cannot drop or rename column "col3" on table "rename_test" because it is excluded from SYSTEM VERSIONING
CONTEXT: PL/pgSQL function periods.rename_following() line 121 at RAISE
ALTER TABLE rename_test RENAME CONSTRAINT rename_test_system_time_end_infinity_check TO inf_check;
ALTER TRIGGER rename_test_system_time_generated_always ON rename_test RENAME TO generated_always;
ALTER TRIGGER rename_test_system_time_write_history ON rename_test RENAME TO write_history;
ALTER TRIGGER rename_test_truncate ON rename_test RENAME TO trunc;
TABLE periods.system_time_periods;
table_name | period_name | infinity_check_constraint | generated_always_trigger | write_history_trigger | truncate_trigger | excluded_column_names
-------------+-------------+---------------------------+--------------------------+-----------------------+------------------+-----------------------
rename_test | system_time | inf_check | generated_always | write_history | trunc | {col3}
(1 row)
/* for_portion_views */
ALTER TABLE rename_test ADD COLUMN id integer PRIMARY KEY;
SELECT periods.add_for_portion_view('rename_test', 'p');
add_for_portion_view
----------------------
t
(1 row)
TABLE periods.for_portion_views;
table_name | period_name | view_name | trigger_name
-------------+-------------+-------------------------------+------------------
rename_test | p | rename_test__for_portion_of_p | for_portion_of_p
(1 row)
ALTER TRIGGER for_portion_of_p ON rename_test__for_portion_of_p RENAME TO portion_trigger;
TABLE periods.for_portion_views;
table_name | period_name | view_name | trigger_name
-------------+-------------+-------------------------------+-----------------
rename_test | p | rename_test__for_portion_of_p | portion_trigger
(1 row)
SELECT periods.drop_for_portion_view('rename_test', 'p');
drop_for_portion_view
-----------------------
t
(1 row)
ALTER TABLE rename_test DROP COLUMN id;
/* unique_keys */
SELECT periods.add_unique_key('rename_test', ARRAY['col2', 'col1', 'col3'], 'p');
add_unique_key
------------------------------
rename_test_col2_col1_col3_p
(1 row)
TABLE periods.unique_keys;
key_name | table_name | column_names | period_name | unique_constraint | exclude_constraint
------------------------------+-------------+------------------+-------------+---------------------------------------------------------+-------------------------------------------
rename_test_col2_col1_col3_p | rename_test | {col2,col1,col3} | p | rename_test_col2_col1_col3_s < e_embedded " symbols_key | rename_test_col2_col1_col3_int4range_excl
(1 row)
ALTER TABLE rename_test RENAME COLUMN col1 TO "COLUMN1";
ALTER TABLE rename_test RENAME CONSTRAINT "rename_test_col2_col1_col3_s < e_embedded "" symbols_key" TO unconst;
ALTER TABLE rename_test RENAME CONSTRAINT rename_test_col2_col1_col3_int4range_excl TO exconst;
TABLE periods.unique_keys;
key_name | table_name | column_names | period_name | unique_constraint | exclude_constraint
------------------------------+-------------+---------------------+-------------+-------------------+--------------------
rename_test_col2_col1_col3_p | rename_test | {col2,COLUMN1,col3} | p | unconst | exconst
(1 row)
/* foreign_keys */
CREATE TABLE rename_test_ref (LIKE rename_test);
SELECT periods.add_period('rename_test_ref', 'q', 's < e', 'embedded " symbols');
add_period
------------
t
(1 row)
SELECT periods.add_foreign_key('rename_test_ref', ARRAY['col2', 'COLUMN1', 'col3'], 'q', 'rename_test_col2_col1_col3_p');
add_foreign_key
-------------------------------------
rename_test_ref_col2_COLUMN1_col3_q
(1 row)
TABLE periods.foreign_keys;
key_name | table_name | column_names | period_name | unique_key | match_type | delete_action | update_action | fk_insert_trigger | fk_update_trigger | uk_update_trigger | uk_delete_trigger
-------------------------------------+-----------------+---------------------+-------------+------------------------------+------------+---------------+---------------+-----------------------------------------------+-----------------------------------------------+-----------------------------------------------+-----------------------------------------------
rename_test_ref_col2_COLUMN1_col3_q | rename_test_ref | {col2,COLUMN1,col3} | q | rename_test_col2_col1_col3_p | SIMPLE | NO ACTION | NO ACTION | rename_test_ref_col2_COLUMN1_col3_q_fk_insert | rename_test_ref_col2_COLUMN1_col3_q_fk_update | rename_test_ref_col2_COLUMN1_col3_q_uk_update | rename_test_ref_col2_COLUMN1_col3_q_uk_delete
(1 row)
ALTER TABLE rename_test_ref RENAME COLUMN "COLUMN1" TO col1; -- fails
ERROR: cannot drop or rename column "COLUMN1" on table "rename_test_ref" because it is used in period foreign key "rename_test_ref_col2_COLUMN1_col3_q"
CONTEXT: PL/pgSQL function periods.rename_following() line 210 at RAISE
ALTER TRIGGER "rename_test_ref_col2_COLUMN1_col3_q_fk_insert" ON rename_test_ref RENAME TO fk_insert;
ERROR: cannot drop or rename trigger "rename_test_ref_col2_COLUMN1_col3_q_fk_insert" on table "rename_test_ref" because it is used in period foreign key "rename_test_ref_col2_COLUMN1_col3_q"
CONTEXT: PL/pgSQL function periods.rename_following() line 245 at RAISE
ALTER TRIGGER "rename_test_ref_col2_COLUMN1_col3_q_fk_update" ON rename_test_ref RENAME TO fk_update;
ERROR: cannot drop or rename trigger "rename_test_ref_col2_COLUMN1_col3_q_fk_update" on table "rename_test_ref" because it is used in period foreign key "rename_test_ref_col2_COLUMN1_col3_q"
CONTEXT: PL/pgSQL function periods.rename_following() line 245 at RAISE
ALTER TRIGGER "rename_test_ref_col2_COLUMN1_col3_q_uk_update" ON rename_test RENAME TO uk_update;
ERROR: cannot drop or rename trigger "rename_test_ref_col2_COLUMN1_col3_q_uk_update" on table "rename_test" because it is used in period foreign key "rename_test_ref_col2_COLUMN1_col3_q"
CONTEXT: PL/pgSQL function periods.rename_following() line 245 at RAISE
ALTER TRIGGER "rename_test_ref_col2_COLUMN1_col3_q_uk_delete" ON rename_test RENAME TO uk_delete;
ERROR: cannot drop or rename trigger "rename_test_ref_col2_COLUMN1_col3_q_uk_delete" on table "rename_test" because it is used in period foreign key "rename_test_ref_col2_COLUMN1_col3_q"
CONTEXT: PL/pgSQL function periods.rename_following() line 245 at RAISE
TABLE periods.foreign_keys;
key_name | table_name | column_names | period_name | unique_key | match_type | delete_action | update_action | fk_insert_trigger | fk_update_trigger | uk_update_trigger | uk_delete_trigger
-------------------------------------+-----------------+---------------------+-------------+------------------------------+------------+---------------+---------------+-----------------------------------------------+-----------------------------------------------+-----------------------------------------------+-----------------------------------------------
rename_test_ref_col2_COLUMN1_col3_q | rename_test_ref | {col2,COLUMN1,col3} | q | rename_test_col2_col1_col3_p | SIMPLE | NO ACTION | NO ACTION | rename_test_ref_col2_COLUMN1_col3_q_fk_insert | rename_test_ref_col2_COLUMN1_col3_q_fk_update | rename_test_ref_col2_COLUMN1_col3_q_uk_update | rename_test_ref_col2_COLUMN1_col3_q_uk_delete
(1 row)
DROP TABLE rename_test_ref;
/* system_versioning */
SELECT periods.add_system_versioning('rename_test');
NOTICE: history table "rename_test_history" created for "rename_test", be sure to index it properly
add_system_versioning
-----------------------
(1 row)
ALTER FUNCTION rename_test__as_of(timestamp with time zone) RENAME TO bumble_bee;
ERROR: cannot drop or rename function "public.rename_test__as_of(timestamp with time zone)" because it is used in SYSTEM VERSIONING for table "public.rename_test"
CONTEXT: PL/pgSQL function periods.health_checks() line 42 at RAISE
ALTER FUNCTION rename_test__between(timestamp with time zone, timestamp with time zone) RENAME TO bumble_bee;
ERROR: cannot drop or rename function "public.rename_test__between(timestamp with time zone,timestamp with time zone)" because it is used in SYSTEM VERSIONING for table "public.rename_test"
CONTEXT: PL/pgSQL function periods.health_checks() line 42 at RAISE
ALTER FUNCTION rename_test__between_symmetric(timestamp with time zone, timestamp with time zone) RENAME TO bumble_bee;
ERROR: cannot drop or rename function "public.rename_test__between_symmetric(timestamp with time zone,timestamp with time zone)" because it is used in SYSTEM VERSIONING for table "public.rename_test"
CONTEXT: PL/pgSQL function periods.health_checks() line 42 at RAISE
ALTER FUNCTION rename_test__from_to(timestamp with time zone, timestamp with time zone) RENAME TO bumble_bee;
ERROR: cannot drop or rename function "public.rename_test__from_to(timestamp with time zone,timestamp with time zone)" because it is used in SYSTEM VERSIONING for table "public.rename_test"
CONTEXT: PL/pgSQL function periods.health_checks() line 42 at RAISE
SELECT periods.drop_system_versioning('rename_test', purge => true);
drop_system_versioning
------------------------
t
(1 row)
DROP TABLE rename_test;
|