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
|
SELECT setting::integer < 100000 AS pre_10
FROM pg_settings WHERE name = 'server_version_num';
/* 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
);
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');
SELECT periods.add_system_versioning ('issue5');
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');
COMMIT;
SELECT periods.drop_system_versioning('issue5', drop_behavior => 'CASCADE', purge => true);
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');
SELECT periods.add_system_versioning('pg16242');
UPDATE pg16242 SET value = 'hello world';
SELECT system_time_start FROM pg16242_history;
SELECT periods.drop_system_versioning('pg16242', drop_behavior => 'CASCADE', purge => true);
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
);
-- 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');
SELECT periods.add_system_versioning('issue11');
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;
-- 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;
SET enable_seqscan = off;
SELECT id, revision FROM "issue11_history" WHERE "id" = 2 ORDER BY row_start_time;
RESET enable_seqscan;
SELECT periods.drop_system_versioning('issue11', drop_behavior => 'CASCADE', purge => true);
DROP TABLE "issue11";
|
