Обсуждение: strange error occurs when adding index

Поиск
Список
Период
Сортировка

strange error occurs when adding index

От
Juan Backson
Дата:
Hi,
 
When I tried to add the following index, I get some strange error.  Does anyone know what these errors mean and how to fix it?
 
Here is the index query:
create index idx_product_items_digits on product_items using gist (digits gist_prefix_range_ops,product_id)
 
 
Here is the error:
NOTICE:  __pr_penalty(sa[], a1928901[]) orig->first=0 orig->last=0NOTICE:  __pr_penalty(sa[], 1206323[]) orig->first=0 orig->last=0
NOTICE:  __pr_penalty(a1206329[], 1206370[]) orig->first=0 orig->last=0NOTICE:  __pr_penalty(a1206329[], a1206328[]) orig->first=0 orig->last=0NOTICE:  __pr_penalty(a120632[8-9], 1206369[]) orig->first=56 orig->last=57
NOTICE:  __pr_penalty(a120632[8-9], a1206327[]) orig->first=56 orig->last=57NOTICE:  __pr_penalty(a120632[7-9], 1206368[]) orig->first=55 orig->last=57NOTICE:  __pr_penalty(a120632[7-9], a1206326[]) orig->first=55 orig->last=57NOTICE:  __pr_penalty(a120632[6-9], 1206367[]) orig->first=54 orig->last=57NOTICE:  __pr_penalty(a120632[6-9], a1206325[]) orig->first=54 orig->last=57NOTICE:  __pr_penalty(a120632[5-9], 1206366[]) orig->first=53 orig->last=57NOTICE:  __pr_penalty(a120632[5-9], a1206324[]) orig->first=53 orig->last=57NOTICE:  __pr_penalty(a120632[4-9], 1206365[]) orig->first=52 orig->last=57NOTICE:  __pr_penalty(a120632[4-9], a1206323[]) orig->first=52 orig->last=57NOTICE:  __pr_penalty(a120632[3-9], 1206364[]) orig->first=51 orig->last=57NOTICE:  __pr_penalty(a120632[3-9], a1206322[]) orig->first=51 orig->last=57NOTICE:  __pr_penalty(a120632[2-9], 1206363[]) orig->first=50 orig->last=57NOTICE:  __pr_penalty(a120632[2-9], a1206321[]) orig->first=50 orig->last=57NOTICE:  __pr_penalty(a120632[1-9], 1206362[]) orig->first=49 orig->last=57NOTICE:  __pr_penalty(a120632[1-9], a1206320[]) orig->first=49 orig->last=57NOTICE:  __pr_penalty(a120632[0-9], 1206361[]) orig->first=48 orig->last=57NOTICE:  __pr_penalty(a120632[0-9], a1206319[]) orig->first=48 orig->last=57NOTICE:  __pr_penalty(a12063[1-2], 1206359[]) orig->first=49 orig->last=50NOTICE:  __pr_penalty(a12063[1-2], a1206318[]) orig->first=49 orig->last=50NOTICE:  __pr_penalty(a12063[1-2], 1206358[]) orig->first=49 orig->last=50NOTICE:  __pr_penalty(a12063[1-2], a1206315[]) orig->first=49 orig->last=50NOTICE:  __pr_penalty(a12063[1-2], 1206357[]) orig->first=49 orig->last=50NOTICE:  __pr_penalty(a12063[1-2], a1206314[]) orig->first=49 orig->last=50NOTICE:  __pr_penalty(a12063[1-2], 1206356[]) orig->first=49 orig->last=50NOTICE:  __pr_penalty(a12063[1-2], a1206313[]) orig->first=49 orig->last=50NOTICE:  __pr_penalty(a12063[1-2], 1206355[]) orig->first=49 orig->last=50NOTICE:  __pr_penalty(a12063[1-2], a1206312[]) orig->first=49 orig->last=50NOTICE:  __pr_penalty(a12063[1-2], 1206354[]) orig->first=49 orig->last=50NOTICE:  __pr_penalty(a12063[1-2], a1206310[]) orig->first=49 orig->last=50NOTICE:  __pr_penalty(a12063[1-2], 1206353[]) orig->first=49 orig->last=50NOTICE:  __pr_penalty(a12063[1-2], a1206309[]) orig->first=49 orig->last=50NOTICE:  __pr_penalty(a12063[0-2], 1206352[]) orig->first=48 orig->last=50NOTICE:  __pr_penalty(a12063[0-2], a1206307[]) orig->first=48 orig->last=50NOTICE:  __pr_penalty(a12063[0-2], 1206351[]) orig->first=48 orig->last=50NOTICE:  __pr_penalty(a12063[0-2], a1206306[]) orig->first=48 orig->last=50NOTICE:  __pr_penalty(a12063[0-2], 1206350[]) orig->first=48 orig->last=50NOTICE:  __pr_penalty(a12063[0-2], a1206304[]) orig->first=48 orig->last=50NOTICE:  __pr_penalty(a12063[0-2], 1206349[]) orig->first=48 orig->last=50NOTICE:  __pr_penalty(a12063[0-2], a1206303[]) orig->first=48 orig->last=50NOTICE:  __pr_penalty(a12063[0-2], 1206347[]) orig->first=48 orig->last=50NOTICE:  __pr_penalty(a12063[0-2], a1206302[]) orig->first=48 orig->last=50NOTICE:  __pr_penalty(a12063[0-2], a1952240[]) orig->first=48 orig->last=50NOTICE:  __pr_penalty(a12063[0-2], a1206301[]) orig->first=48 orig->last=50NOTICE:  __pr_penalty(a12063[0-2], a1952240[]) orig->first=48 orig->last=50NOTICE:  __pr_penalty(a12063[0-2], a1206300[]) orig->first=48 orig->last=50NOTICE:  __pr_penalty(a12063[0-2], a1952240[]) orig->first=48 orig->last=50NOTICE:  __pr_penalty(a12063[0-2], a1206299[]) orig->first=48 orig->last=50NOTICE:  __pr_penalty(a1206[2-3], a1952240[]) orig->first=50 orig->last=51NOTICE:  __pr_penalty(a1206[2-3], a1206298[]) orig->first=50 orig->last=51NOTICE:  __pr_penalty(a1206[2-3], a1952240[]) orig->first=50 orig->last=51NOTICE:  __pr_penalty(a1206[2-3], a1206297[]) orig->first=50 orig->last=51NOTICE:  __pr_penalty(a1206[2-3], a1952240[]) orig->first=50 orig->last=51NOTICE:  __pr_penalty(a1206[2-3], a1206296[]) orig->first=50 orig->last=51NOTICE:  __pr_penalty(a1206[2-3], a1952240[]) orig->first=50 orig->last=51NOTICE:  __pr_penalty(a1206[2-3], a1206295[]) orig->firs
 
 
Thanks for your help.
 
jb

Re: strange error occurs when adding index

От
hubert depesz lubaczewski
Дата:
On Fri, Sep 04, 2009 at 05:42:31PM +0800, Juan Backson wrote:
> When I tried to add the following index, I get some strange error.  Does
> anyone know what these errors mean and how to fix it?
> Here is the index query:
> create index idx_product_items_digits on product_items using gist (digits
> gist_prefix_range_ops,product_id)

these are not errors, just notices. are you sure you have the latest
prefix version?

Best regards,

depesz

--
Linkedin: http://www.linkedin.com/in/depesz  /  blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

Re: strange error occurs when adding index

От
Dimitri Fontaine
Дата:
hubert depesz lubaczewski <depesz@depesz.com> writes:

> On Fri, Sep 04, 2009 at 05:42:31PM +0800, Juan Backson wrote:
>> When I tried to add the following index, I get some strange error.  Does
>> anyone know what these errors mean and how to fix it?
>> Here is the index query:
>> create index idx_product_items_digits on product_items using gist (digits
>> gist_prefix_range_ops,product_id)
>
> these are not errors, just notices. are you sure you have the latest
> prefix version?

I just realized earlier this week that the pgfoundry main page for
prefix was proposing the very old (as in avoid it) 0.2 version. It's now
fixed to list the current 1.0~rc2 version, which you'll find also in
debian testing and sid:
  http://pgfoundry.org/projects/prefix/
  http://packages.debian.org/search?searchon=sourcenames&keywords=prefix

This version still comes with #define DEBUG (hey, it's a release
candidate) and penalty() is chatty on some cases where it finds that
your prefix ranges are not containing only numbers, because the penalty
computation isn't really verified against the general case... but should
work: you just won a non-numeric-only prefix_range testing ticket :)

Have you got anything to report performance wise?

Given:
>>  __pr_penalty(sa[], a1928901[]) orig->first=0 orig->last=0
>> NOTICE:  __pr_penalty(sa[], 1206323[]) orig->first=0 orig->last=0

Could you report the result of:
  SELECT pr_penalty('sa', 'a1928901'), pr_penalty('sa', '1206323');

If you're happy with performances as is, I'll remove the NOTICE and
Assert(), if not, we'll have to either find a more general algorithm or
limit the accepted inputs.

Regards,
--
dim