On Tue, Jun 24, 2014 at 10:18 PM, Pavan Deolasee <pavan.deola...@gmail.com> wrote:
> > On Tue, Jun 24, 2014 at 3:40 PM, Kevin Grittner <kgri...@ymail.com> wrote: > > > > Soroosh Sardari <soroosh.sard...@gmail.com> wrote: > > > > > I check this problem with a virgin source code of > > > postgresql-9.3.2. So the bug is not for my codes. > > > > > By the way, following code has two different output and it is > > > weird. > > > > I can confirm that I see the difference in 9.3.2, and that I don't > > see the difference in 9.3.4. Upgrade. > > > > http://www.postgresql.org/support/versioning/ > > > > There's really no point in reporting a possible bug on a version > > with known bugs which have already had fixes published. > > > > FWIW I can reproduce this on HEAD with the attached patch. I could > reproduce this on a 64-bit Ubuntu as well as 64-bit Mac OSX. Very confusing > it is because I tried with various values for N in char[N] array and it > fails for N=20. Other values I tried are 4, 12, 22, 24 and the test passes > for all of them. The logic for trying other values is to see if pd_linp[] > starting on un-aligned boundary can trigger the issue. But there seem to be > no correlation. > > postgres=# select version(); > > PostgreSQL 9.5devel on x86_64-apple-darwin13.2.0, compiled by Apple LLVM > version 5.1 (clang-503.0.38) (based on LLVM 3.4svn), 64-bit > > postgres=# -- test SP-GiST index that's been built incrementally > > postgres=# create table test_range_spgist(ir int4range); > postgres=# create index test_range_spgist_idx on test_range_spgist using > spgist (ir); > postgres=# insert into test_range_spgist select int4range(g, g+10) from > generate_series(1,586) g; > INSERT 0 586 > > postgres=# SET enable_seqscan = t; > postgres=# SET enable_indexscan = f; > postgres=# SET enable_bitmapscan = f; > > postgres=# select * from test_range_spgist where ir -|- int4range(100,500); > > ir > ----------- > [90,100) > [500,510) > (2 rows) > > postgres=# SET enable_seqscan = f; > postgres=# select * from test_range_spgist where ir -|- int4range(100,500); > > ir > ----------- > [90,100) > [500,510) > (2 rows) > > At this point, both rows are visible via index scan as well as seq scan. > > postgres=# insert into test_range_spgist select int4range(g, g+10) from > generate_series(587,587) g; > INSERT 0 1 > > postgres=# select * from test_range_spgist where ir -|- int4range(100,500); > ir > ---------- > [90,100) > (1 row) > > Ouch. The second row somehow disappeared. > > postgres=# SET enable_seqscan = t; > postgres=# select * from test_range_spgist where ir -|- int4range(100,500); > > ir > ----------- > [90,100) > [500,510) > (2 rows) > > So the last INSERT suddenly makes one row disappear via the index scan > though its still reachable via seq scan. I tried looking at the SP-Gist > code but clearly I don't understand it a whole lot to figure out the issue, > if one exists. > > > Thanks, > Pavan > > -- > Pavan Deolasee > http://www.linkedin.com/in/pavandeolasee > Is there any plug in to examine each page of spgist index? Unfortunately pageinspect only work for btree index.