Skip site navigation (1) Skip section navigation (2)

Re: Index opclass checking (was Re: Crash in PostgreSQL 7.0.b5.)

From: Frank Mayhar <frank(at)exit(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-bugs(at)postgresql(dot)org, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Index opclass checking (was Re: Crash in PostgreSQL 7.0.b5.)
Date: 2000-04-24 18:49:36
Message-ID: 200004241849.LAA16492@realtime.exit.com (view raw or flat)
Thread:
Lists: pgsql-bugs
Tom Lane wrote:
> Actually, I don't have to look very hard:
> 
> CREATE TABLE td_products ( grp  CHAR(2), cat  CHAR(2), sub  CHAR(2), vend_code CHAR(6), manu_part CHAR(20), part_num CHAR(15), descr  CHAR(50), cost  NUMERIC(10,2), retail  NUMERIC(10,2), qty  INT4, list_price NUMERIC(10,2), eff_date CHAR(11), tech_fax BOOLEAN, status  CHAR(1), upc  CHAR(15));
> [ snip ]
> CREATE INDEX prodcost_idx ON td_products USING BTREE (cost bpchar_ops);
> CREATE INDEX prodqty_idx ON td_products USING BTREE (qty bpchar_ops);
> 
> Since cost and qty are numeric and int4 respectively, applying bpchar
> comparison ops to them is a bad idea; the crash is no doubt due to
> trying to interpret an int4 value as a pointer to character string :-(.

Sorry, that got past me.

> Currently, if you specify an index opclass then the system assumes that
> you know what you are doing; there is no cross-check to see if the
> chosen operators will work with the column datatype.  That bothers me,
> but I hesitate to insert a type-compatibility check; I wonder whether
> there might be legitimate uses of comparison operators that would fail
> a normal type-compatibility check against the column datatype.

Well, certainly crashing violates the POTA; perhaps the bpchar comparison
op could just check the parameter and see if it looks reasonably like a
pointer.  Obviously anything like 0xfffffxxx" is unlikely to be a pointer
on any architecture of which I'm aware (although I'm sure there are some
warped, perverted architectures out there that use that, sigh).

> The short-term answer for Frank is "don't specify index opclasses in
> handwritten CREATE INDEX commands, unless you're really sure that you
> need something other than the default opclass for the datatype".

Got it.
-- 
Frank Mayhar frank(at)exit(dot)com	http://www.exit.com/

In response to

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2000-04-24 22:00:41
Subject: Re: Index opclass checking (was Re: Crash in PostgreSQL 7.0.b5.)
Previous:From: Tom LaneDate: 2000-04-24 18:14:58
Subject: Index opclass checking (was Re: Crash in PostgreSQL 7.0.b5.)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group