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

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

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

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