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
|
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;
/* Make sure nobody drops the objects we keep track of in our catalogs. */
CREATE TYPE integerrange AS RANGE (SUBTYPE = integer);
CREATE TABLE dp (
id bigint,
s integer,
e integer,
x boolean
);
/* periods */
SELECT periods.add_period('dp', 'p', 's', 'e', 'integerrange');
add_period
------------
t
(1 row)
DROP TYPE integerrange;
ERROR: cannot drop rangetype "public.integerrange" because it is used in period "p" on table "dp"
CONTEXT: PL/pgSQL function periods.drop_protection() line 56 at RAISE
/* system_time_periods */
SELECT periods.add_system_time_period('dp', excluded_column_names => ARRAY['x']);
add_system_time_period
------------------------
t
(1 row)
ALTER TABLE dp DROP COLUMN x; -- fails
ERROR: cannot drop or rename column "x" on table "dp" because it is excluded from SYSTEM VERSIONING
CONTEXT: PL/pgSQL function periods.drop_protection() line 124 at RAISE
ALTER TABLE dp DROP CONSTRAINT dp_system_time_end_infinity_check; -- fails
ERROR: cannot drop constraint "dp_system_time_end_infinity_check" on table "dp" because it is used in SYSTEM_TIME period
CONTEXT: PL/pgSQL function periods.drop_protection() line 72 at RAISE
DROP TRIGGER dp_system_time_generated_always ON dp; -- fails
ERROR: cannot drop trigger "dp_system_time_generated_always" on table "dp" because it is used in SYSTEM_TIME period
CONTEXT: PL/pgSQL function periods.drop_protection() line 84 at RAISE
DROP TRIGGER dp_system_time_write_history ON dp; -- fails
ERROR: cannot drop trigger "dp_system_time_write_history" on table "dp" because it is used in SYSTEM_TIME period
CONTEXT: PL/pgSQL function periods.drop_protection() line 96 at RAISE
DROP TRIGGER dp_truncate ON dp; -- fails
ERROR: cannot drop trigger "dp_truncate" on table "dp" because it is used in SYSTEM_TIME period
CONTEXT: PL/pgSQL function periods.drop_protection() line 108 at RAISE
/* for_portion_views */
ALTER TABLE dp ADD CONSTRAINT dp_pkey PRIMARY KEY (id);
SELECT periods.add_for_portion_view('dp', 'p');
add_for_portion_view
----------------------
t
(1 row)
DROP VIEW dp__for_portion_of_p;
ERROR: cannot drop view "public.dp__for_portion_of_p", call "periods.drop_for_portion_view()" instead
CONTEXT: PL/pgSQL function periods.drop_protection() line 141 at RAISE
DROP TRIGGER for_portion_of_p ON dp__for_portion_of_p;
ERROR: cannot drop trigger "for_portion_of_p" on view "dp__for_portion_of_p" because it is used in FOR PORTION OF view for period "p" on table "dp"
CONTEXT: PL/pgSQL function periods.drop_protection() line 153 at RAISE
ALTER TABLE dp DROP CONSTRAINT dp_pkey;
ERROR: cannot drop primary key on table "dp" because it has a FOR PORTION OF view for period "p"
CONTEXT: PL/pgSQL function periods.drop_protection() line 165 at RAISE
SELECT periods.drop_for_portion_view('dp', 'p');
drop_for_portion_view
-----------------------
t
(1 row)
ALTER TABLE dp DROP CONSTRAINT dp_pkey;
/* unique_keys */
ALTER TABLE dp
ADD CONSTRAINT u UNIQUE (id, s, e),
ADD CONSTRAINT x EXCLUDE USING gist (id WITH =, integerrange(s, e, '[)') WITH &&);
SELECT periods.add_unique_key('dp', ARRAY['id'], 'p', 'k', 'u', 'x');
add_unique_key
----------------
k
(1 row)
ALTER TABLE dp DROP CONSTRAINT u; -- fails
ERROR: cannot drop constraint "u" on table "dp" because it is used in period unique key "k"
CONTEXT: PL/pgSQL function periods.drop_protection() line 186 at RAISE
ALTER TABLE dp DROP CONSTRAINT x; -- fails
ERROR: cannot drop constraint "x" on table "dp" because it is used in period unique key "k"
CONTEXT: PL/pgSQL function periods.drop_protection() line 197 at RAISE
ALTER TABLE dp DROP CONSTRAINT dp_p_check; -- fails
/* foreign_keys */
CREATE TABLE dp_ref (LIKE dp);
SELECT periods.add_period('dp_ref', 'p', 's', 'e', 'integerrange');
add_period
------------
t
(1 row)
SELECT periods.add_foreign_key('dp_ref', ARRAY['id'], 'p', 'k', key_name => 'f');
add_foreign_key
-----------------
f
(1 row)
DROP TRIGGER f_fk_insert ON dp_ref; -- fails
ERROR: cannot drop trigger "f_fk_insert" on table "dp_ref" because it is used in period foreign key "f"
CONTEXT: PL/pgSQL function periods.drop_protection() line 213 at RAISE
DROP TRIGGER f_fk_update ON dp_ref; -- fails
ERROR: cannot drop trigger "f_fk_update" on table "dp_ref" because it is used in period foreign key "f"
CONTEXT: PL/pgSQL function periods.drop_protection() line 224 at RAISE
DROP TRIGGER f_uk_update ON dp; -- fails
ERROR: cannot drop trigger "f_uk_update" on table "dp" because it is used in period foreign key "f"
CONTEXT: PL/pgSQL function periods.drop_protection() line 236 at RAISE
DROP TRIGGER f_uk_delete ON dp; -- fails
ERROR: cannot drop trigger "f_uk_delete" on table "dp" because it is used in period foreign key "f"
CONTEXT: PL/pgSQL function periods.drop_protection() line 248 at RAISE
SELECT periods.drop_foreign_key('dp_ref', 'f');
drop_foreign_key
------------------
t
(1 row)
DROP TABLE dp_ref;
/* system_versioning */
SELECT periods.add_system_versioning('dp');
NOTICE: history table "dp_history" created for "dp", be sure to index it properly
add_system_versioning
-----------------------
(1 row)
-- Note: The history table is protected by the history view and the history
-- view is protected by the temporal functions.
DROP TABLE dp_history CASCADE;
NOTICE: drop cascades to 5 other objects
DETAIL: drop cascades to view dp_with_history
drop cascades to function dp__as_of(timestamp with time zone)
drop cascades to function dp__between(timestamp with time zone,timestamp with time zone)
drop cascades to function dp__between_symmetric(timestamp with time zone,timestamp with time zone)
drop cascades to function dp__from_to(timestamp with time zone,timestamp with time zone)
ERROR: cannot drop table "public.dp_history" because it is used in SYSTEM VERSIONING for table "dp"
CONTEXT: PL/pgSQL function periods.drop_protection() line 264 at RAISE
DROP VIEW dp_with_history CASCADE;
NOTICE: drop cascades to 4 other objects
DETAIL: drop cascades to function dp__as_of(timestamp with time zone)
drop cascades to function dp__between(timestamp with time zone,timestamp with time zone)
drop cascades to function dp__between_symmetric(timestamp with time zone,timestamp with time zone)
drop cascades to function dp__from_to(timestamp with time zone,timestamp with time zone)
ERROR: cannot drop view "public.dp_with_history" because it is used in SYSTEM VERSIONING for table "dp"
CONTEXT: PL/pgSQL function periods.drop_protection() line 276 at RAISE
DROP FUNCTION dp__as_of(timestamp with time zone);
ERROR: cannot drop function "public.dp__as_of(timestamp with time zone)" because it is used in SYSTEM VERSIONING for table "dp"
CONTEXT: PL/pgSQL function periods.drop_protection() line 288 at RAISE
DROP FUNCTION dp__between(timestamp with time zone,timestamp with time zone);
ERROR: cannot drop function "public.dp__between(timestamp with time zone,timestamp with time zone)" because it is used in SYSTEM VERSIONING for table "dp"
CONTEXT: PL/pgSQL function periods.drop_protection() line 288 at RAISE
DROP FUNCTION dp__between_symmetric(timestamp with time zone,timestamp with time zone);
ERROR: cannot drop function "public.dp__between_symmetric(timestamp with time zone,timestamp with time zone)" because it is used in SYSTEM VERSIONING for table "dp"
CONTEXT: PL/pgSQL function periods.drop_protection() line 288 at RAISE
DROP FUNCTION dp__from_to(timestamp with time zone,timestamp with time zone);
ERROR: cannot drop function "public.dp__from_to(timestamp with time zone,timestamp with time zone)" because it is used in SYSTEM VERSIONING for table "dp"
CONTEXT: PL/pgSQL function periods.drop_protection() line 288 at RAISE
SELECT periods.drop_system_versioning('dp', purge => true);
drop_system_versioning
------------------------
t
(1 row)
DROP TABLE dp;
DROP TYPE integerrange;
|
