blob: 595cb70fc5cb14907199a9918320fadb747ce969 (
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
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
|
# INSERT...ON CONFLICT test on table with TOAST
#
# This test verifies that speculatively inserted toast rows do not
# cause conflicts. It does so by using expression index over a
# function which acquires an advisory lock, triggering two index
# insertions to happen almost at the same time. This is not guaranteed
# to lead to a failed speculative insertion, but makes one quite
# likely.
setup
{
CREATE TABLE ctoast (key int primary key, val text);
CREATE OR REPLACE FUNCTION ctoast_lock_func(int) RETURNS INT IMMUTABLE LANGUAGE SQL AS 'select pg_advisory_xact_lock_shared(1); select $1;';
CREATE OR REPLACE FUNCTION ctoast_large_val() RETURNS TEXT LANGUAGE SQL AS 'select array_agg(md5(g::text))::text from generate_series(1, 256) g';
CREATE UNIQUE INDEX ctoast_lock_idx ON ctoast (ctoast_lock_func(key));
}
teardown
{
DROP TABLE ctoast;
DROP FUNCTION ctoast_lock_func(int);
DROP FUNCTION ctoast_large_val();
}
session s1
setup
{
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT pg_advisory_xact_lock(1);
}
step s1commit { COMMIT; }
session s2
setup
{
SET default_transaction_isolation = 'read committed';
}
step s2insert {
INSERT INTO ctoast (key, val) VALUES (1, ctoast_large_val()) ON CONFLICT DO NOTHING;
}
session s3
setup
{
SET default_transaction_isolation = 'read committed';
}
step s3insert {
INSERT INTO ctoast (key, val) VALUES (1, ctoast_large_val()) ON CONFLICT DO NOTHING;
}
# s1's commit will release s2 and s3 at the same time, so there's a
# race condition as to which finishes first. Annotate the permutation
# to always report s2 first.
permutation s2insert s3insert(s2insert) s1commit
|
