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

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/