[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: [HACKERS] GSoC 2017 : Patch for predicate locking in Gist index
From: Shubham Barai <shubhambaraiss () gmail ! com>
Date: 2017-10-31 21:22:44
Message-ID: CALxAEPsCjOjUFWgPA8gmEtP1AnWz+FPeMeyZNLY9PxayxfEQRA () mail ! gmail ! com
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
On 9 October 2017 at 18:57, Alexander Korotkov <a.korotkov@postgrespro.ru>
wrote:
> On Thu, Oct 5, 2017 at 9:48 PM, Shubham Barai <shubhambaraiss@gmail.com>
> wrote:
>
>> On 3 October 2017 at 00:32, Alexander Korotkov <a.korotkov@postgrespro.ru
>> > wrote:
>>
>>> On Mon, Oct 2, 2017 at 9:11 PM, Andrew Borodin <amborodin86@gmail.com>
>>> wrote:
>>>
>>>> On Mon, Oct 2, 2017 at 8:00 PM, Alexander Korotkov
>>>> <a.korotkov@postgrespro.ru> wrote:
>>>> > What happen if exactly this "continue" fires?
>>>> >
>>>> >> if (GistFollowRight(stack->page))
>>>> >> {
>>>> >> if (!xlocked)
>>>> >> {
>>>> >> LockBuffer(stack->buffer, GIST_UNLOCK);
>>>> >> LockBuffer(stack->buffer, GIST_EXCLUSIVE);
>>>> >> xlocked = true;
>>>> >> /* someone might've completed the split when we unlocked */
>>>> >> if (!GistFollowRight(stack->page))
>>>> >> continue;
>>>> >
>>>> >
>>>> > In this case we might get xlocked == true without calling
>>>> > CheckForSerializableConflictIn().
>>>> Indeed! I've overlooked it. I'm remembering this issue, we were
>>>> considering not fixing splits if in Serializable isolation, but
>>>> dropped the idea.
>>>>
>>>
>>> Yeah, current insert algorithm assumes that split must be fixed before
>>> we can correctly traverse the tree downwards.
>>>
>>>
>>>> CheckForSerializableConflictIn() must be after every exclusive lock.
>>>>
>>>
>>> I'm not sure, that fixing split is the case to necessary call
>>> CheckForSerializableConflictIn(). This lock on leaf page is not taken
>>> to do modification of the page. It's just taken to ensure that nobody else
>>> is fixing this split the same this. After fixing the split, it might
>>> appear that insert would go to another page.
>>>
>>> > I think it would be rather safe and easy for understanding to more
>>>> > CheckForSerializableConflictIn() directly before gistinserttuple().
>>>> The difference is that after lock we have conditions to change page,
>>>> and before gistinserttuple() we have actual intent to change page.
>>>>
>>>> From the point of future development first version is better (if some
>>>> new calls occasionally spawn in), but it has 3 calls while your
>>>> proposal have 2 calls.
>>>> It seems to me that CheckForSerializableConflictIn() before
>>>> gistinserttuple() is better as for now.
>>>>
>>>
>>> Agree.
>>>
>>
>> I have updated the location of CheckForSerializableConflictIn() and
>> changed the status of the patch to "needs review".
>>
>
> 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.
Regards,
Shubham
[Attachment #5 (text/html)]
<div dir="ltr"><br><div class="gmail_extra"><br><div class="gmail_quote">On 9 October \
2017 at 18:57, Alexander Korotkov <span dir="ltr"><<a \
href="mailto:a.korotkov@postgrespro.ru" \
target="_blank">a.korotkov@postgrespro.ru</a>></span> wrote:<br><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"><div dir="ltr"><div class="gmail_extra"><div \
class="gmail_quote"><div><div class="h5">On Thu, Oct 5, 2017 at 9:48 PM, Shubham \
Barai <span dir="ltr"><<a href="mailto:shubhambaraiss@gmail.com" \
target="_blank">shubhambaraiss@gmail.com</a>></span> wrote:<br><blockquote \
class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div class="gmail_extra"><div \
class="gmail_quote"><div><div \
class="m_-4652876611427752082m_9167215152429618440gmail-h5">On 3 October 2017 at \
00:32, Alexander Korotkov <span dir="ltr"><<a \
href="mailto:a.korotkov@postgrespro.ru" \
target="_blank">a.korotkov@postgrespro.ru</a>></span> wrote:<br><blockquote \
class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div class="gmail_extra"><span \
class="m_-4652876611427752082m_9167215152429618440gmail-m_8860430334968544378gmail-"><div><div \
class="m_-4652876611427752082m_9167215152429618440gmail-m_8860430334968544378gmail_signature">On \
Mon, Oct 2, 2017 at 9:11 PM, Andrew Borodin <span dir="ltr"><<a \
href="mailto:amborodin86@gmail.com" \
target="_blank">amborodin86@gmail.com</a>></span> \
wrote:<br></div></div></span><div class="gmail_quote"><span \
class="m_-4652876611427752082m_9167215152429618440gmail-m_8860430334968544378gmail-"><blockquote \
class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex"><span \
class="m_-4652876611427752082m_9167215152429618440gmail-m_8860430334968544378gmail-m_8883943564049737893gmail-">On \
Mon, Oct 2, 2017 at 8:00 PM, Alexander Korotkov<br> <<a \
href="mailto:a.korotkov@postgrespro.ru" \
target="_blank">a.korotkov@postgrespro.ru</a>> wrote:<br> > What happen if \
exactly this "continue" fires?<br> ><br>
>> if (GistFollowRight(stack->page))<br>
>> {<br>
>> if (!xlocked)<br>
>> {<br>
>> LockBuffer(stack->buffer, GIST_UNLOCK);<br>
>> LockBuffer(stack->buffer, GIST_EXCLUSIVE);<br>
>> xlocked = true;<br>
>> /* someone might've completed the split when we unlocked */<br>
>> if (!GistFollowRight(stack->page)<wbr>)<br>
>> continue;<br>
><br>
><br>
> In this case we might get xlocked == true without calling<br>
> CheckForSerializableConflictIn<wbr>().<br>
</span>Indeed! I've overlooked it. I'm remembering this issue, we were<br>
considering not fixing splits if in Serializable isolation, but<br>
dropped the idea.<br></blockquote><div><br></div></span><div>Yeah, current insert \
algorithm assumes that split must be fixed before we can correctly traverse the tree \
downwards.</div><span \
class="m_-4652876611427752082m_9167215152429618440gmail-m_8860430334968544378gmail-"><div> \
</div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px \
solid rgb(204,204,204);padding-left:1ex"> CheckForSerializableConflictIn<wbr>() must \
be after every exclusive lock.<br></blockquote><div> </div></span><div>I'm not \
sure, that fixing split is the case to necessary call \
CheckForSerializableConflictIn<wbr>(). This lock on leaf page is not taken to do \
modification of the page. It's just taken to ensure that nobody else is fixing \
this split the same this. After fixing the split, it might appear that insert would \
go to another page.</div><span \
class="m_-4652876611427752082m_9167215152429618440gmail-m_8860430334968544378gmail-"><div><br></div><blockquote \
class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex"><span \
class="m_-4652876611427752082m_9167215152429618440gmail-m_8860430334968544378gmail-m_8883943564049737893gmail-">
> I think it would be rather safe and easy for understanding to more<br>
> CheckForSerializableConflictIn<wbr>() directly before gistinserttuple().<br>
</span>The difference is that after lock we have conditions to change page,<br>
and before gistinserttuple() we have actual intent to change page.<br>
<br>
From the point of future development first version is better (if some<br>
new calls occasionally spawn in), but it has 3 calls while your<br>
proposal have 2 calls.<br>
It seems to me that CheckForSerializableConflictIn<wbr>() before<br>
gistinserttuple() is better as for \
now.<br></blockquote><div><br></div></span><div>Agree.</div></div></div></div></blockquote><div><br></div></div></div><div>I \
have updated the location of CheckForSerializableConflictIn<wbr>() and changed \
the status of the patch to "needs \
review".</div></div></div></div></blockquote><div><br></div></div></div><div>Now, \
ITSM that predicate locks and conflict checks are placed right for \
now.</div><div>However, it would be good to add couple comments to gistdoinsert() \
whose would state why do we call CheckForSerializableConfl<wbr>ictIn() in these \
particular places.</div><div><br></div><div>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.</div><div>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.</div></div></div></div></blockquote><div><br></div><div>Please find the \
updated version of patch here. I have made suggested \
changes.<br></div><div><br></div><div>Regards,</div><div>Shubham<br></div></div></div></div>
["Predicate-locking-in-gist-index_4.patch" (application/octet-stream)]
From 4489079068ed9789ea70dbb016eb341d38ca163f Mon Sep 17 00:00:00 2001
From: shubhambaraiss <you@example.com>
Date: Sun, 1 Oct 2017 23:42:41 +0530
Subject: [PATCH] Predicate locking in gist index
---
src/backend/access/gist/gist.c | 20 +-
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 | 106 ++++
6 files changed, 792 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..849bbb7
--- 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,11 @@ 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 +839,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..62fd31d
--- /dev/null
+++ b/src/test/isolation/specs/predicate-gist.spec
@@ -0,0 +1,106 @@
+# 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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic