On 27 November 2017 at 13:17, Alexander Korotkov <a.korot...@postgrespro.ru>
wrote:
> Hi, Shubham!
>
> On Wed, Nov 1, 2017 at 12:10 AM, Shubham Barai <shubhambara...@gmail.com>
> wrote:
>
>> On 9 October 2017 at 18:57, Alexander Korotkov <a.korot...@postgrespro.ru
>> > wrote:
>>
>>> Now, ITSM that predicate locks and conflict checks are placed right for
>>> now.
>>> However, it would be good to add couple comments to gistdoinsert() whose
>>> would state why do we call CheckForSerializableConflictIn() in these
>>> particular places.
>>>
>>> I also take a look at isolation tests. You made two separate test
>>> specs: one to verify that serialization failures do fire, and another to
>>> check there are no false positives.
>>> I wonder if we could merge this two test specs into one, but use more
>>> variety of statements with different keys for both inserts and selects.
>>>
>>
>> Please find the updated version of patch here. I have made suggested
>> changes.
>>
>
> In general, patch looks good for me now. I just see some cosmetic issues.
>
> /*
>> + *Check for any r-w conflicts (in serialisation isolation level)
>> + *just before we intend to modify the page
>> + */
>> + CheckForSerializableConflictIn(r, NULL, stack->buffer);
>> + /*
>
>
> Formatting doesn't look good here. You've missed space after star sign in
> the comment. You also missed newline after CheckForSerializableConflictIn()
> call.
>
> Also, you've long comment lines in predicate-gist.spec. Please, break
> long comments into multiple lines.
>
>
I have fixed formatting style. Please take a look at updated patch.
Regards,
Shubham
>
>
From d7780debdcce60340aebcef06bb03f12419dbbeb Mon Sep 17 00:00:00 2001
From: shubhambaraiss <y...@example.com>
Date: Sun, 1 Oct 2017 23:42:41 +0530
Subject: [PATCH] Predicate locking in gist index
---
src/backend/access/gist/gist.c | 21 +-
src/backend/access/gist/gistget.c | 3 +
src/backend/storage/lmgr/README-SSI | 5 +-
src/test/isolation/expected/predicate-gist.out | 659 +++++++++++++++++++++++++
src/test/isolation/isolation_schedule | 2 +
src/test/isolation/specs/predicate-gist.spec | 117 +++++
6 files changed, 804 insertions(+), 3 deletions(-)
mode change 100644 => 100755 src/backend/access/gist/gist.c
mode change 100644 => 100755 src/backend/access/gist/gistget.c
mode change 100644 => 100755 src/backend/storage/lmgr/README-SSI
create mode 100644 src/test/isolation/expected/predicate-gist.out
create mode 100644 src/test/isolation/specs/predicate-gist.spec
diff --git a/src/backend/access/gist/gist.c b/src/backend/access/gist/gist.c
old mode 100644
new mode 100755
index 565525b..74e8c7c
--- a/src/backend/access/gist/gist.c
+++ b/src/backend/access/gist/gist.c
@@ -18,6 +18,8 @@
#include "access/gistscan.h"
#include "catalog/pg_collation.h"
#include "miscadmin.h"
+#include "storage/lmgr.h"
+#include "storage/predicate.h"
#include "nodes/execnodes.h"
#include "utils/builtins.h"
#include "utils/index_selfuncs.h"
@@ -70,7 +72,7 @@ gisthandler(PG_FUNCTION_ARGS)
amroutine->amsearchnulls = true;
amroutine->amstorage = true;
amroutine->amclusterable = true;
- amroutine->ampredlocks = false;
+ amroutine->ampredlocks = true;
amroutine->amcanparallel = false;
amroutine->amkeytype = InvalidOid;
@@ -446,6 +448,11 @@ gistplacetopage(Relation rel, Size freespace, GISTSTATE *giststate,
GistPageSetNSN(ptr->page, oldnsn);
}
+ for (ptr = dist; ptr; ptr = ptr->next)
+ PredicateLockPageSplit(rel,
+ BufferGetBlockNumber(buffer),
+ BufferGetBlockNumber(ptr->buffer));
+
/*
* gistXLogSplit() needs to WAL log a lot of pages, prepare WAL
* insertion for that. NB: The number of pages and data segments
@@ -734,6 +741,12 @@ gistdoinsert(Relation r, IndexTuple itup, Size freespace, GISTSTATE *giststate)
}
/*
+ * Check for any r-w conflicts (in serialisation isolation level)
+ * just before we intend to modify the page
+ */
+ CheckForSerializableConflictIn(r, NULL, stack->buffer);
+
+ /*
* Update the tuple.
*
* We still hold the lock after gistinserttuple(), but it
@@ -827,6 +840,12 @@ gistdoinsert(Relation r, IndexTuple itup, Size freespace, GISTSTATE *giststate)
}
}
+ /*
+ * Check for any r-w conflicts (in serialisation isolation level)
+ * just before we intend to modify the page
+ */
+ CheckForSerializableConflictIn(r, NULL, stack->buffer);
+
/* now state.stack->(page, buffer and blkno) points to leaf page */
gistinserttuple(&state, stack, giststate, itup,
diff --git a/src/backend/access/gist/gistget.c b/src/backend/access/gist/gistget.c
old mode 100644
new mode 100755
index 760ea0c..4fe5be2
--- a/src/backend/access/gist/gistget.c
+++ b/src/backend/access/gist/gistget.c
@@ -18,6 +18,8 @@
#include "access/relscan.h"
#include "catalog/pg_type.h"
#include "miscadmin.h"
+#include "storage/lmgr.h"
+#include "storage/predicate.h"
#include "pgstat.h"
#include "lib/pairingheap.h"
#include "utils/builtins.h"
@@ -336,6 +338,7 @@ gistScanPage(IndexScanDesc scan, GISTSearchItem *pageItem, double *myDistances,
buffer = ReadBuffer(scan->indexRelation, pageItem->blkno);
LockBuffer(buffer, GIST_SHARE);
+ PredicateLockPage(r, BufferGetBlockNumber(buffer), scan->xs_snapshot);
gistcheckpage(scan->indexRelation, buffer);
page = BufferGetPage(buffer);
TestForOldSnapshot(scan->xs_snapshot, r, page);
diff --git a/src/backend/storage/lmgr/README-SSI b/src/backend/storage/lmgr/README-SSI
old mode 100644
new mode 100755
index a9dc01f..e221241
--- a/src/backend/storage/lmgr/README-SSI
+++ b/src/backend/storage/lmgr/README-SSI
@@ -374,10 +374,11 @@ however, a search discovers that no root page has yet been created, a
predicate lock on the index relation is required.
* GiST searches can determine that there are no matches at any
-level of the index, so there must be a predicate lock at each index
+level of the index, so we acquire predicate lock at each index
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.
+conflicting predicate locks may exist. In case there is a page split,
+we need to copy predicate lock from an original page to all new pages.
* The effects of page splits, overflows, consolidations, and
removals must be carefully reviewed to ensure that predicate locks
diff --git a/src/test/isolation/expected/predicate-gist.out b/src/test/isolation/expected/predicate-gist.out
new file mode 100644
index 0000000..ca11510
--- /dev/null
+++ b/src/test/isolation/expected/predicate-gist.out
@@ -0,0 +1,659 @@
+Parsed test spec with 2 sessions
+
+starting permutation: rxy1 wx1 c1 rxy2 wy2 c2
+step rxy1: select sum(p[0]) from gist_point_tbl where p << point(2500, 2500);
+sum
+
+311250
+step wx1: insert into gist_point_tbl (id, p)
+ select g, point(g*500, g*500) from generate_series(15, 20) g;
+step c1: COMMIT;
+step rxy2: select sum(p[0]) from gist_point_tbl where p >> point(7500,7500);
+sum
+
+2233750
+step wy2: insert into gist_point_tbl (id, p)
+ select g, point(g*500, g*500) from generate_series(1, 5) g;
+step c2: COMMIT;
+
+starting permutation: rxy2 wy2 c2 rxy1 wx1 c1
+step rxy2: select sum(p[0]) from gist_point_tbl where p >> point(7500,7500);
+sum
+
+2188750
+step wy2: insert into gist_point_tbl (id, p)
+ select g, point(g*500, g*500) from generate_series(1, 5) g;
+step c2: COMMIT;
+step rxy1: select sum(p[0]) from gist_point_tbl where p << point(2500, 2500);
+sum
+
+316250
+step wx1: insert into gist_point_tbl (id, p)
+ select g, point(g*500, g*500) from generate_series(15, 20) g;
+step c1: COMMIT;
+
+starting permutation: rxy3 wx3 c1 rxy4 wy4 c2
+step rxy3: select sum(p[0]) from gist_point_tbl where p >> point(6000,6000);
+sum
+
+3202000
+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;
+step rxy4: select sum(p[0]) from gist_point_tbl where p << point(1000,1000);
+sum
+
+49500
+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;
+
+starting permutation: rxy4 wy4 c2 rxy3 wx3 c1
+step rxy4: select sum(p[0]) from gist_point_tbl where p << point(1000,1000);
+sum
+
+49500
+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;
+step rxy3: select sum(p[0]) from gist_point_tbl where p >> point(6000,6000);
+sum
+
+3202000
+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;
+
+starting permutation: rxy1 wx1 rxy2 c1 wy2 c2
+step rxy1: select sum(p[0]) from gist_point_tbl where p << point(2500, 2500);
+sum
+
+311250
+step wx1: insert into gist_point_tbl (id, p)
+ select g, point(g*500, g*500) from generate_series(15, 20) g;
+step rxy2: select sum(p[0]) from gist_point_tbl where p >> point(7500,7500);
+sum
+
+2188750
+step c1: COMMIT;
+step wy2: insert into gist_point_tbl (id, p)
+ select g, point(g*500, g*500) from generate_series(1, 5) 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(p[0]) from gist_point_tbl where p << point(2500, 2500);
+sum
+
+311250
+step wx1: insert into gist_point_tbl (id, p)
+ select g, point(g*500, g*500) from generate_series(15, 20) g;
+step rxy2: select sum(p[0]) from gist_point_tbl where p >> point(7500,7500);
+sum
+
+2188750
+step wy2: insert into gist_point_tbl (id, p)
+ select g, point(g*500, g*500) from generate_series(1, 5) 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(p[0]) from gist_point_tbl where p << point(2500, 2500);
+sum
+
+311250
+step wx1: insert into gist_point_tbl (id, p)
+ select g, point(g*500, g*500) from generate_series(15, 20) g;
+step rxy2: select sum(p[0]) from gist_point_tbl where p >> point(7500,7500);
+sum
+
+2188750
+step wy2: insert into gist_point_tbl (id, p)
+ select g, point(g*500, g*500) from generate_series(1, 5) 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(p[0]) from gist_point_tbl where p << point(2500, 2500);
+sum
+
+311250
+step rxy2: select sum(p[0]) from gist_point_tbl where p >> point(7500,7500);
+sum
+
+2188750
+step wx1: insert into gist_point_tbl (id, p)
+ select g, point(g*500, g*500) from generate_series(15, 20) g;
+step c1: COMMIT;
+step wy2: insert into gist_point_tbl (id, p)
+ select g, point(g*500, g*500) from generate_series(1, 5) 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(p[0]) from gist_point_tbl where p << point(2500, 2500);
+sum
+
+311250
+step rxy2: select sum(p[0]) from gist_point_tbl where p >> point(7500,7500);
+sum
+
+2188750
+step wx1: insert into gist_point_tbl (id, p)
+ select g, point(g*500, g*500) from generate_series(15, 20) g;
+step wy2: insert into gist_point_tbl (id, p)
+ select g, point(g*500, g*500) from generate_series(1, 5) 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(p[0]) from gist_point_tbl where p << point(2500, 2500);
+sum
+
+311250
+step rxy2: select sum(p[0]) from gist_point_tbl where p >> point(7500,7500);
+sum
+
+2188750
+step wx1: insert into gist_point_tbl (id, p)
+ select g, point(g*500, g*500) from generate_series(15, 20) g;
+step wy2: insert into gist_point_tbl (id, p)
+ select g, point(g*500, g*500) from generate_series(1, 5) 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(p[0]) from gist_point_tbl where p << point(2500, 2500);
+sum
+
+311250
+step rxy2: select sum(p[0]) from gist_point_tbl where p >> point(7500,7500);
+sum
+
+2188750
+step wy2: insert into gist_point_tbl (id, p)
+ select g, point(g*500, g*500) from generate_series(1, 5) g;
+step wx1: insert into gist_point_tbl (id, p)
+ select g, point(g*500, g*500) from generate_series(15, 20) 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(p[0]) from gist_point_tbl where p << point(2500, 2500);
+sum
+
+311250
+step rxy2: select sum(p[0]) from gist_point_tbl where p >> point(7500,7500);
+sum
+
+2188750
+step wy2: insert into gist_point_tbl (id, p)
+ select g, point(g*500, g*500) from generate_series(1, 5) g;
+step wx1: insert into gist_point_tbl (id, p)
+ select g, point(g*500, g*500) from generate_series(15, 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 c2 wx1 c1
+step rxy1: select sum(p[0]) from gist_point_tbl where p << point(2500, 2500);
+sum
+
+311250
+step rxy2: select sum(p[0]) from gist_point_tbl where p >> point(7500,7500);
+sum
+
+2188750
+step wy2: insert into gist_point_tbl (id, p)
+ select g, point(g*500, g*500) from generate_series(1, 5) g;
+step c2: COMMIT;
+step wx1: insert into gist_point_tbl (id, p)
+ select g, point(g*500, g*500) from generate_series(15, 20) 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(p[0]) from gist_point_tbl where p >> point(7500,7500);
+sum
+
+2188750
+step rxy1: select sum(p[0]) from gist_point_tbl where p << point(2500, 2500);
+sum
+
+311250
+step wx1: insert into gist_point_tbl (id, p)
+ select g, point(g*500, g*500) from generate_series(15, 20) g;
+step c1: COMMIT;
+step wy2: insert into gist_point_tbl (id, p)
+ select g, point(g*500, g*500) from generate_series(1, 5) 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(p[0]) from gist_point_tbl where p >> point(7500,7500);
+sum
+
+2188750
+step rxy1: select sum(p[0]) from gist_point_tbl where p << point(2500, 2500);
+sum
+
+311250
+step wx1: insert into gist_point_tbl (id, p)
+ select g, point(g*500, g*500) from generate_series(15, 20) g;
+step wy2: insert into gist_point_tbl (id, p)
+ select g, point(g*500, g*500) from generate_series(1, 5) 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(p[0]) from gist_point_tbl where p >> point(7500,7500);
+sum
+
+2188750
+step rxy1: select sum(p[0]) from gist_point_tbl where p << point(2500, 2500);
+sum
+
+311250
+step wx1: insert into gist_point_tbl (id, p)
+ select g, point(g*500, g*500) from generate_series(15, 20) g;
+step wy2: insert into gist_point_tbl (id, p)
+ select g, point(g*500, g*500) from generate_series(1, 5) 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(p[0]) from gist_point_tbl where p >> point(7500,7500);
+sum
+
+2188750
+step rxy1: select sum(p[0]) from gist_point_tbl where p << point(2500, 2500);
+sum
+
+311250
+step wy2: insert into gist_point_tbl (id, p)
+ select g, point(g*500, g*500) from generate_series(1, 5) g;
+step wx1: insert into gist_point_tbl (id, p)
+ select g, point(g*500, g*500) from generate_series(15, 20) 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(p[0]) from gist_point_tbl where p >> point(7500,7500);
+sum
+
+2188750
+step rxy1: select sum(p[0]) from gist_point_tbl where p << point(2500, 2500);
+sum
+
+311250
+step wy2: insert into gist_point_tbl (id, p)
+ select g, point(g*500, g*500) from generate_series(1, 5) g;
+step wx1: insert into gist_point_tbl (id, p)
+ select g, point(g*500, g*500) from generate_series(15, 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 c2 wx1 c1
+step rxy2: select sum(p[0]) from gist_point_tbl where p >> point(7500,7500);
+sum
+
+2188750
+step rxy1: select sum(p[0]) from gist_point_tbl where p << point(2500, 2500);
+sum
+
+311250
+step wy2: insert into gist_point_tbl (id, p)
+ select g, point(g*500, g*500) from generate_series(1, 5) g;
+step c2: COMMIT;
+step wx1: insert into gist_point_tbl (id, p)
+ select g, point(g*500, g*500) from generate_series(15, 20) 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(p[0]) from gist_point_tbl where p >> point(7500,7500);
+sum
+
+2188750
+step wy2: insert into gist_point_tbl (id, p)
+ select g, point(g*500, g*500) from generate_series(1, 5) g;
+step rxy1: select sum(p[0]) from gist_point_tbl where p << point(2500, 2500);
+sum
+
+311250
+step wx1: insert into gist_point_tbl (id, p)
+ select g, point(g*500, g*500) from generate_series(15, 20) 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(p[0]) from gist_point_tbl where p >> point(7500,7500);
+sum
+
+2188750
+step wy2: insert into gist_point_tbl (id, p)
+ select g, point(g*500, g*500) from generate_series(1, 5) g;
+step rxy1: select sum(p[0]) from gist_point_tbl where p << point(2500, 2500);
+sum
+
+311250
+step wx1: insert into gist_point_tbl (id, p)
+ select g, point(g*500, g*500) from generate_series(15, 20) 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(p[0]) from gist_point_tbl where p >> point(7500,7500);
+sum
+
+2188750
+step wy2: insert into gist_point_tbl (id, p)
+ select g, point(g*500, g*500) from generate_series(1, 5) g;
+step rxy1: select sum(p[0]) from gist_point_tbl where p << point(2500, 2500);
+sum
+
+311250
+step c2: COMMIT;
+step wx1: insert into gist_point_tbl (id, p)
+ select g, point(g*500, g*500) from generate_series(15, 20) g;
+ERROR: could not serialize access due to read/write dependencies among transactions
+step c1: COMMIT;
+
+starting permutation: rxy3 wx3 rxy4 c1 wy4 c2
+step rxy3: select sum(p[0]) from gist_point_tbl where p >> point(6000,6000);
+sum
+
+3202000
+step wx3: insert into gist_point_tbl (id, p)
+ select g, point(g*500, g*500) from generate_series(12, 18) g;
+step rxy4: select sum(p[0]) from gist_point_tbl where p << point(1000,1000);
+sum
+
+49500
+step c1: COMMIT;
+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;
+
+starting permutation: rxy3 wx3 rxy4 wy4 c1 c2
+step rxy3: select sum(p[0]) from gist_point_tbl where p >> point(6000,6000);
+sum
+
+3202000
+step wx3: insert into gist_point_tbl (id, p)
+ select g, point(g*500, g*500) from generate_series(12, 18) g;
+step rxy4: select sum(p[0]) from gist_point_tbl where p << point(1000,1000);
+sum
+
+49500
+step wy4: insert into gist_point_tbl (id, p)
+ select g, point(g*50, g*50) from generate_series(1, 20) g;
+step c1: COMMIT;
+step c2: COMMIT;
+
+starting permutation: rxy3 wx3 rxy4 wy4 c2 c1
+step rxy3: select sum(p[0]) from gist_point_tbl where p >> point(6000,6000);
+sum
+
+3202000
+step wx3: insert into gist_point_tbl (id, p)
+ select g, point(g*500, g*500) from generate_series(12, 18) g;
+step rxy4: select sum(p[0]) from gist_point_tbl where p << point(1000,1000);
+sum
+
+49500
+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;
+step c1: COMMIT;
+
+starting permutation: rxy3 rxy4 wx3 c1 wy4 c2
+step rxy3: select sum(p[0]) from gist_point_tbl where p >> point(6000,6000);
+sum
+
+3202000
+step rxy4: select sum(p[0]) from gist_point_tbl where p << point(1000,1000);
+sum
+
+49500
+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;
+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;
+
+starting permutation: rxy3 rxy4 wx3 wy4 c1 c2
+step rxy3: select sum(p[0]) from gist_point_tbl where p >> point(6000,6000);
+sum
+
+3202000
+step rxy4: select sum(p[0]) from gist_point_tbl where p << point(1000,1000);
+sum
+
+49500
+step wx3: insert into gist_point_tbl (id, p)
+ select g, point(g*500, g*500) from generate_series(12, 18) g;
+step wy4: insert into gist_point_tbl (id, p)
+ select g, point(g*50, g*50) from generate_series(1, 20) g;
+step c1: COMMIT;
+step c2: COMMIT;
+
+starting permutation: rxy3 rxy4 wx3 wy4 c2 c1
+step rxy3: select sum(p[0]) from gist_point_tbl where p >> point(6000,6000);
+sum
+
+3202000
+step rxy4: select sum(p[0]) from gist_point_tbl where p << point(1000,1000);
+sum
+
+49500
+step wx3: insert into gist_point_tbl (id, p)
+ select g, point(g*500, g*500) from generate_series(12, 18) g;
+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;
+step c1: COMMIT;
+
+starting permutation: rxy3 rxy4 wy4 wx3 c1 c2
+step rxy3: select sum(p[0]) from gist_point_tbl where p >> point(6000,6000);
+sum
+
+3202000
+step rxy4: select sum(p[0]) from gist_point_tbl where p << point(1000,1000);
+sum
+
+49500
+step wy4: insert into gist_point_tbl (id, p)
+ select g, point(g*50, g*50) from generate_series(1, 20) g;
+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;
+step c2: COMMIT;
+
+starting permutation: rxy3 rxy4 wy4 wx3 c2 c1
+step rxy3: select sum(p[0]) from gist_point_tbl where p >> point(6000,6000);
+sum
+
+3202000
+step rxy4: select sum(p[0]) from gist_point_tbl where p << point(1000,1000);
+sum
+
+49500
+step wy4: insert into gist_point_tbl (id, p)
+ select g, point(g*50, g*50) from generate_series(1, 20) g;
+step wx3: insert into gist_point_tbl (id, p)
+ select g, point(g*500, g*500) from generate_series(12, 18) g;
+step c2: COMMIT;
+step c1: COMMIT;
+
+starting permutation: rxy3 rxy4 wy4 c2 wx3 c1
+step rxy3: select sum(p[0]) from gist_point_tbl where p >> point(6000,6000);
+sum
+
+3202000
+step rxy4: select sum(p[0]) from gist_point_tbl where p << point(1000,1000);
+sum
+
+49500
+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;
+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;
+
+starting permutation: rxy4 rxy3 wx3 c1 wy4 c2
+step rxy4: select sum(p[0]) from gist_point_tbl where p << point(1000,1000);
+sum
+
+49500
+step rxy3: select sum(p[0]) from gist_point_tbl where p >> point(6000,6000);
+sum
+
+3202000
+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;
+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;
+
+starting permutation: rxy4 rxy3 wx3 wy4 c1 c2
+step rxy4: select sum(p[0]) from gist_point_tbl where p << point(1000,1000);
+sum
+
+49500
+step rxy3: select sum(p[0]) from gist_point_tbl where p >> point(6000,6000);
+sum
+
+3202000
+step wx3: insert into gist_point_tbl (id, p)
+ select g, point(g*500, g*500) from generate_series(12, 18) g;
+step wy4: insert into gist_point_tbl (id, p)
+ select g, point(g*50, g*50) from generate_series(1, 20) g;
+step c1: COMMIT;
+step c2: COMMIT;
+
+starting permutation: rxy4 rxy3 wx3 wy4 c2 c1
+step rxy4: select sum(p[0]) from gist_point_tbl where p << point(1000,1000);
+sum
+
+49500
+step rxy3: select sum(p[0]) from gist_point_tbl where p >> point(6000,6000);
+sum
+
+3202000
+step wx3: insert into gist_point_tbl (id, p)
+ select g, point(g*500, g*500) from generate_series(12, 18) g;
+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;
+step c1: COMMIT;
+
+starting permutation: rxy4 rxy3 wy4 wx3 c1 c2
+step rxy4: select sum(p[0]) from gist_point_tbl where p << point(1000,1000);
+sum
+
+49500
+step rxy3: select sum(p[0]) from gist_point_tbl where p >> point(6000,6000);
+sum
+
+3202000
+step wy4: insert into gist_point_tbl (id, p)
+ select g, point(g*50, g*50) from generate_series(1, 20) g;
+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;
+step c2: COMMIT;
+
+starting permutation: rxy4 rxy3 wy4 wx3 c2 c1
+step rxy4: select sum(p[0]) from gist_point_tbl where p << point(1000,1000);
+sum
+
+49500
+step rxy3: select sum(p[0]) from gist_point_tbl where p >> point(6000,6000);
+sum
+
+3202000
+step wy4: insert into gist_point_tbl (id, p)
+ select g, point(g*50, g*50) from generate_series(1, 20) g;
+step wx3: insert into gist_point_tbl (id, p)
+ select g, point(g*500, g*500) from generate_series(12, 18) g;
+step c2: COMMIT;
+step c1: COMMIT;
+
+starting permutation: rxy4 rxy3 wy4 c2 wx3 c1
+step rxy4: select sum(p[0]) from gist_point_tbl where p << point(1000,1000);
+sum
+
+49500
+step rxy3: select sum(p[0]) from gist_point_tbl where p >> point(6000,6000);
+sum
+
+3202000
+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;
+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;
+
+starting permutation: rxy4 wy4 rxy3 wx3 c1 c2
+step rxy4: select sum(p[0]) from gist_point_tbl where p << point(1000,1000);
+sum
+
+49500
+step wy4: insert into gist_point_tbl (id, p)
+ select g, point(g*50, g*50) from generate_series(1, 20) g;
+step rxy3: select sum(p[0]) from gist_point_tbl where p >> point(6000,6000);
+sum
+
+3202000
+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;
+step c2: COMMIT;
+
+starting permutation: rxy4 wy4 rxy3 wx3 c2 c1
+step rxy4: select sum(p[0]) from gist_point_tbl where p << point(1000,1000);
+sum
+
+49500
+step wy4: insert into gist_point_tbl (id, p)
+ select g, point(g*50, g*50) from generate_series(1, 20) g;
+step rxy3: select sum(p[0]) from gist_point_tbl where p >> point(6000,6000);
+sum
+
+3202000
+step wx3: insert into gist_point_tbl (id, p)
+ select g, point(g*500, g*500) from generate_series(12, 18) g;
+step c2: COMMIT;
+step c1: COMMIT;
+
+starting permutation: rxy4 wy4 rxy3 c2 wx3 c1
+step rxy4: select sum(p[0]) from gist_point_tbl where p << point(1000,1000);
+sum
+
+49500
+step wy4: insert into gist_point_tbl (id, p)
+ select g, point(g*50, g*50) from generate_series(1, 20) g;
+step rxy3: select sum(p[0]) from gist_point_tbl where p >> point(6000,6000);
+sum
+
+3202000
+step c2: COMMIT;
+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;
diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_schedule
index 32c965b..4fb9500 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-gist
+
diff --git a/src/test/isolation/specs/predicate-gist.spec b/src/test/isolation/specs/predicate-gist.spec
new file mode 100644
index 0000000..ce9973c
--- /dev/null
+++ b/src/test/isolation/specs/predicate-gist.spec
@@ -0,0 +1,117 @@
+# 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.
+#
+# To check reduced false positives, 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 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 comflict.
+
+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 comflict.
+
+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 comflict.
+
+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 comflict.
+
+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"
--
1.9.1
