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
|
SELECT setting::integer < 100000 AS pre_10
FROM pg_settings WHERE name = 'server_version_num';
pre_10
--------
t
(1 row)
/* Run tests as unprivileged user */
SET ROLE TO periods_unprivileged_user;
/* https://github.com/xocolatl/periods/issues/5 */
CREATE TABLE issue5 (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
value VARCHAR NOT NULL
);
ERROR: syntax error at or near "GENERATED"
LINE 3: id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
^
CREATE TABLE IF NOT EXISTS issue5 (
id serial PRIMARY KEY,
value VARCHAR NOT NULL
);
ALTER TABLE issue5
DROP COLUMN value;
ALTER TABLE issue5
ADD COLUMN value2 varchar NOT NULL;
INSERT INTO issue5 (value2)
VALUES ('hello'), ('world');
SELECT periods.add_system_time_period ('issue5');
add_system_time_period
------------------------
t
(1 row)
SELECT periods.add_system_versioning ('issue5');
NOTICE: history table "issue5_history" created for "issue5", be sure to index it properly
add_system_versioning
-----------------------
(1 row)
BEGIN;
SELECT now() AS ts \gset
UPDATE issue5
SET value2 = 'goodbye'
WHERE id = 2;
SELECT id, value2, system_time_start, system_time_end
FROM issue5_with_history
EXCEPT ALL
VALUES (1::integer, 'hello'::varchar, '-infinity'::timestamptz, 'infinity'::timestamptz),
(2, 'goodbye', :'ts', 'infinity'),
(2, 'world', '-infinity', :'ts');
id | value2 | system_time_start | system_time_end
----+--------+-------------------+-----------------
(0 rows)
COMMIT;
SELECT periods.drop_system_versioning('issue5', drop_behavior => 'CASCADE', purge => true);
drop_system_versioning
------------------------
t
(1 row)
DROP TABLE issue5;
/* Check PostgreSQL Bug #16242 */
CREATE TABLE pg16242 (value text);
INSERT INTO pg16242 (value) VALUES ('helloworld');
SELECT periods.add_system_time_period('pg16242');
add_system_time_period
------------------------
t
(1 row)
SELECT periods.add_system_versioning('pg16242');
NOTICE: history table "pg16242_history" created for "pg16242", be sure to index it properly
add_system_versioning
-----------------------
(1 row)
UPDATE pg16242 SET value = 'hello world';
SELECT system_time_start FROM pg16242_history;
system_time_start
-------------------
-infinity
(1 row)
SELECT periods.drop_system_versioning('pg16242', drop_behavior => 'CASCADE', purge => true);
drop_system_versioning
------------------------
t
(1 row)
DROP TABLE pg16242;
/* https://github.com/xocolatl/periods/issues/11 */
CREATE TABLE "issue11" (
"id" BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
"revision" INTEGER NOT NULL
);
ERROR: syntax error at or near "GENERATED"
LINE 3: "id" BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY...
^
-- for versions pre-10:
CREATE TABLE "issue11" (
"id" bigserial PRIMARY KEY,
"revision" INTEGER NOT NULL
);
SELECT periods.add_system_time_period('issue11', 'row_start_time', 'row_end_time');
add_system_time_period
------------------------
t
(1 row)
SELECT periods.add_system_versioning('issue11');
NOTICE: history table "issue11_history" created for "issue11", be sure to index it properly
add_system_versioning
-----------------------
(1 row)
INSERT INTO "issue11" ("revision") VALUES (1);
INSERT INTO "issue11" ("revision") VALUES (10);
UPDATE "issue11" SET "revision" = 2 WHERE ("id" = 1);
UPDATE "issue11" SET "revision" = 3 WHERE ("id" = 1);
CREATE INDEX "yolo" ON "issue11_history" ("id", "revision");
UPDATE "issue11" SET "revision" = 11 WHERE ("id" = 2);
-- returns 2 rows
SELECT id, revision FROM "issue11_history" WHERE "id" = 1 ORDER BY row_start_time;
id | revision
----+----------
1 | 1
1 | 2
(2 rows)
-- returns 0 rows if index is used / 1 row if seq scan is used
SELECT id, revision FROM "issue11_history" WHERE "id" = 2 ORDER BY row_start_time;
id | revision
----+----------
2 | 10
(1 row)
SET enable_seqscan = off;
SELECT id, revision FROM "issue11_history" WHERE "id" = 2 ORDER BY row_start_time;
id | revision
----+----------
2 | 10
(1 row)
RESET enable_seqscan;
SELECT periods.drop_system_versioning('issue11', drop_behavior => 'CASCADE', purge => true);
drop_system_versioning
------------------------
t
(1 row)
DROP TABLE "issue11";
|
