strange error occurs when adding index

Started by Juan Backsonover 16 years ago3 messagesgeneral

juanbackson@gmail.com

over 16 years ago

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.

hubert depesz lubaczewski

depesz@depesz.com

over 16 years ago

In reply to: Juan Backson (#1)

Re: strange error occurs when adding index

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

Dimitri Fontaine

dimitri@2ndQuadrant.fr

over 16 years ago

In reply to: hubert depesz lubaczewski (#2)

Re: strange error occurs when adding index

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