Re: BUG #13327: Error on insert (gist index)

<p dir="ltr">Oh, thanks! I forgot to add gist_intbig_ops.<div class="gmail_quote">22 мая 2015 г. 4:57 пользователь "Tom
Lane"<<a href="mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>> написал:<br type="attribution" /><blockquote
class="gmail_quote"style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">I wrote:<br /> > <a
href="mailto:cpro29a@gmail.com">cpro29a@gmail.com</a>writes:<br /> >> create table test_gist(id serial primary
key,a1 int[],a2 int[], a3 int[],<br /> >> a4 int[], a5 int[]);<br /> >> create index test_gist_i on
test_gistusing gist (a1,a2,a3,a4,a5);<br /><br /> >> --Simple random array generator<br /> >> create or
replacefunction rnd_arr(p_len int) returns int[]<br /> >> as $$<br /> >> select array_agg(val)<br />
>>from (select trunc(random()*1000.0)::int val<br /> >> from
generate_series(1,trunc(random()*p_len*1.0)::int+1)<br/> >> ) v;<br /> >> $$ language sql;<br /><br />
>>--##THIS FAILS:<br /> >> insert into test_gist(a1,a2,a3,a4,a5)<br /> >> select
rnd_arr(10),rnd_arr(10),rnd_arr(10),rnd_arr(10),rnd_arr(10)<br/> >> from generate_series(1,100000);<br />
>>--##WITH MESSAGE:<br /> >> --ERROR:  failed to add item to index page in "test_gist_i"<br /><br /> > I
canreproduce this on HEAD (after installing contrib/intarray).<br /><br /> On closer inspection, the answer is "you
shouldbe using gist__intbig_ops".<br /> gist__intbig_ops uses a different representation that can deal with more<br />
distinctarray entries than the default gist_int_ops opclass can.<br /><br /> The reason for the failure is that the
indexentries have gotten up to<br /> over 4K apiece, so that splitting does not help (or maybe that GIST<br /> thinks
itshould be able to put two entries in a page and it can't).<br /><br /> It'd be nice if the error thrown was a little
clearer,but I don't think<br /> there is anything we can or should do to make this example actually succeed.<br /><br
/>Note that gist_int_ops' g_int_compress function does actually contain a<br /> check that warns you once you've got
morethan 200 distinct values in an<br /> index item.  Unfortunately, this example is trying to cram five arrays<br />
intoeach index tuple, so the total tuple size exceeds what GIST can cope<br /> with before that check fires for any one
arrayvalue.  It doesn't look to<br /> me like g_int_compress has any way to know how many index columns there<br />
are,else maybe it could adjust its threshold for complaining.<br /><br /> It might be reasonable for the core GIST code
toemit some complaint about<br /> index tuple size rather than the very opaque "failed to add item" message.<br /> Not
sureexactly what test is appropriate though.  Should we be limiting<br /> GIST index entries to half a page?<br /><br
/>                        regards, tom lane<br /></blockquote></div>