GSoC 2017: weekly progress reports (week 8)

From 666b4022396b4a15cd97639428d0b96e3ed65e0f Mon Sep 17 00:00:00 2001
From: shubhambaraiss <you@example.com>
Date: Tue, 25 Jul 2017 07:41:05 +0530
Subject: [PATCH] Predicate Locking in spgist index

---
 src/backend/access/spgist/spgdoinsert.c            |   7 +
 src/backend/access/spgist/spgscan.c                |   3 +
 src/backend/access/spgist/spgutils.c               |   2 +-
 src/backend/storage/lmgr/README-SSI                |   4 +
 src/test/isolation/expected/predicate-spgist-2.out | 401 ++++++++++++++++++++
 src/test/isolation/expected/predicate-spgist.out   | 419 +++++++++++++++++++++
 src/test/isolation/isolation_schedule              |   2 +
 src/test/isolation/specs/predicate-spgist-2.spec   |  48 +++
 src/test/isolation/specs/predicate-spgist.spec     |  48 +++
 9 files changed, 933 insertions(+), 1 deletion(-)
 create mode 100644 src/test/isolation/expected/predicate-spgist-2.out
 create mode 100644 src/test/isolation/expected/predicate-spgist.out
 create mode 100644 src/test/isolation/specs/predicate-spgist-2.spec
 create mode 100644 src/test/isolation/specs/predicate-spgist.spec

diff --git a/src/backend/access/spgist/spgdoinsert.c b/src/backend/access/spgist/spgdoinsert.c
index b0702a7..ed996e8 100644
--- a/src/backend/access/spgist/spgdoinsert.c
+++ b/src/backend/access/spgist/spgdoinsert.c
@@ -19,6 +19,7 @@
 #include "access/spgist_private.h"
 #include "access/spgxlog.h"
 #include "access/xloginsert.h"
+#include "storage/predicate.h"
 #include "miscadmin.h"
 #include "storage/bufmgr.h"
 #include "utils/rel.h"
@@ -1030,6 +1031,10 @@ doPickSplit(Relation index, SpGistState *state,
 											SPGIST_PAGE_CAPACITY),
 										&xlrec.initDest);
 
+		PredicateLockPageSplit(index,
+					BufferGetBlockNumber(current->buffer),
+					BufferGetBlockNumber(newLeafBuffer));
+
 		/*
 		 * Attempt to assign node groups to the two pages.  We might fail to
 		 * do so, even if totalLeafSizes is less than the available space,
@@ -2024,6 +2029,8 @@ spgdoinsert(Relation index, SpGistState *state,
 			int			nToSplit,
 						sizeToSplit;
 
+			CheckForSerializableConflictIn(index, NULL, current.buffer);
+
 			leafTuple = spgFormLeafTuple(state, heapPtr, leafDatum, isnull);
 			if (leafTuple->size + sizeof(ItemIdData) <=
 				SpGistPageGetFreeSpace(current.page, 1))
diff --git a/src/backend/access/spgist/spgscan.c b/src/backend/access/spgist/spgscan.c
index 7965b58..0600fe7 100644
--- a/src/backend/access/spgist/spgscan.c
+++ b/src/backend/access/spgist/spgscan.c
@@ -17,6 +17,7 @@
 
 #include "access/relscan.h"
 #include "access/spgist_private.h"
+#include "storage/predicate.h"
 #include "miscadmin.h"
 #include "storage/bufmgr.h"
 #include "utils/datum.h"
@@ -351,6 +352,8 @@ redirect:
 			Datum		leafValue = (Datum) 0;
 			bool		recheck = false;
 
+			PredicateLockPage(index, BufferGetBlockNumber(buffer), snapshot);
+
 			if (SpGistBlockIsRoot(blkno))
 			{
 				/* When root is a leaf, examine all its tuples */
diff --git a/src/backend/access/spgist/spgutils.c b/src/backend/access/spgist/spgutils.c
index 8656af4..f096ec5 100644
--- a/src/backend/access/spgist/spgutils.c
+++ b/src/backend/access/spgist/spgutils.c
@@ -48,7 +48,7 @@ spghandler(PG_FUNCTION_ARGS)
 	amroutine->amsearchnulls = true;
 	amroutine->amstorage = false;
 	amroutine->amclusterable = false;
-	amroutine->ampredlocks = false;
+	amroutine->ampredlocks = true;
 	amroutine->amcanparallel = false;
 	amroutine->amkeytype = InvalidOid;
 
diff --git a/src/backend/storage/lmgr/README-SSI b/src/backend/storage/lmgr/README-SSI
index a9dc01f..0a1370e 100644
--- a/src/backend/storage/lmgr/README-SSI
+++ b/src/backend/storage/lmgr/README-SSI
@@ -379,6 +379,10 @@ level during a GiST search. An index insert at the leaf level can
 then be trusted to ripple up to all levels and locations where
 conflicting predicate locks may exist.
 
+	* SPGist searches acquire predicate lock only on the leaf pages.
+During a page split, a predicate lock is copied from the original
+page to the new page.
+
     * The effects of page splits, overflows, consolidations, and
 removals must be carefully reviewed to ensure that predicate locks
 aren't "lost" during those operations, or kept with pages which could
diff --git a/src/test/isolation/expected/predicate-spgist-2.out b/src/test/isolation/expected/predicate-spgist-2.out
new file mode 100644
index 0000000..428fb6d
--- /dev/null
+++ b/src/test/isolation/expected/predicate-spgist-2.out
@@ -0,0 +1,401 @@
+Parsed test spec with 2 sessions
+
+starting permutation: rxy1 wx1 c1 rxy2 wy2 c2
+step rxy1: select sum(id) from spgist_box_tbl where
+		  p @> box(point(5000,5000),point(15000,15000));
+sum            
+
+375750         
+step wx1: insert into spgist_box_tbl (id, p)
+                  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(11, 20) g;
+step c1: COMMIT;
+step rxy2: select sum(id) from spgist_box_tbl where
+                  p <@ box(point(5000,5000),point(15000,15000));
+sum            
+
+125250         
+step wy2: insert into spgist_box_tbl (id, p)
+		  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(1, 9) g;
+step c2: COMMIT;
+
+starting permutation: rxy1 wx1 rxy2 c1 wy2 c2
+step rxy1: select sum(id) from spgist_box_tbl where
+		  p @> box(point(5000,5000),point(15000,15000));
+sum            
+
+375750         
+step wx1: insert into spgist_box_tbl (id, p)
+                  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(11, 20) g;
+step rxy2: select sum(id) from spgist_box_tbl where
+                  p <@ box(point(5000,5000),point(15000,15000));
+sum            
+
+125250         
+step c1: COMMIT;
+step wy2: insert into spgist_box_tbl (id, p)
+		  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(1, 9) g;
+step c2: COMMIT;
+
+starting permutation: rxy1 wx1 rxy2 wy2 c1 c2
+step rxy1: select sum(id) from spgist_box_tbl where
+		  p @> box(point(5000,5000),point(15000,15000));
+sum            
+
+375750         
+step wx1: insert into spgist_box_tbl (id, p)
+                  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(11, 20) g;
+step rxy2: select sum(id) from spgist_box_tbl where
+                  p <@ box(point(5000,5000),point(15000,15000));
+sum            
+
+125250         
+step wy2: insert into spgist_box_tbl (id, p)
+		  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(1, 9) g;
+step c1: COMMIT;
+step c2: COMMIT;
+
+starting permutation: rxy1 wx1 rxy2 wy2 c2 c1
+step rxy1: select sum(id) from spgist_box_tbl where
+		  p @> box(point(5000,5000),point(15000,15000));
+sum            
+
+375750         
+step wx1: insert into spgist_box_tbl (id, p)
+                  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(11, 20) g;
+step rxy2: select sum(id) from spgist_box_tbl where
+                  p <@ box(point(5000,5000),point(15000,15000));
+sum            
+
+125250         
+step wy2: insert into spgist_box_tbl (id, p)
+		  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(1, 9) g;
+step c2: COMMIT;
+step c1: COMMIT;
+
+starting permutation: rxy1 rxy2 wx1 c1 wy2 c2
+step rxy1: select sum(id) from spgist_box_tbl where
+		  p @> box(point(5000,5000),point(15000,15000));
+sum            
+
+375750         
+step rxy2: select sum(id) from spgist_box_tbl where
+                  p <@ box(point(5000,5000),point(15000,15000));
+sum            
+
+125250         
+step wx1: insert into spgist_box_tbl (id, p)
+                  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(11, 20) g;
+step c1: COMMIT;
+step wy2: insert into spgist_box_tbl (id, p)
+		  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(1, 9) g;
+step c2: COMMIT;
+
+starting permutation: rxy1 rxy2 wx1 wy2 c1 c2
+step rxy1: select sum(id) from spgist_box_tbl where
+		  p @> box(point(5000,5000),point(15000,15000));
+sum            
+
+375750         
+step rxy2: select sum(id) from spgist_box_tbl where
+                  p <@ box(point(5000,5000),point(15000,15000));
+sum            
+
+125250         
+step wx1: insert into spgist_box_tbl (id, p)
+                  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(11, 20) g;
+step wy2: insert into spgist_box_tbl (id, p)
+		  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(1, 9) g;
+step c1: COMMIT;
+step c2: COMMIT;
+
+starting permutation: rxy1 rxy2 wx1 wy2 c2 c1
+step rxy1: select sum(id) from spgist_box_tbl where
+		  p @> box(point(5000,5000),point(15000,15000));
+sum            
+
+375750         
+step rxy2: select sum(id) from spgist_box_tbl where
+                  p <@ box(point(5000,5000),point(15000,15000));
+sum            
+
+125250         
+step wx1: insert into spgist_box_tbl (id, p)
+                  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(11, 20) g;
+step wy2: insert into spgist_box_tbl (id, p)
+		  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(1, 9) g;
+step c2: COMMIT;
+step c1: COMMIT;
+
+starting permutation: rxy1 rxy2 wy2 wx1 c1 c2
+step rxy1: select sum(id) from spgist_box_tbl where
+		  p @> box(point(5000,5000),point(15000,15000));
+sum            
+
+375750         
+step rxy2: select sum(id) from spgist_box_tbl where
+                  p <@ box(point(5000,5000),point(15000,15000));
+sum            
+
+125250         
+step wy2: insert into spgist_box_tbl (id, p)
+		  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(1, 9) g;
+step wx1: insert into spgist_box_tbl (id, p)
+                  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(11, 20) g;
+step c1: COMMIT;
+step c2: COMMIT;
+
+starting permutation: rxy1 rxy2 wy2 wx1 c2 c1
+step rxy1: select sum(id) from spgist_box_tbl where
+		  p @> box(point(5000,5000),point(15000,15000));
+sum            
+
+375750         
+step rxy2: select sum(id) from spgist_box_tbl where
+                  p <@ box(point(5000,5000),point(15000,15000));
+sum            
+
+125250         
+step wy2: insert into spgist_box_tbl (id, p)
+		  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(1, 9) g;
+step wx1: insert into spgist_box_tbl (id, p)
+                  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(11, 20) g;
+step c2: COMMIT;
+step c1: COMMIT;
+
+starting permutation: rxy1 rxy2 wy2 c2 wx1 c1
+step rxy1: select sum(id) from spgist_box_tbl where
+		  p @> box(point(5000,5000),point(15000,15000));
+sum            
+
+375750         
+step rxy2: select sum(id) from spgist_box_tbl where
+                  p <@ box(point(5000,5000),point(15000,15000));
+sum            
+
+125250         
+step wy2: insert into spgist_box_tbl (id, p)
+		  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(1, 9) g;
+step c2: COMMIT;
+step wx1: insert into spgist_box_tbl (id, p)
+                  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(11, 20) g;
+step c1: COMMIT;
+
+starting permutation: rxy2 rxy1 wx1 c1 wy2 c2
+step rxy2: select sum(id) from spgist_box_tbl where
+                  p <@ box(point(5000,5000),point(15000,15000));
+sum            
+
+125250         
+step rxy1: select sum(id) from spgist_box_tbl where
+		  p @> box(point(5000,5000),point(15000,15000));
+sum            
+
+375750         
+step wx1: insert into spgist_box_tbl (id, p)
+                  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(11, 20) g;
+step c1: COMMIT;
+step wy2: insert into spgist_box_tbl (id, p)
+		  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(1, 9) g;
+step c2: COMMIT;
+
+starting permutation: rxy2 rxy1 wx1 wy2 c1 c2
+step rxy2: select sum(id) from spgist_box_tbl where
+                  p <@ box(point(5000,5000),point(15000,15000));
+sum            
+
+125250         
+step rxy1: select sum(id) from spgist_box_tbl where
+		  p @> box(point(5000,5000),point(15000,15000));
+sum            
+
+375750         
+step wx1: insert into spgist_box_tbl (id, p)
+                  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(11, 20) g;
+step wy2: insert into spgist_box_tbl (id, p)
+		  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(1, 9) g;
+step c1: COMMIT;
+step c2: COMMIT;
+
+starting permutation: rxy2 rxy1 wx1 wy2 c2 c1
+step rxy2: select sum(id) from spgist_box_tbl where
+                  p <@ box(point(5000,5000),point(15000,15000));
+sum            
+
+125250         
+step rxy1: select sum(id) from spgist_box_tbl where
+		  p @> box(point(5000,5000),point(15000,15000));
+sum            
+
+375750         
+step wx1: insert into spgist_box_tbl (id, p)
+                  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(11, 20) g;
+step wy2: insert into spgist_box_tbl (id, p)
+		  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(1, 9) g;
+step c2: COMMIT;
+step c1: COMMIT;
+
+starting permutation: rxy2 rxy1 wy2 wx1 c1 c2
+step rxy2: select sum(id) from spgist_box_tbl where
+                  p <@ box(point(5000,5000),point(15000,15000));
+sum            
+
+125250         
+step rxy1: select sum(id) from spgist_box_tbl where
+		  p @> box(point(5000,5000),point(15000,15000));
+sum            
+
+375750         
+step wy2: insert into spgist_box_tbl (id, p)
+		  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(1, 9) g;
+step wx1: insert into spgist_box_tbl (id, p)
+                  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(11, 20) g;
+step c1: COMMIT;
+step c2: COMMIT;
+
+starting permutation: rxy2 rxy1 wy2 wx1 c2 c1
+step rxy2: select sum(id) from spgist_box_tbl where
+                  p <@ box(point(5000,5000),point(15000,15000));
+sum            
+
+125250         
+step rxy1: select sum(id) from spgist_box_tbl where
+		  p @> box(point(5000,5000),point(15000,15000));
+sum            
+
+375750         
+step wy2: insert into spgist_box_tbl (id, p)
+		  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(1, 9) g;
+step wx1: insert into spgist_box_tbl (id, p)
+                  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(11, 20) g;
+step c2: COMMIT;
+step c1: COMMIT;
+
+starting permutation: rxy2 rxy1 wy2 c2 wx1 c1
+step rxy2: select sum(id) from spgist_box_tbl where
+                  p <@ box(point(5000,5000),point(15000,15000));
+sum            
+
+125250         
+step rxy1: select sum(id) from spgist_box_tbl where
+		  p @> box(point(5000,5000),point(15000,15000));
+sum            
+
+375750         
+step wy2: insert into spgist_box_tbl (id, p)
+		  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(1, 9) g;
+step c2: COMMIT;
+step wx1: insert into spgist_box_tbl (id, p)
+                  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(11, 20) g;
+step c1: COMMIT;
+
+starting permutation: rxy2 wy2 rxy1 wx1 c1 c2
+step rxy2: select sum(id) from spgist_box_tbl where
+                  p <@ box(point(5000,5000),point(15000,15000));
+sum            
+
+125250         
+step wy2: insert into spgist_box_tbl (id, p)
+		  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(1, 9) g;
+step rxy1: select sum(id) from spgist_box_tbl where
+		  p @> box(point(5000,5000),point(15000,15000));
+sum            
+
+375750         
+step wx1: insert into spgist_box_tbl (id, p)
+                  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(11, 20) g;
+step c1: COMMIT;
+step c2: COMMIT;
+
+starting permutation: rxy2 wy2 rxy1 wx1 c2 c1
+step rxy2: select sum(id) from spgist_box_tbl where
+                  p <@ box(point(5000,5000),point(15000,15000));
+sum            
+
+125250         
+step wy2: insert into spgist_box_tbl (id, p)
+		  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(1, 9) g;
+step rxy1: select sum(id) from spgist_box_tbl where
+		  p @> box(point(5000,5000),point(15000,15000));
+sum            
+
+375750         
+step wx1: insert into spgist_box_tbl (id, p)
+                  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(11, 20) g;
+step c2: COMMIT;
+step c1: COMMIT;
+
+starting permutation: rxy2 wy2 rxy1 c2 wx1 c1
+step rxy2: select sum(id) from spgist_box_tbl where
+                  p <@ box(point(5000,5000),point(15000,15000));
+sum            
+
+125250         
+step wy2: insert into spgist_box_tbl (id, p)
+		  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(1, 9) g;
+step rxy1: select sum(id) from spgist_box_tbl where
+		  p @> box(point(5000,5000),point(15000,15000));
+sum            
+
+375750         
+step c2: COMMIT;
+step wx1: insert into spgist_box_tbl (id, p)
+                  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(11, 20) g;
+step c1: COMMIT;
+
+starting permutation: rxy2 wy2 c2 rxy1 wx1 c1
+step rxy2: select sum(id) from spgist_box_tbl where
+                  p <@ box(point(5000,5000),point(15000,15000));
+sum            
+
+125250         
+step wy2: insert into spgist_box_tbl (id, p)
+		  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(1, 9) g;
+step c2: COMMIT;
+step rxy1: select sum(id) from spgist_box_tbl where
+		  p @> box(point(5000,5000),point(15000,15000));
+sum            
+
+375750         
+step wx1: insert into spgist_box_tbl (id, p)
+                  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(11, 20) g;
+step c1: COMMIT;
diff --git a/src/test/isolation/expected/predicate-spgist.out b/src/test/isolation/expected/predicate-spgist.out
new file mode 100644
index 0000000..917c304
--- /dev/null
+++ b/src/test/isolation/expected/predicate-spgist.out
@@ -0,0 +1,419 @@
+Parsed test spec with 2 sessions
+
+starting permutation: rxy1 wx1 c1 rxy2 wy2 c2
+step rxy1: select sum(id) from spgist_box_tbl where
+		  p @> box(point(5000,5000),point(15000,15000));
+sum            
+
+375750         
+step wx1: insert into spgist_box_tbl (id, p)
+                  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(1, 10) g;
+step c1: COMMIT;
+step rxy2: select sum(id) from spgist_box_tbl where
+		  p <@ box(point(5000,5000),point(15000,15000));
+sum            
+
+125305         
+step wy2: insert into spgist_box_tbl (id, p)
+		  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(11, 20) g;
+step c2: COMMIT;
+
+starting permutation: rxy1 wx1 rxy2 c1 wy2 c2
+step rxy1: select sum(id) from spgist_box_tbl where
+		  p @> box(point(5000,5000),point(15000,15000));
+sum            
+
+375750         
+step wx1: insert into spgist_box_tbl (id, p)
+                  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(1, 10) g;
+step rxy2: select sum(id) from spgist_box_tbl where
+		  p <@ box(point(5000,5000),point(15000,15000));
+sum            
+
+125250         
+step c1: COMMIT;
+step wy2: insert into spgist_box_tbl (id, p)
+		  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(11, 20) g;
+ERROR:  could not serialize access due to read/write dependencies among transactions
+step c2: COMMIT;
+
+starting permutation: rxy1 wx1 rxy2 wy2 c1 c2
+step rxy1: select sum(id) from spgist_box_tbl where
+		  p @> box(point(5000,5000),point(15000,15000));
+sum            
+
+375750         
+step wx1: insert into spgist_box_tbl (id, p)
+                  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(1, 10) g;
+step rxy2: select sum(id) from spgist_box_tbl where
+		  p <@ box(point(5000,5000),point(15000,15000));
+sum            
+
+125250         
+step wy2: insert into spgist_box_tbl (id, p)
+		  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(11, 20) g;
+step c1: COMMIT;
+step c2: COMMIT;
+ERROR:  could not serialize access due to read/write dependencies among transactions
+
+starting permutation: rxy1 wx1 rxy2 wy2 c2 c1
+step rxy1: select sum(id) from spgist_box_tbl where
+		  p @> box(point(5000,5000),point(15000,15000));
+sum            
+
+375750         
+step wx1: insert into spgist_box_tbl (id, p)
+                  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(1, 10) g;
+step rxy2: select sum(id) from spgist_box_tbl where
+		  p <@ box(point(5000,5000),point(15000,15000));
+sum            
+
+125250         
+step wy2: insert into spgist_box_tbl (id, p)
+		  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(11, 20) g;
+step c2: COMMIT;
+step c1: COMMIT;
+ERROR:  could not serialize access due to read/write dependencies among transactions
+
+starting permutation: rxy1 rxy2 wx1 c1 wy2 c2
+step rxy1: select sum(id) from spgist_box_tbl where
+		  p @> box(point(5000,5000),point(15000,15000));
+sum            
+
+375750         
+step rxy2: select sum(id) from spgist_box_tbl where
+		  p <@ box(point(5000,5000),point(15000,15000));
+sum            
+
+125250         
+step wx1: insert into spgist_box_tbl (id, p)
+                  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(1, 10) g;
+step c1: COMMIT;
+step wy2: insert into spgist_box_tbl (id, p)
+		  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(11, 20) g;
+ERROR:  could not serialize access due to read/write dependencies among transactions
+step c2: COMMIT;
+
+starting permutation: rxy1 rxy2 wx1 wy2 c1 c2
+step rxy1: select sum(id) from spgist_box_tbl where
+		  p @> box(point(5000,5000),point(15000,15000));
+sum            
+
+375750         
+step rxy2: select sum(id) from spgist_box_tbl where
+		  p <@ box(point(5000,5000),point(15000,15000));
+sum            
+
+125250         
+step wx1: insert into spgist_box_tbl (id, p)
+                  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(1, 10) g;
+step wy2: insert into spgist_box_tbl (id, p)
+		  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(11, 20) g;
+step c1: COMMIT;
+step c2: COMMIT;
+ERROR:  could not serialize access due to read/write dependencies among transactions
+
+starting permutation: rxy1 rxy2 wx1 wy2 c2 c1
+step rxy1: select sum(id) from spgist_box_tbl where
+		  p @> box(point(5000,5000),point(15000,15000));
+sum            
+
+375750         
+step rxy2: select sum(id) from spgist_box_tbl where
+		  p <@ box(point(5000,5000),point(15000,15000));
+sum            
+
+125250         
+step wx1: insert into spgist_box_tbl (id, p)
+                  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(1, 10) g;
+step wy2: insert into spgist_box_tbl (id, p)
+		  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(11, 20) g;
+step c2: COMMIT;
+step c1: COMMIT;
+ERROR:  could not serialize access due to read/write dependencies among transactions
+
+starting permutation: rxy1 rxy2 wy2 wx1 c1 c2
+step rxy1: select sum(id) from spgist_box_tbl where
+		  p @> box(point(5000,5000),point(15000,15000));
+sum            
+
+375750         
+step rxy2: select sum(id) from spgist_box_tbl where
+		  p <@ box(point(5000,5000),point(15000,15000));
+sum            
+
+125250         
+step wy2: insert into spgist_box_tbl (id, p)
+		  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(11, 20) g;
+step wx1: insert into spgist_box_tbl (id, p)
+                  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(1, 10) g;
+step c1: COMMIT;
+step c2: COMMIT;
+ERROR:  could not serialize access due to read/write dependencies among transactions
+
+starting permutation: rxy1 rxy2 wy2 wx1 c2 c1
+step rxy1: select sum(id) from spgist_box_tbl where
+		  p @> box(point(5000,5000),point(15000,15000));
+sum            
+
+375750         
+step rxy2: select sum(id) from spgist_box_tbl where
+		  p <@ box(point(5000,5000),point(15000,15000));
+sum            
+
+125250         
+step wy2: insert into spgist_box_tbl (id, p)
+		  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(11, 20) g;
+step wx1: insert into spgist_box_tbl (id, p)
+                  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(1, 10) g;
+step c2: COMMIT;
+step c1: COMMIT;
+ERROR:  could not serialize access due to read/write dependencies among transactions
+
+starting permutation: rxy1 rxy2 wy2 c2 wx1 c1
+step rxy1: select sum(id) from spgist_box_tbl where
+		  p @> box(point(5000,5000),point(15000,15000));
+sum            
+
+375750         
+step rxy2: select sum(id) from spgist_box_tbl where
+		  p <@ box(point(5000,5000),point(15000,15000));
+sum            
+
+125250         
+step wy2: insert into spgist_box_tbl (id, p)
+		  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(11, 20) g;
+step c2: COMMIT;
+step wx1: insert into spgist_box_tbl (id, p)
+                  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(1, 10) g;
+ERROR:  could not serialize access due to read/write dependencies among transactions
+step c1: COMMIT;
+
+starting permutation: rxy2 rxy1 wx1 c1 wy2 c2
+step rxy2: select sum(id) from spgist_box_tbl where
+		  p <@ box(point(5000,5000),point(15000,15000));
+sum            
+
+125250         
+step rxy1: select sum(id) from spgist_box_tbl where
+		  p @> box(point(5000,5000),point(15000,15000));
+sum            
+
+375750         
+step wx1: insert into spgist_box_tbl (id, p)
+                  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(1, 10) g;
+step c1: COMMIT;
+step wy2: insert into spgist_box_tbl (id, p)
+		  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(11, 20) g;
+ERROR:  could not serialize access due to read/write dependencies among transactions
+step c2: COMMIT;
+
+starting permutation: rxy2 rxy1 wx1 wy2 c1 c2
+step rxy2: select sum(id) from spgist_box_tbl where
+		  p <@ box(point(5000,5000),point(15000,15000));
+sum            
+
+125250         
+step rxy1: select sum(id) from spgist_box_tbl where
+		  p @> box(point(5000,5000),point(15000,15000));
+sum            
+
+375750         
+step wx1: insert into spgist_box_tbl (id, p)
+                  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(1, 10) g;
+step wy2: insert into spgist_box_tbl (id, p)
+		  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(11, 20) g;
+step c1: COMMIT;
+step c2: COMMIT;
+ERROR:  could not serialize access due to read/write dependencies among transactions
+
+starting permutation: rxy2 rxy1 wx1 wy2 c2 c1
+step rxy2: select sum(id) from spgist_box_tbl where
+		  p <@ box(point(5000,5000),point(15000,15000));
+sum            
+
+125250         
+step rxy1: select sum(id) from spgist_box_tbl where
+		  p @> box(point(5000,5000),point(15000,15000));
+sum            
+
+375750         
+step wx1: insert into spgist_box_tbl (id, p)
+                  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(1, 10) g;
+step wy2: insert into spgist_box_tbl (id, p)
+		  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(11, 20) g;
+step c2: COMMIT;
+step c1: COMMIT;
+ERROR:  could not serialize access due to read/write dependencies among transactions
+
+starting permutation: rxy2 rxy1 wy2 wx1 c1 c2
+step rxy2: select sum(id) from spgist_box_tbl where
+		  p <@ box(point(5000,5000),point(15000,15000));
+sum            
+
+125250         
+step rxy1: select sum(id) from spgist_box_tbl where
+		  p @> box(point(5000,5000),point(15000,15000));
+sum            
+
+375750         
+step wy2: insert into spgist_box_tbl (id, p)
+		  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(11, 20) g;
+step wx1: insert into spgist_box_tbl (id, p)
+                  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(1, 10) g;
+step c1: COMMIT;
+step c2: COMMIT;
+ERROR:  could not serialize access due to read/write dependencies among transactions
+
+starting permutation: rxy2 rxy1 wy2 wx1 c2 c1
+step rxy2: select sum(id) from spgist_box_tbl where
+		  p <@ box(point(5000,5000),point(15000,15000));
+sum            
+
+125250         
+step rxy1: select sum(id) from spgist_box_tbl where
+		  p @> box(point(5000,5000),point(15000,15000));
+sum            
+
+375750         
+step wy2: insert into spgist_box_tbl (id, p)
+		  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(11, 20) g;
+step wx1: insert into spgist_box_tbl (id, p)
+                  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(1, 10) g;
+step c2: COMMIT;
+step c1: COMMIT;
+ERROR:  could not serialize access due to read/write dependencies among transactions
+
+starting permutation: rxy2 rxy1 wy2 c2 wx1 c1
+step rxy2: select sum(id) from spgist_box_tbl where
+		  p <@ box(point(5000,5000),point(15000,15000));
+sum            
+
+125250         
+step rxy1: select sum(id) from spgist_box_tbl where
+		  p @> box(point(5000,5000),point(15000,15000));
+sum            
+
+375750         
+step wy2: insert into spgist_box_tbl (id, p)
+		  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(11, 20) g;
+step c2: COMMIT;
+step wx1: insert into spgist_box_tbl (id, p)
+                  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(1, 10) g;
+ERROR:  could not serialize access due to read/write dependencies among transactions
+step c1: COMMIT;
+
+starting permutation: rxy2 wy2 rxy1 wx1 c1 c2
+step rxy2: select sum(id) from spgist_box_tbl where
+		  p <@ box(point(5000,5000),point(15000,15000));
+sum            
+
+125250         
+step wy2: insert into spgist_box_tbl (id, p)
+		  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(11, 20) g;
+step rxy1: select sum(id) from spgist_box_tbl where
+		  p @> box(point(5000,5000),point(15000,15000));
+sum            
+
+375750         
+step wx1: insert into spgist_box_tbl (id, p)
+                  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(1, 10) g;
+step c1: COMMIT;
+step c2: COMMIT;
+ERROR:  could not serialize access due to read/write dependencies among transactions
+
+starting permutation: rxy2 wy2 rxy1 wx1 c2 c1
+step rxy2: select sum(id) from spgist_box_tbl where
+		  p <@ box(point(5000,5000),point(15000,15000));
+sum            
+
+125250         
+step wy2: insert into spgist_box_tbl (id, p)
+		  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(11, 20) g;
+step rxy1: select sum(id) from spgist_box_tbl where
+		  p @> box(point(5000,5000),point(15000,15000));
+sum            
+
+375750         
+step wx1: insert into spgist_box_tbl (id, p)
+                  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(1, 10) g;
+step c2: COMMIT;
+step c1: COMMIT;
+ERROR:  could not serialize access due to read/write dependencies among transactions
+
+starting permutation: rxy2 wy2 rxy1 c2 wx1 c1
+step rxy2: select sum(id) from spgist_box_tbl where
+		  p <@ box(point(5000,5000),point(15000,15000));
+sum            
+
+125250         
+step wy2: insert into spgist_box_tbl (id, p)
+		  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(11, 20) g;
+step rxy1: select sum(id) from spgist_box_tbl where
+		  p @> box(point(5000,5000),point(15000,15000));
+sum            
+
+375750         
+step c2: COMMIT;
+step wx1: insert into spgist_box_tbl (id, p)
+                  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(1, 10) g;
+ERROR:  could not serialize access due to read/write dependencies among transactions
+step c1: COMMIT;
+
+starting permutation: rxy2 wy2 c2 rxy1 wx1 c1
+step rxy2: select sum(id) from spgist_box_tbl where
+		  p <@ box(point(5000,5000),point(15000,15000));
+sum            
+
+125250         
+step wy2: insert into spgist_box_tbl (id, p)
+		  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(11, 20) g;
+step c2: COMMIT;
+step rxy1: select sum(id) from spgist_box_tbl where
+		  p @> box(point(5000,5000),point(15000,15000));
+sum            
+
+375905         
+step wx1: insert into spgist_box_tbl (id, p)
+                  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(1, 10) g;
+step c1: COMMIT;
diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_schedule
index 32c965b..d88dbd1 100644
--- a/src/test/isolation/isolation_schedule
+++ b/src/test/isolation/isolation_schedule
@@ -62,3 +62,5 @@ test: sequence-ddl
 test: async-notify
 test: vacuum-reltuples
 test: timeouts
+test: predicate-spgist
+test: predicate-spgist-2
diff --git a/src/test/isolation/specs/predicate-spgist-2.spec b/src/test/isolation/specs/predicate-spgist-2.spec
new file mode 100644
index 0000000..f0557e7
--- /dev/null
+++ b/src/test/isolation/specs/predicate-spgist-2.spec
@@ -0,0 +1,48 @@
+# Test for page level predicate locking in spgist
+#
+# Test to check false positives.
+#
+# Queries are written in such a way that an index scan(from one transaction) and an index insert(from another transaction) will try to access different parts(sub-tree) of the index.
+
+setup
+{
+ create table spgist_box_tbl(id int, p box);
+ create index spgist_boxidx on spgist_box_tbl using spgist(p);
+ insert into spgist_box_tbl (id, p)
+ select g, box(point(10000-g*10, 10000-g*10),point(10000+g*10, 10000+g*10))
+ from generate_series(1, 1000) g;
+}
+
+teardown
+{
+ DROP TABLE spgist_box_tbl;
+}
+
+session "s1"
+setup		{
+		  BEGIN ISOLATION LEVEL SERIALIZABLE;
+		  set enable_seqscan=off;
+		  set enable_bitmapscan=off;
+		  set enable_indexonlyscan=on;
+		}
+step "rxy1"	{ select sum(id) from spgist_box_tbl where
+		  p @> box(point(5000,5000),point(15000,15000)); }
+step "wx1"	{ insert into spgist_box_tbl (id, p)
+                  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(11, 20) 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(id) from spgist_box_tbl where
+                  p <@ box(point(5000,5000),point(15000,15000)); }
+step "wy2"	{ insert into spgist_box_tbl (id, p)
+		  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(1, 9) g; }
+step "c2"	{ COMMIT; }
diff --git a/src/test/isolation/specs/predicate-spgist.spec b/src/test/isolation/specs/predicate-spgist.spec
new file mode 100644
index 0000000..77f2bf3
--- /dev/null
+++ b/src/test/isolation/specs/predicate-spgist.spec
@@ -0,0 +1,48 @@
+# Test for page level predicate locking in spgist
+#
+# Test to verify serialization failures.
+#
+# Queries 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.
+
+setup
+{
+ create table spgist_box_tbl(id int, p box);
+ create index spgist_boxidx on spgist_box_tbl using spgist(p);
+ insert into spgist_box_tbl (id, p)
+ select g, box(point(10000-g*10, 10000-g*10),point(10000+g*10, 10000+g*10))
+ from generate_series(1, 1000) g;
+}
+
+teardown
+{
+ DROP TABLE spgist_box_tbl;
+}
+
+session "s1"
+setup		{
+		  BEGIN ISOLATION LEVEL SERIALIZABLE;
+		  set enable_seqscan=off;
+		  set enable_bitmapscan=off;
+		  set enable_indexonlyscan=on;
+		}
+step "rxy1"	{ select sum(id) from spgist_box_tbl where
+		  p @> box(point(5000,5000),point(15000,15000)); }
+step "wx1"	{ insert into spgist_box_tbl (id, p)
+                  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(1, 10) 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(id) from spgist_box_tbl where
+		  p <@ box(point(5000,5000),point(15000,15000)); }
+step "wy2"	{ insert into spgist_box_tbl (id, p)
+		  select g, box(point(10000-g*500,10000-g*500),point(10000+g*500,10000+g*500))
+		  from generate_series(11, 20) g; }
+step "c2"	{ COMMIT; }
-- 
1.9.1