src/test/isolation/specs/predicate-gist.spec

# Test for page level predicate locking in gist
#
# Test to verify serialization failures and to check reduced false positives
#
# To verify serialization failures, queries and permutations are written in such
# a way that an index scan  (from one transaction) and an index insert (from
# another transaction) will try to access the same part (sub-tree) of the index
# whereas to check reduced false positives, they will try to access different
# parts (sub-tree) of the index.

setup
{
  create table gist_point_tbl(id int4, p point);
  create index gist_pointidx on gist_point_tbl using gist(p);
  insert into gist_point_tbl (id, p)
  select g, point(g*10, g*10) from generate_series(1, 1000) g;
}

teardown
{
  drop table gist_point_tbl;
}

session s1
setup
{
  begin isolation level serializable;
  set enable_seqscan=off;
  set enable_bitmapscan=off;
  set enable_indexonlyscan=on;
}

step rxy1	{ select sum(p[0]) from gist_point_tbl where p << point(2500, 2500); }
step wx1	{ insert into gist_point_tbl (id, p)
			  select g, point(g*500, g*500) from generate_series(15, 20) g; }
step rxy3	{ select sum(p[0]) from gist_point_tbl where p >> point(6000,6000); }
step wx3	{ insert into gist_point_tbl (id, p)
			  select g, point(g*500, g*500) from generate_series(12, 18) g; }
step c1		{ commit; }


session s2
setup
{
  begin isolation level serializable;
  set enable_seqscan=off;
  set enable_bitmapscan=off;
  set enable_indexonlyscan=on;
}

step rxy2	{ select sum(p[0]) from gist_point_tbl where p >> point(7500,7500); }
step wy2	{ insert into gist_point_tbl (id, p)
			  select g, point(g*500, g*500) from generate_series(1, 5) g; }
step rxy4	{ select sum(p[0]) from gist_point_tbl where p << point(1000,1000); }
step wy4	{ insert into gist_point_tbl (id, p)
			  select g, point(g*50, g*50) from generate_series(1, 20) g; }
step c2		{ commit; }

# An index scan (from one transaction) and an index insert (from another
# transaction) try to access the same part of the index but one transaction
# commits before other transaction begins so no r-w conflict.

permutation rxy1 wx1 c1 rxy2 wy2 c2
permutation rxy2 wy2 c2 rxy1 wx1 c1

# An index scan (from one transaction) and an index insert (from another
# transaction) try to access different parts of the index and also one
# transaction commits before other transaction begins, so no r-w conflict.

permutation rxy3 wx3 c1 rxy4 wy4 c2
permutation rxy4 wy4 c2 rxy3 wx3 c1


# An index scan (from one transaction) and an index insert (from another
# transaction) try to access the same part of the index and one transaction
# begins before other transaction commits so there is a r-w conflict.

permutation rxy1 wx1 rxy2 c1 wy2 c2
permutation rxy1 wx1 rxy2 wy2 c1 c2
permutation rxy1 wx1 rxy2 wy2 c2 c1
permutation rxy1 rxy2 wx1 c1 wy2 c2
permutation rxy1 rxy2 wx1 wy2 c1 c2
permutation rxy1 rxy2 wx1 wy2 c2 c1
permutation rxy1 rxy2 wy2 wx1 c1 c2
permutation rxy1 rxy2 wy2 wx1 c2 c1
permutation rxy1 rxy2 wy2 c2 wx1 c1
permutation rxy2 rxy1 wx1 c1 wy2 c2
permutation rxy2 rxy1 wx1 wy2 c1 c2
permutation rxy2 rxy1 wx1 wy2 c2 c1
permutation rxy2 rxy1 wy2 wx1 c1 c2
permutation rxy2 rxy1 wy2 wx1 c2 c1
permutation rxy2 rxy1 wy2 c2 wx1 c1
permutation rxy2 wy2 rxy1 wx1 c1 c2
permutation rxy2 wy2 rxy1 wx1 c2 c1
permutation rxy2 wy2 rxy1 c2 wx1 c1

# An index scan (from one transaction) and an index insert (from another
# transaction) try to access different parts of the index so no r-w conflict.

permutation rxy3 wx3 rxy4 c1 wy4 c2
permutation rxy3 wx3 rxy4 wy4 c1 c2
permutation rxy3 wx3 rxy4 wy4 c2 c1
permutation rxy3 rxy4 wx3 c1 wy4 c2
permutation rxy3 rxy4 wx3 wy4 c1 c2
permutation rxy3 rxy4 wx3 wy4 c2 c1
permutation rxy3 rxy4 wy4 wx3 c1 c2
permutation rxy3 rxy4 wy4 wx3 c2 c1
permutation rxy3 rxy4 wy4 c2 wx3 c1
permutation rxy4 rxy3 wx3 c1 wy4 c2
permutation rxy4 rxy3 wx3 wy4 c1 c2
permutation rxy4 rxy3 wx3 wy4 c2 c1
permutation rxy4 rxy3 wy4 wx3 c1 c2
permutation rxy4 rxy3 wy4 wx3 c2 c1
permutation rxy4 rxy3 wy4 c2 wx3 c1
permutation rxy4 wy4 rxy3 wx3 c1 c2
permutation rxy4 wy4 rxy3 wx3 c2 c1
permutation rxy4 wy4 rxy3 c2 wx3 c1