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 [EMAIL PROTECTED] http://www.exit.com/
