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

>> Wups, got it already. It happens on the second insert, luckily (the db is
>> HUGE :-). I've attached the offending SQL script.

> Got it, confirm seeing the crash here. I have to do real work now :-(
> but will look into it tonight.

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 :-(.

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.

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".

In the long term, does anyone have any thoughts about whether and how
to tighten up checking of index opclass selection?

regards, tom lane