Thread

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

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-04-24T18:14:58Z

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

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

   Frank Mayhar <frank@exit.com> — 2000-04-24T18:49:36Z

   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/
   
   

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

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-04-24T22:00:41Z

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