Обсуждение: 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
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@exit.com http://www.exit.com/
>> 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. It'd be sufficient to prevent crashes if we checked that the actual data type of the column is binary-compatible with the declared input type of the operators associated with the opclass. This wouldn't take much additional code, either. I'm still worried that there might be useful applications for index opclasses that are not marked binary-compatible with the column data type. But I don't have any examples at hand, and preventing crashes due to user error is probably more important than allowing people to play type-cheat games. (Besides, if you have an example where this really works, you could evade the check by creating additional pg_proc entries pointing at the same executable code but showing the desired data type as the input type...) Unless someone comes up with a counterexample, I'll put in a cross-check that works like this. regards, tom lane