Thread: strange update problem with 7.2.1
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
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
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
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
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
> 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
Attachment
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
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
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
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
Attachment
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
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
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
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.
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
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
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
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
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