Обсуждение: CREATE INDEX test_idx ON test (UPPER(varchar_field)) doesn't work...

Hi -Someone on -general suggested I bring this up here.  I'll try and
explain as much as I can.  If you need more information from me, please
let me know.  I think the easiest way to illustrate this is to just paste
in the output.  This is all happening on FreeBSD 3.4 running 7.0.2.

--------------------------------------------------------------------------
devloki=> CREATE TABLE test (field VARCHAR(10));
CREATE
devloki=> \d test                           Table "test"Attribute |    Type     | Modifier 
-----------+-------------+----------field     | varchar(10) | 

devloki=> INSERT INTO test VALUES ('test string');
INSERT 110505 1
devloki=> SELECT field FROM test;  field    
------------test strin
(1 row)

devloki=> SELECT UPPER(field) FROM test;  upper    
------------TEST STRIN
(1 row)

devloki=> CREATE INDEX test_idx ON test (field);
CREATE
devloki=> CREATE INDEX test_upper_idx ON test (UPPER(field));
ERROR:  DefineIndex: function 'upper(varchar)' does not exist
--------------------------------------------------------------------------

Is there any other information I can provide?  Should I send this on to
-bugs?

Thanks,

-philip

On Wed, 9 Aug 2000, Philip Hallstrom wrote:

> devloki=> SELECT UPPER(field) FROM test;
>    upper    
> ------------
>  TEST STRIN
> (1 row)
> 
> devloki=> CREATE INDEX test_idx ON test (field);
> CREATE
> devloki=> CREATE INDEX test_upper_idx ON test (UPPER(field));
> ERROR:  DefineIndex: function 'upper(varchar)' does not exist
> --------------------------------------------------------------------------
> 
> Is there any other information I can provide?  Should I send this on to
> -bugs?

I think the reason for this is that the function is
upper(text) returns text.  The select is willing to 
do the type conversion for you but the index creation 
is not.

I'm not 100% sure it's a good idea, but IIRC text and
varchar are binary compatible.  You probably could
get away with adding an entry in pg_proc for
upper(varchar) returns varchar using the same function
by adding a new row with only the prorettype and proargtypes 
changed.

Philip Hallstrom <philip@adhesivemedia.com> writes:
> devloki=> CREATE INDEX test_upper_idx ON test (UPPER(field));
> ERROR:  DefineIndex: function 'upper(varchar)' does not exist

This is a known bug.  There is indeed no upper(varchar) function
declared in pg_proc, but the parser knows that varchar is "binary
equivalent" to type text, so when you ask for upper(varchar) in
most contexts it will silently substitute upper(text) instead.
The bug is that CREATE INDEX does not provide the same leeway;
it wants to find an exact type-signature match.  It should accept
functions that are binary-compatible with the type being indexed.

This is on the to-do list and might make a good first backend-hacking
project, if anyone is annoyed enough by it to work on it before the
core developers get 'round to it.

BTW, I did just read over the discussion in pg-general (was out of town
so couldn't answer sooner) and I believe you could have made your
function work safely if it read
CREATE FUNCTION upper(VARCHAR) RETURNS TEXT AS '...RETURN UPPER($1::text);...

As you wrote it it's an infinite recursion, because as soon as you
provide a function upper(varchar), that will be selected in preference
to upper(text) for any varchar input value --- so "RETURN UPPER($1)" is
a self-reference.  But with the type coercion you should get a call to
the built-in upper(text) instead.

A faster way is the one someone else suggested: just create another row
in pg_proc that declares upper(varchar) as an alias for the built-in
upper(text).  For example,
CREATE FUNCTION upper(VARCHAR) RETURNS TEXT AS 'upper' LANGUAGE 'internal';

(You have to first look in pg_proc to confirm that the internal function
is in fact named 'upper' at the C level --- look at the 'prosrc' field.)

The infinite recursion should not have "locked up" your machine; if it
did I'd say that's a bad weakness in FreeBSD.  What I see on HPUX is a
coredump due to stack limit overrun within a second or two of invoking
an infinitely-recursive function.  Performance of other processes
doesn't seem to be hurt materially... although HPUX does take an
unreasonably long time to actually execute a coredump of a process
that's grown to a large size...
        regards, tom lane