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
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
|
/*
* An alternative file for pre-v12 is necessary because LEAST() and GREATEST()
* were not constant folded. It was actually while writing this extension that
* the lack of optimization was noticed, and subsequently fixed.
*
* https://www.postgresql.org/message-id/flat/c6e8504c-4c43-35fa-6c8f-3c0b80a912cc%402ndquadrant.com
*/
SELECT setting::integer < 120000 AS pre_12
FROM pg_settings WHERE name = 'server_version_num';
pre_12
--------
t
(1 row)
/* Run tests as unprivileged user */
SET ROLE TO periods_unprivileged_user;
/* Basic SYSTEM VERSIONING */
CREATE TABLE sysver (val text, flap boolean);
SELECT periods.add_system_time_period('sysver', excluded_column_names => ARRAY['flap']);
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
------------+-------------+---------------------------------------+-------------------------------------+----------------------------------+------------------+-----------------------
sysver | system_time | sysver_system_time_end_infinity_check | sysver_system_time_generated_always | sysver_system_time_write_history | sysver_truncate | {flap}
(1 row)
TABLE periods.system_versioning;
table_name | period_name | history_table_name | view_name | func_as_of | func_between | func_between_symmetric | func_from_to
------------+-------------+--------------------+-----------+------------+--------------+------------------------+--------------
(0 rows)
SELECT periods.add_system_versioning('sysver',
history_table_name => 'custom_history_name',
view_name => 'custom_view_name',
function_as_of_name => 'custom_as_of',
function_between_name => 'custom_between',
function_between_symmetric_name => 'custom_between_symmetric',
function_from_to_name => 'custom_from_to');
NOTICE: history table "custom_history_name" created for "sysver", be sure to index it properly
add_system_versioning
-----------------------
(1 row)
TABLE periods.system_versioning;
table_name | period_name | history_table_name | view_name | func_as_of | func_between | func_between_symmetric | func_from_to
------------+-------------+---------------------+------------------+-----------------------------------------------+--------------------------------------------------------------------------+------------------------------------------------------------------------------------+--------------------------------------------------------------------------
sysver | system_time | custom_history_name | custom_view_name | public.custom_as_of(timestamp with time zone) | public.custom_between(timestamp with time zone,timestamp with time zone) | public.custom_between_symmetric(timestamp with time zone,timestamp with time zone) | public.custom_from_to(timestamp with time zone,timestamp with time zone)
(1 row)
SELECT periods.drop_system_versioning('sysver', drop_behavior => 'CASCADE');
drop_system_versioning
------------------------
t
(1 row)
DROP TABLE custom_history_name;
SELECT periods.add_system_versioning('sysver');
NOTICE: history table "sysver_history" created for "sysver", be sure to index it properly
add_system_versioning
-----------------------
(1 row)
TABLE periods.system_versioning;
table_name | period_name | history_table_name | view_name | func_as_of | func_between | func_between_symmetric | func_from_to
------------+-------------+--------------------+---------------------+------------------------------------------------+---------------------------------------------------------------------------+-------------------------------------------------------------------------------------+---------------------------------------------------------------------------
sysver | system_time | sysver_history | sysver_with_history | public.sysver__as_of(timestamp with time zone) | public.sysver__between(timestamp with time zone,timestamp with time zone) | public.sysver__between_symmetric(timestamp with time zone,timestamp with time zone) | public.sysver__from_to(timestamp with time zone,timestamp with time zone)
(1 row)
INSERT INTO sysver (val, flap) VALUES ('hello', false);
SELECT val FROM sysver;
val
-------
hello
(1 row)
SELECT val FROM sysver_history ORDER BY system_time_start;
val
-----
(0 rows)
SELECT transaction_timestamp() AS ts1 \gset
UPDATE sysver SET val = 'world';
SELECT val FROM sysver;
val
-------
world
(1 row)
SELECT val FROM sysver_history ORDER BY system_time_start;
val
-------
hello
(1 row)
UPDATE sysver SET flap = not flap;
UPDATE sysver SET flap = not flap;
UPDATE sysver SET flap = not flap;
UPDATE sysver SET flap = not flap;
UPDATE sysver SET flap = not flap;
SELECT val FROM sysver;
val
-------
world
(1 row)
SELECT val FROM sysver_history ORDER BY system_time_start;
val
-------
hello
(1 row)
SELECT transaction_timestamp() AS ts2 \gset
DELETE FROM sysver;
SELECT val FROM sysver;
val
-----
(0 rows)
SELECT val FROM sysver_history ORDER BY system_time_start;
val
-------
hello
world
(2 rows)
/* temporal queries */
SELECT val FROM sysver__as_of(:'ts1') ORDER BY system_time_start;
val
-------
hello
(1 row)
SELECT val FROM sysver__as_of(:'ts2') ORDER BY system_time_start;
val
-------
world
(1 row)
SELECT val FROM sysver__from_to(:'ts1', :'ts2') ORDER BY system_time_start;
val
-------
hello
world
(2 rows)
SELECT val FROM sysver__from_to(:'ts2', :'ts1') ORDER BY system_time_start;
val
-----
(0 rows)
SELECT val FROM sysver__between(:'ts1', :'ts2') ORDER BY system_time_start;
val
-------
hello
world
(2 rows)
SELECT val FROM sysver__between(:'ts2', :'ts1') ORDER BY system_time_start;
val
-----
(0 rows)
SELECT val FROM sysver__between_symmetric(:'ts1', :'ts2') ORDER BY system_time_start;
val
-------
hello
world
(2 rows)
SELECT val FROM sysver__between_symmetric(:'ts2', :'ts1') ORDER BY system_time_start;
val
-------
hello
world
(2 rows)
/* Ensure functions are inlined */
SET TimeZone = 'UTC';
SET DateStyle = 'ISO';
EXPLAIN (COSTS OFF) SELECT * FROM sysver__as_of('2000-01-01');
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Append
-> Seq Scan on sysver
Filter: ((system_time_start <= '2000-01-01 00:00:00+00'::timestamp with time zone) AND (system_time_end > '2000-01-01 00:00:00+00'::timestamp with time zone))
-> Seq Scan on sysver_history
Filter: ((system_time_start <= '2000-01-01 00:00:00+00'::timestamp with time zone) AND (system_time_end > '2000-01-01 00:00:00+00'::timestamp with time zone))
(5 rows)
EXPLAIN (COSTS OFF) SELECT * FROM sysver__from_to('1000-01-01', '3000-01-01');
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Append
-> Seq Scan on sysver
Filter: ((system_time_end > '1000-01-01 00:00:00+00'::timestamp with time zone) AND (system_time_start < '3000-01-01 00:00:00+00'::timestamp with time zone))
-> Seq Scan on sysver_history
Filter: ((system_time_end > '1000-01-01 00:00:00+00'::timestamp with time zone) AND (system_time_start < '3000-01-01 00:00:00+00'::timestamp with time zone))
(5 rows)
EXPLAIN (COSTS OFF) SELECT * FROM sysver__between('1000-01-01', '3000-01-01');
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Append
-> Seq Scan on sysver
Filter: ((system_time_end > '1000-01-01 00:00:00+00'::timestamp with time zone) AND (system_time_start <= '3000-01-01 00:00:00+00'::timestamp with time zone))
-> Seq Scan on sysver_history
Filter: ((system_time_end > '1000-01-01 00:00:00+00'::timestamp with time zone) AND (system_time_start <= '3000-01-01 00:00:00+00'::timestamp with time zone))
(5 rows)
EXPLAIN (COSTS OFF) SELECT * FROM sysver__between_symmetric('3000-01-01', '1000-01-01');
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Append
-> Seq Scan on sysver
Filter: ((system_time_end > LEAST('3000-01-01 00:00:00+00'::timestamp with time zone, '1000-01-01 00:00:00+00'::timestamp with time zone)) AND (system_time_start <= GREATEST('3000-01-01 00:00:00+00'::timestamp with time zone, '1000-01-01 00:00:00+00'::timestamp with time zone)))
-> Seq Scan on sysver_history
Filter: ((system_time_end > LEAST('3000-01-01 00:00:00+00'::timestamp with time zone, '1000-01-01 00:00:00+00'::timestamp with time zone)) AND (system_time_start <= GREATEST('3000-01-01 00:00:00+00'::timestamp with time zone, '1000-01-01 00:00:00+00'::timestamp with time zone)))
(5 rows)
/* TRUNCATE should delete the history, too */
SELECT val FROM sysver_with_history;
val
-------
hello
world
(2 rows)
TRUNCATE sysver;
SELECT val FROM sysver_with_history; --empty
val
-----
(0 rows)
/* Try modifying several times in a transaction */
BEGIN;
INSERT INTO sysver (val) VALUES ('hello');
INSERT INTO sysver (val) VALUES ('world');
ROLLBACK;
SELECT val FROM sysver_with_history; --empty
val
-----
(0 rows)
BEGIN;
INSERT INTO sysver (val) VALUES ('hello');
UPDATE sysver SET val = 'world';
UPDATE sysver SET val = 'world2';
UPDATE sysver SET val = 'world3';
DELETE FROM sysver;
COMMIT;
SELECT val FROM sysver_with_history; --empty
val
-----
(0 rows)
-- We can't drop the the table without first dropping SYSTEM VERSIONING because
-- Postgres will complain about dependant objects (our view functions) before
-- we get a chance to clean them up.
DROP TABLE sysver;
ERROR: cannot drop table sysver because other objects depend on it
DETAIL: view sysver_with_history depends on table sysver
function sysver__as_of(timestamp with time zone) depends on type sysver_with_history
function sysver__between(timestamp with time zone,timestamp with time zone) depends on type sysver_with_history
function sysver__between_symmetric(timestamp with time zone,timestamp with time zone) depends on type sysver_with_history
function sysver__from_to(timestamp with time zone,timestamp with time zone) depends on type sysver_with_history
HINT: Use DROP ... CASCADE to drop the dependent objects too.
SELECT periods.drop_system_versioning('sysver', drop_behavior => 'CASCADE', purge => true);
drop_system_versioning
------------------------
t
(1 row)
TABLE periods.system_versioning;
table_name | period_name | history_table_name | view_name | func_as_of | func_between | func_between_symmetric | func_from_to
------------+-------------+--------------------+-----------+------------+--------------+------------------------+--------------
(0 rows)
DROP TABLE sysver;
TABLE periods.periods;
table_name | period_name | start_column_name | end_column_name | range_type | bounds_check_constraint
------------+-------------+-------------------+-----------------+------------+-------------------------
(0 rows)
TABLE periods.system_time_periods;
table_name | period_name | infinity_check_constraint | generated_always_trigger | write_history_trigger | truncate_trigger | excluded_column_names
------------+-------------+---------------------------+--------------------------+-----------------------+------------------+-----------------------
(0 rows)
|
