blob: c5e39ef9e316362cb7a4f90272b3bddd0c0d4f9f (
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
|
# 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;
}
permutation "s2insert" "s3insert" "s1commit"
|
