1# Test for page level predicate locking in gin index 2# 3# Test to verify serialization failures and to check reduced false positives 4# 5# To verify serialization failures, queries and permutations are written in such 6# a way that an index scan (from one transaction) and an index insert (from 7# another transaction) will try to access the same part (sub-tree) of the index 8# whereas to check reduced false positives, they will try to access different 9# parts (sub-tree) of the index. 10 11 12setup 13{ 14 create table gin_tbl(p int4[]); 15 insert into gin_tbl select array[1] from generate_series(1, 8192) g; 16 insert into gin_tbl select array[g] from generate_series(2, 800) g; 17 create index ginidx on gin_tbl using gin(p) with (fastupdate = off); 18 create table other_tbl(v int4); 19} 20 21teardown 22{ 23 drop table gin_tbl; 24 drop table other_tbl; 25} 26 27session s1 28setup 29{ 30 begin isolation level serializable; 31 set enable_seqscan=off; 32} 33 34step ra1 { select * from gin_tbl where p @> array[1] limit 1; } 35step rb1 { select count(*) from gin_tbl where p @> array[2]; } 36step rc1 { select count(*) from gin_tbl where p @> array[800]; } 37step rd1 { select count(*) from gin_tbl where p @> array[2000]; } 38 39step wo1 { insert into other_tbl values (1); } 40 41step c1 { commit; } 42 43session s2 44setup 45{ 46 begin isolation level serializable; 47 set enable_seqscan=off; 48} 49 50step ro2 { select count(*) from other_tbl; } 51 52step wa2 { insert into gin_tbl values (array[1]); } 53step wb2 { insert into gin_tbl values (array[2]); } 54step wc2 { insert into gin_tbl values (array[800]); } 55step wd2 { insert into gin_tbl values (array[2000]); } 56 57step c2 { commit; } 58 59session s3 60step fu { alter index ginidx set (fastupdate = on); } 61 62# An index scan (from one transaction) and an index insert (from another 63# transaction) try to access the same part of the index. So, there is a 64# r-w conflict. 65 66permutation ra1 ro2 wo1 c1 wa2 c2 67permutation ro2 ra1 wo1 c1 wa2 c2 68permutation ro2 ra1 wo1 wa2 c1 c2 69permutation ra1 ro2 wa2 wo1 c1 c2 70 71permutation rb1 ro2 wo1 c1 wb2 c2 72permutation ro2 rb1 wo1 c1 wb2 c2 73permutation ro2 rb1 wo1 wb2 c1 c2 74permutation rb1 ro2 wb2 wo1 c1 c2 75 76permutation rc1 ro2 wo1 c1 wc2 c2 77permutation ro2 rc1 wo1 c1 wc2 c2 78permutation ro2 rc1 wo1 wc2 c1 c2 79permutation rc1 ro2 wc2 wo1 c1 c2 80 81# An index scan (from one transaction) and an index insert (from another 82# transaction) try to access different parts of the index. So, there is no 83# r-w conflict. 84 85permutation ra1 ro2 wo1 c1 wb2 c2 86permutation ro2 ra1 wo1 c1 wc2 c2 87permutation ro2 rb1 wo1 wa2 c1 c2 88permutation rc1 ro2 wa2 wo1 c1 c2 89 90permutation rb1 ro2 wo1 c1 wa2 c2 91permutation ro2 rb1 wo1 c1 wc2 c2 92permutation ro2 ra1 wo1 wb2 c1 c2 93permutation rc1 ro2 wb2 wo1 c1 c2 94 95permutation rc1 ro2 wo1 c1 wa2 c2 96permutation ro2 rc1 wo1 c1 wb2 c2 97permutation ro2 ra1 wo1 wc2 c1 c2 98permutation rb1 ro2 wc2 wo1 c1 c2 99 100# With fastupdate = on all index is under predicate lock. So we can't 101# distinguish particular keys. 102 103permutation fu ra1 ro2 wo1 c1 wa2 c2 104permutation fu ra1 ro2 wo1 c1 wb2 c2 105 106# Check fastupdate turned on concurrently. 107 108permutation ra1 ro2 wo1 c1 fu wa2 c2 109 110# Tests for conflicts with previously non-existing key 111 112permutation rd1 ro2 wo1 c1 wd2 c2 113permutation ro2 rd1 wo1 c1 wd2 c2 114permutation ro2 rd1 wo1 wd2 c1 c2 115permutation rd1 ro2 wd2 wo1 c1 c2 116