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
|
SELECT setting::integer < 90600 AS pre_96
FROM pg_settings WHERE name = 'server_version_num';
pre_96
--------
t
(1 row)
/* Run tests as unprivileged user */
SET ROLE TO periods_unprivileged_user;
CREATE TABLE excl (
value text NOT NULL,
null_value integer,
flap text NOT NULL
);
SELECT periods.add_system_time_period('excl', excluded_column_names => ARRAY['xmin']); -- fails
ERROR: cannot exclude system column "xmin"
SELECT periods.add_system_time_period('excl', excluded_column_names => ARRAY['none']); -- fails
ERROR: column "none" does not exist
SELECT periods.add_system_time_period('excl', excluded_column_names => ARRAY['flap']); -- passes
add_system_time_period
------------------------
t
(1 row)
SELECT periods.add_system_versioning('excl');
NOTICE: history table "excl_history" created for "excl", be sure to index it properly
add_system_versioning
-----------------------
(1 row)
TABLE periods.periods;
table_name | period_name | start_column_name | end_column_name | range_type | bounds_check_constraint
------------+-------------+-------------------+-----------------+------------+-------------------------
excl | system_time | system_time_start | system_time_end | tstzrange | excl_system_time_check
(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
------------+-------------+-------------------------------------+-----------------------------------+--------------------------------+------------------+-----------------------
excl | system_time | excl_system_time_end_infinity_check | excl_system_time_generated_always | excl_system_time_write_history | excl_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
------------+-------------+--------------------+-------------------+----------------------------------------------+-------------------------------------------------------------------------+-----------------------------------------------------------------------------------+-------------------------------------------------------------------------
excl | system_time | excl_history | excl_with_history | public.excl__as_of(timestamp with time zone) | public.excl__between(timestamp with time zone,timestamp with time zone) | public.excl__between_symmetric(timestamp with time zone,timestamp with time zone) | public.excl__from_to(timestamp with time zone,timestamp with time zone)
(1 row)
BEGIN;
SELECT CURRENT_TIMESTAMP AS now \gset
INSERT INTO excl (value, flap) VALUES ('hello world', 'off');
COMMIT;
SELECT value, null_value, flap, system_time_start <> :'now' AS changed FROM excl;
value | null_value | flap | changed
-------------+------------+------+---------
hello world | | off | f
(1 row)
UPDATE excl SET flap = 'off';
UPDATE excl SET flap = 'on';
UPDATE excl SET flap = 'off';
UPDATE excl SET flap = 'on';
SELECT value, null_value, flap, system_time_start <> :'now' AS changed FROM excl;
value | null_value | flap | changed
-------------+------------+------+---------
hello world | | on | f
(1 row)
BEGIN;
SELECT CURRENT_TIMESTAMP AS now2 \gset
UPDATE excl SET value = 'howdy folks!';
COMMIT;
SELECT value, null_value, flap, system_time_start <> :'now' AS changed FROM excl;
value | null_value | flap | changed
--------------+------------+------+---------
howdy folks! | | on | t
(1 row)
UPDATE excl SET null_value = 0;
SELECT value, null_value, flap, system_time_start <> :'now2' AS changed FROM excl;
value | null_value | flap | changed
--------------+------------+------+---------
howdy folks! | 0 | on | t
(1 row)
/* Test directly setting the excluded columns */
SELECT periods.drop_system_versioning('excl');
drop_system_versioning
------------------------
t
(1 row)
ALTER TABLE excl ADD COLUMN flop text;
ALTER TABLE excl_history ADD COLUMN flop text;
SELECT periods.add_system_versioning('excl');
add_system_versioning
-----------------------
(1 row)
SELECT periods.set_system_time_period_excluded_columns('excl', ARRAY['flap', 'flop']);
set_system_time_period_excluded_columns
-----------------------------------------
(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
------------+-------------+-------------------------------------+-----------------------------------+--------------------------------+------------------+-----------------------
excl | system_time | excl_system_time_end_infinity_check | excl_system_time_generated_always | excl_system_time_write_history | excl_truncate | {flap,flop}
(1 row)
UPDATE excl SET flop = 'flop';
SELECT value, null_value, flap, flop FROM excl;
value | null_value | flap | flop
--------------+------------+------+------
howdy folks! | 0 | on | flop
(1 row)
SELECT value, null_value, flap, flop FROM excl_history ORDER BY system_time_start;
value | null_value | flap | flop
--------------+------------+------+------
hello world | | on |
howdy folks! | | on |
(2 rows)
SELECT periods.drop_system_versioning('excl', drop_behavior => 'CASCADE', purge => true);
drop_system_versioning
------------------------
t
(1 row)
DROP TABLE excl;
|
