Hi,

we've got rather strange problem with updating and GiST indices.
Below is a test run:

drop table tst;
create table tst ( a int[], i int );
copy tst from stdin;
........
\.
create index tsti on tst using gist (a);
vacuum full analyze;


test=# update tst set i = i+10   where a && '{3,4}';
UPDATE 3267
test=# set enable_indexscan=off;
SET VARIABLE
test=# update tst set i = i+10   where a && '{3,4}';
UPDATE 4060
test=# select count(*) from tst   where a && '{3,4}';
 count
-------
  4060
(1 row)

test=# select version();
                            version
---------------------------------------------------------------
 PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.95.3
(1 row)

enabling gist indices cause some rows doesn't updating !
Please find attached test sql script (need to install contrib/intarray module)

	Regards,
		Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83


Sat, 25 May 2002 12:31:04 +0300 (GMT)
Oleg Bartunov <oleg@sai.msu.su>
strange update problem with 7.2.1
Sorry,

forget  to attach file.

Oleg
On Sat, 25 May 2002, Oleg Bartunov wrote:

> Hi,
>
> we've got rather strange problem with updating and GiST indices.
> Below is a test run:
>
> drop table tst;
> create table tst ( a int[], i int );
> copy tst from stdin;
> ........
> \.
> create index tsti on tst using gist (a);
> vacuum full analyze;
>
>
> test=# update tst set i = i+10   where a && '{3,4}';
> UPDATE 3267
> test=# set enable_indexscan=off;
> SET VARIABLE
> test=# update tst set i = i+10   where a && '{3,4}';
> UPDATE 4060
> test=# select count(*) from tst   where a && '{3,4}';
>  count
> -------
>   4060
> (1 row)
>
> test=# select version();
>                             version
> ---------------------------------------------------------------
>  PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.95.3
> (1 row)
>
> enabling gist indices cause some rows doesn't updating !
> Please find attached test sql script (need to install contrib/intarray module)
>
> 	Regards,
> 		Oleg
> _____________________________________________________________
> Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
> Sternberg Astronomical Institute, Moscow University (Russia)
> Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
> phone: +007(095)939-16-83, +007(095)939-23-83
>
>

	Regards,
		Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
Sat, 25 May 2002 12:32:06 +0300 (GMT)
Oleg Bartunov <oleg@sai.msu.su>
Re: strange update problem with 7.2.1
Oleg Bartunov <oleg@sai.msu.su> writes:
> test=# update tst set i = i+10   where a && '{3,4}';
> UPDATE 3267
> test=# set enable_indexscan=off;
> SET VARIABLE
> test=# update tst set i = i+10   where a && '{3,4}';
> UPDATE 4060

I get the same in current sources (in fact the number of rows updated
varies from try to try).  Are you sure it's not a problem with the
gist index mechanism?

			regards, tom lane

Sat, 25 May 2002 11:58:52 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: strange update problem with 7.2.1 
On Sat, 25 May 2002, Tom Lane wrote:

> Oleg Bartunov <oleg@sai.msu.su> writes:
> > test=# update tst set i = i+10   where a && '{3,4}';
> > UPDATE 3267
> > test=# set enable_indexscan=off;
> > SET VARIABLE
> > test=# update tst set i = i+10   where a && '{3,4}';
> > UPDATE 4060
>
> I get the same in current sources (in fact the number of rows updated
> varies from try to try).  Are you sure it's not a problem with the
> gist index mechanism?
>

We'll look once more, but code for select and update should be the same.

> 			regards, tom lane
>

	Regards,
		Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83


Sat, 25 May 2002 20:06:57 +0300 (GMT)
Oleg Bartunov <oleg@sai.msu.su>
Re: strange update problem with 7.2.1 
Oleg Bartunov <oleg@sai.msu.su> writes:
> On Sat, 25 May 2002, Tom Lane wrote:
>> I get the same in current sources (in fact the number of rows updated
>> varies from try to try).  Are you sure it's not a problem with the
>> gist index mechanism?

> We'll look once more, but code for select and update should be the same.

Yeah, but the update case is inserting more entries into the index.
I'm wondering if that causes the index scan's state to get corrupted
so that it misses scanning some entries.  btree has a carefully designed
algorithm to cope with this, but I have no idea how gist manages it.

			regards, tom lane

Sat, 25 May 2002 13:28:29 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: strange update problem with 7.2.1 
> Yeah, but the update case is inserting more entries into the index.
> I'm wondering if that causes the index scan's state to get corrupted
> so that it misses scanning some entries.  btree has a carefully designed
> algorithm to cope with this, but I have no idea how gist manages it.


Thank you, Tom. You give me a direction for looking. Attached patch fix
the problem with broken state. Please apply it for 7.2.2 and current cvs 
(sorry,
but I'll have a possibility to check it on current cvs only tomorrow).



-- 
Teodor Sigaev
teodor@stack.net
Sun, 26 May 2002 15:17:58 +0400
Teodor Sigaev <teodor@stack.net>
Re: strange update problem with 7.2.1
Just tested with 7.2.1. It works. We have one more patch (for rtree_gist)
to submit before 7.2.2 release.

	Oleg

On Sun, 26 May 2002, Teodor Sigaev wrote:

> > Yeah, but the update case is inserting more entries into the index.
> > I'm wondering if that causes the index scan's state to get corrupted
> > so that it misses scanning some entries.  btree has a carefully designed
> > algorithm to cope with this, but I have no idea how gist manages it.
>
>
> Thank you, Tom. You give me a direction for looking. Attached patch fix
> the problem with broken state. Please apply it for 7.2.2 and current cvs
> (sorry,
> but I'll have a possibility to check it on current cvs only tomorrow).
>
>
>
>

	Regards,
		Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83


Sun, 26 May 2002 16:05:32 +0300 (GMT)
Oleg Bartunov <oleg@sai.msu.su>
Re: strange update problem with 7.2.1
Tested it with current CVS. It works.

Oleg Bartunov wrote:
> Just tested with 7.2.1. It works. We have one more patch (for rtree_gist)
> to submit before 7.2.2 release.
> 
> 	Oleg
> 
> On Sun, 26 May 2002, Teodor Sigaev wrote:
> 
> 
>>>Yeah, but the update case is inserting more entries into the index.
>>>I'm wondering if that causes the index scan's state to get corrupted
>>>so that it misses scanning some entries.  btree has a carefully designed
>>>algorithm to cope with this, but I have no idea how gist manages it.
>>>
>>
>>Thank you, Tom. You give me a direction for looking. Attached patch fix
>>the problem with broken state. Please apply it for 7.2.2 and current cvs
>>(sorry,
>>but I'll have a possibility to check it on current cvs only tomorrow).
>>
>>
>>
>>
>>
> 
> 	Regards,
> 		Oleg
> _____________________________________________________________
> Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
> Sternberg Astronomical Institute, Moscow University (Russia)
> Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
> phone: +007(095)939-16-83, +007(095)939-23-83
> 
> 
> 


-- 
Teodor Sigaev
teodor@stack.net



Mon, 27 May 2002 12:35:26 +0400
Teodor Sigaev <teodor@stack.net>
Re: strange update problem with 7.2.1


Oleg Bartunov wrote:
> Just tested with 7.2.1. It works. We have one more patch (for rtree_gist)
> to submit before 7.2.2 release.
> 

Attached patch fix a bug with creating index. Bug was reported by Chris Hodgson 
<chodgson@refractions.net>. Please, apply it for 7.2.2 and current CVS.


-- 
Teodor Sigaev
teodor@stack.net



Mon, 27 May 2002 12:59:31 +0400
Teodor Sigaev <teodor@stack.net>
Re: strange update problem with 7.2.1
Sorry, forgot a patch...

Teodor Sigaev wrote:
> 
> 
> Oleg Bartunov wrote:
> 
>> Just tested with 7.2.1. It works. We have one more patch (for rtree_gist)
>> to submit before 7.2.2 release.
>>
> 
> Attached patch fix a bug with creating index. Bug was reported by Chris 
> Hodgson <chodgson@refractions.net>. Please, apply it for 7.2.2 and 
> current CVS.
> 
> 


-- 
Teodor Sigaev
teodor@stack.net
Mon, 27 May 2002 13:18:43 +0400
Teodor Sigaev <teodor@stack.net>
Re: strange update problem with 7.2.1
Teodor Sigaev <teodor@stack.net> writes:
>> Yeah, but the update case is inserting more entries into the index.
>> I'm wondering if that causes the index scan's state to get corrupted
>> so that it misses scanning some entries.

> Thank you, Tom. You give me a direction for looking. Attached patch fix
> the problem with broken state.

Hmm, is this patch really correct?  Removing the gistadjscans() call
from gistSplit seems wrong to me --- won't that miss reporting splits
on leaf pages?  Or does this not matter for some reason?

			regards, tom lane

Mon, 27 May 2002 17:54:08 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: strange update problem with 7.2.1 


Tom Lane wrote:
> Teodor Sigaev <teodor@stack.net> writes:
> 
>>>Yeah, but the update case is inserting more entries into the index.
>>>I'm wondering if that causes the index scan's state to get corrupted
>>>so that it misses scanning some entries.
>>>
> 
>>Thank you, Tom. You give me a direction for looking. Attached patch fix
>>the problem with broken state.
>>
> 
> Hmm, is this patch really correct?  Removing the gistadjscans() call
> from gistSplit seems wrong to me --- won't that miss reporting splits
> on leaf pages?  Or does this not matter for some reason?
> 
gistadjscans() is moving to gistlayerinsert. gistadjscans() must be called for 
parent of splitted page, but gistSplit doesn't know parent of current page and
gistlayerinsert return status of its action: inserted and (may be) splitted. So
we can call gistadjscans(GIST_SPLIT) in gistlayerinsert when it's need.




-- 
Teodor Sigaev
teodor@stack.net



Tue, 28 May 2002 11:37:29 +0400
Teodor Sigaev <teodor@stack.net>
Re: strange update problem with 7.2.1
Teodor Sigaev <teodor@stack.net> writes:
>> Hmm, is this patch really correct?  Removing the gistadjscans() call
>> from gistSplit seems wrong to me --- won't that miss reporting splits
>> on leaf pages?  Or does this not matter for some reason?

> gistadjscans() is moving to gistlayerinsert. gistadjscans() must be
> called for parent of splitted page, but gistSplit doesn't know parent
> of current page and gistlayerinsert return status of its action:
> inserted and (may be) splitted. So we can call
> gistadjscans(GIST_SPLIT) in gistlayerinsert when it's need.

But gistSplit is recursive.  Is there no need to worry about the
additional splits it might do internally?

			regards, tom lane

Tue, 28 May 2002 09:33:07 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: strange update problem with 7.2.1 


Tom Lane wrote:
> Teodor Sigaev <teodor@stack.net> writes:
> 
>>>Hmm, is this patch really correct?  Removing the gistadjscans() call
>>>from gistSplit seems wrong to me --- won't that miss reporting splits
>>>on leaf pages?  Or does this not matter for some reason?
>>
> 
>>gistadjscans() is moving to gistlayerinsert. gistadjscans() must be
>>called for parent of splitted page, but gistSplit doesn't know parent
>>of current page and gistlayerinsert return status of its action:
>>inserted and (may be) splitted. So we can call
>>gistadjscans(GIST_SPLIT) in gistlayerinsert when it's need.
> 
> 
> But gistSplit is recursive.  Is there no need to worry about the
> additional splits it might do internally?

Internally splits are doing before calling gistadjscans. All pages 
created by gistSplit will be inserted in the end of parent page.
GiST's indexes aren't a concurrent there for one call of gistadjscans 
will be sufficiant.





Tue, 28 May 2002 19:09:02 +0400
Teodor Sigaev <teodor@stack.net>
Re: strange update problem with 7.2.1
Teodor Sigaev <teodor@stack.net> writes:
> Internally splits are doing before calling gistadjscans. All pages 
> created by gistSplit will be inserted in the end of parent page.
> GiST's indexes aren't a concurrent there for one call of gistadjscans 
> will be sufficiant.

Oh, I see.  Thanks.

			regards, tom lane

Tue, 28 May 2002 11:10:25 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: strange update problem with 7.2.1 
Teodor Sigaev <teodor@stack.net> writes:
> Thank you, Tom. You give me a direction for looking. Attached patch fix
> the problem with broken state. Please apply it for 7.2.2 and current cvs 

Patch applied to current and REL7_2 branch.

			regards, tom lane

Tue, 28 May 2002 11:26:00 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: strange update problem with 7.2.1 
Teodor Sigaev <teodor@stack.net> writes:
>> Attached patch fix a bug with creating index. Bug was reported by Chris 
>> Hodgson <chodgson@refractions.net>. Please, apply it for 7.2.2 and 
>> current CVS.

Patch applied to both branches.

			regards, tom lane

Tue, 28 May 2002 11:26:28 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: strange update problem with 7.2.1 
On Tue, 28 May 2002, Tom Lane wrote:

> Teodor Sigaev <teodor@stack.net> writes:
> > Thank you, Tom. You give me a direction for looking. Attached patch fix
> > the problem with broken state. Please apply it for 7.2.2 and current cvs
>
> Patch applied to current and REL7_2 branch.

Is't time for 7.2.2 ?

>
> 			regards, tom lane
>

	Regards,
		Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83


Tue, 28 May 2002 20:45:32 +0300 (GMT)
Oleg Bartunov <oleg@sai.msu.su>
Re: strange update problem with 7.2.1 
Oleg Bartunov <oleg@sai.msu.su> writes:
> Is't time for 7.2.2 ?

I think we had agreed start of June for 7.2.2.

			regards, tom lane

Tue, 28 May 2002 15:01:12 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: strange update problem with 7.2.1 