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

Oh, thanks! I forgot to add gist_intbig_ops.
22 мая 2015 г. 4:57 пользователь "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> написал:

> I wrote:
> > cpro29a(at)gmail(dot)com writes:
> >> create table test_gist(id serial primary key, a1 int[],a2 int[], a3
> int[],
> >> a4 int[], a5 int[]);
> >> create index test_gist_i on test_gist using gist (a1,a2,a3,a4,a5);
>
> >> --Simple random array generator
> >> create or replace function rnd_arr(p_len int) returns int[]
> >> as $$
> >> select array_agg(val)
> >> from (select trunc(random()*1000.0)::int val
> >> from generate_series(1,trunc(random()*p_len*1.0)::int+1)
> >> ) v;
> >> $$ language sql;
>
> >> --##THIS FAILS:
> >> insert into test_gist(a1,a2,a3,a4,a5)
> >> select rnd_arr(10),rnd_arr(10),rnd_arr(10),rnd_arr(10),rnd_arr(10)
> >> from generate_series(1,100000);
> >> --##WITH MESSAGE:
> >> --ERROR: failed to add item to index page in "test_gist_i"
>
> > I can reproduce this on HEAD (after installing contrib/intarray).
>
> On closer inspection, the answer is "you should be using gist__intbig_ops".
> gist__intbig_ops uses a different representation that can deal with more
> distinct array entries than the default gist_int_ops opclass can.
>
> The reason for the failure is that the index entries have gotten up to
> over 4K apiece, so that splitting does not help (or maybe that GIST
> thinks it should be able to put two entries in a page and it can't).
>
> It'd be nice if the error thrown was a little clearer, but I don't think
> there is anything we can or should do to make this example actually
> succeed.
>
> Note that gist_int_ops' g_int_compress function does actually contain a
> check that warns you once you've got more than 200 distinct values in an
> index item. Unfortunately, this example is trying to cram five arrays
> into each index tuple, so the total tuple size exceeds what GIST can cope
> with before that check fires for any one array value. It doesn't look to
> me like g_int_compress has any way to know how many index columns there
> are, else maybe it could adjust its threshold for complaining.
>
> It might be reasonable for the core GIST code to emit some complaint about
> index tuple size rather than the very opaque "failed to add item" message.
> Not sure exactly what test is appropriate though. Should we be limiting
> GIST index entries to half a page?
>
> regards, tom lane
>