blob: 2d4c59c21b146441f1ad215b3f2369e5e8d94cd1 (
plain)
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
|
# Temporal Range Integrity test
#
# Snapshot integrity fails with simple referential integrity tests,
# but those don't make for good demonstrations because people just
# say that foreign key definitions should be used instead. There
# are many integrity tests which are conceptually very similar but
# don't have built-in support which will fail when used in triggers.
# This is intended to illustrate such cases. It is obviously very
# hard to exercise all these permutations when the code is actually
# in a trigger; this test pulls what would normally be inside of
# triggers out to the top level to control the permutations.
#
# Any overlap between the transactions must cause a serialization failure.
setup
{
CREATE TABLE statute (statute_cite text NOT NULL, eff_date date NOT NULL, exp_date date, CONSTRAINT statute_pkey PRIMARY KEY (statute_cite, eff_date));
INSERT INTO statute VALUES ('123.45(1)a', DATE '2008-01-01', NULL);
CREATE TABLE offense (offense_no int NOT NULL, statute_cite text NOT NULL, offense_date date NOT NULL, CONSTRAINT offense_pkey PRIMARY KEY (offense_no));
}
teardown
{
DROP TABLE statute, offense;
}
session s1
setup { BEGIN ISOLATION LEVEL SERIALIZABLE; }
step rx1 { SELECT count(*) FROM statute WHERE statute_cite = '123.45(1)a' AND eff_date <= DATE '2009-05-15' AND (exp_date IS NULL OR exp_date > DATE '2009-05-15'); }
step wy1 { INSERT INTO offense VALUES (1, '123.45(1)a', DATE '2009-05-15'); }
step c1 { COMMIT; }
session s2
setup { BEGIN ISOLATION LEVEL SERIALIZABLE; }
step ry2 { SELECT count(*) FROM offense WHERE statute_cite = '123.45(1)a' AND offense_date >= DATE '2008-01-01'; }
step wx2 { DELETE FROM statute WHERE statute_cite = '123.45(1)a' AND eff_date = DATE '2008-01-01'; }
step c2 { COMMIT; }
|
