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
|
# INSERT ... ON CONFLICT test verifying that speculative insertion
# failures are handled
#
# Does this by using advisory locks controlling progress of
# insertions. By waiting when building the index keys, it's possible
# to schedule concurrent INSERT ON CONFLICTs so that there will always
# be a speculative conflict.
setup
{
CREATE OR REPLACE FUNCTION blurt_and_lock(text) RETURNS text IMMUTABLE LANGUAGE plpgsql AS $$
BEGIN
RAISE NOTICE 'called for %', $1;
-- depending on lock state, wait for lock 2 or 3
IF pg_try_advisory_xact_lock(current_setting('spec.session')::int, 1) THEN
RAISE NOTICE 'blocking 2';
PERFORM pg_advisory_xact_lock(current_setting('spec.session')::int, 2);
ELSE
RAISE NOTICE 'blocking 3';
PERFORM pg_advisory_xact_lock(current_setting('spec.session')::int, 3);
END IF;
RETURN $1;
END;$$;
CREATE TABLE upserttest(key text, data text);
CREATE UNIQUE INDEX ON upserttest((blurt_and_lock(key)));
}
teardown
{
DROP TABLE upserttest;
}
session "controller"
setup
{
SET default_transaction_isolation = 'read committed';
}
step "controller_locks" {SELECT pg_advisory_lock(sess, lock), sess, lock FROM generate_series(1, 2) a(sess), generate_series(1,3) b(lock);}
step "controller_unlock_1_1" { SELECT pg_advisory_unlock(1, 1); }
step "controller_unlock_2_1" { SELECT pg_advisory_unlock(2, 1); }
step "controller_unlock_1_2" { SELECT pg_advisory_unlock(1, 2); }
step "controller_unlock_2_2" { SELECT pg_advisory_unlock(2, 2); }
step "controller_unlock_1_3" { SELECT pg_advisory_unlock(1, 3); }
step "controller_unlock_2_3" { SELECT pg_advisory_unlock(2, 3); }
step "controller_show" {SELECT * FROM upserttest; }
session "s1"
setup
{
SET default_transaction_isolation = 'read committed';
SET spec.session = 1;
}
step "s1_begin" { BEGIN; }
step "s1_upsert" { INSERT INTO upserttest(key, data) VALUES('k1', 'inserted s1') ON CONFLICT (blurt_and_lock(key)) DO UPDATE SET data = upserttest.data || ' with conflict update s1'; }
step "s1_commit" { COMMIT; }
session "s2"
setup
{
SET default_transaction_isolation = 'read committed';
SET spec.session = 2;
}
step "s2_begin" { BEGIN; }
step "s2_upsert" { INSERT INTO upserttest(key, data) VALUES('k1', 'inserted s2') ON CONFLICT (blurt_and_lock(key)) DO UPDATE SET data = upserttest.data || ' with conflict update s2'; }
step "s2_commit" { COMMIT; }
# Test that speculative locks are correctly acquired and released, s2
# inserts, s1 updates.
permutation
# acquire a number of locks, to control execution flow - the
# blurt_and_lock function acquires advisory locks that allow us to
# continue after a) the optimistic conflict probe b) after the
# insertion of the speculative tuple.
"controller_locks"
"controller_show"
"s1_upsert" "s2_upsert"
"controller_show"
# Switch both sessions to wait on the other lock next time (the speculative insertion)
"controller_unlock_1_1" "controller_unlock_2_1"
# Allow both sessions to continue
"controller_unlock_1_3" "controller_unlock_2_3"
"controller_show"
# Allow the second session to finish insertion
"controller_unlock_2_2"
# This should now show a successful insertion
"controller_show"
# Allow the first session to finish insertion
"controller_unlock_1_2"
# This should now show a successful UPSERT
"controller_show"
# Test that speculative locks are correctly acquired and released, s2
# inserts, s1 updates.
permutation
# acquire a number of locks, to control execution flow - the
# blurt_and_lock function acquires advisory locks that allow us to
# continue after a) the optimistic conflict probe b) after the
# insertion of the speculative tuple.
"controller_locks"
"controller_show"
"s1_upsert" "s2_upsert"
"controller_show"
# Switch both sessions to wait on the other lock next time (the speculative insertion)
"controller_unlock_1_1" "controller_unlock_2_1"
# Allow both sessions to continue
"controller_unlock_1_3" "controller_unlock_2_3"
"controller_show"
# Allow the first session to finish insertion
"controller_unlock_1_2"
# This should now show a successful insertion
"controller_show"
# Allow the second session to finish insertion
"controller_unlock_2_2"
# This should now show a successful UPSERT
"controller_show"
# Test that speculative locks are correctly acquired and released, s2
# inserts, s1 updates. With the added complication that transactions
# don't immediately commit.
permutation
# acquire a number of locks, to control execution flow - the
# blurt_and_lock function acquires advisory locks that allow us to
# continue after a) the optimistic conflict probe b) after the
# insertion of the speculative tuple.
"controller_locks"
"controller_show"
"s1_begin" "s2_begin"
"s1_upsert" "s2_upsert"
"controller_show"
# Switch both sessions to wait on the other lock next time (the speculative insertion)
"controller_unlock_1_1" "controller_unlock_2_1"
# Allow both sessions to continue
"controller_unlock_1_3" "controller_unlock_2_3"
"controller_show"
# Allow the first session to finish insertion
"controller_unlock_1_2"
# But the change isn't visible yet, nor should the second session continue
"controller_show"
# Allow the second session to finish insertion, but it's blocked
"controller_unlock_2_2"
"controller_show"
# But committing should unblock
"s1_commit"
"controller_show"
"s2_commit"
"controller_show"
|
