Обсуждение: 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