Thread

1. CREATE INDEX test_idx ON test (UPPER(varchar_field)) doesn't work...

   Philip Hallstrom <philip@adhesivemedia.com> — 2000-08-10T03:54:25Z

   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
   
   
   

2. Re: CREATE INDEX test_idx ON test (UPPER(varchar_field)) doesn't work...

   Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2000-08-10T04:49:34Z

   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.
   
   
   
   

3. Re: CREATE INDEX test_idx ON test (UPPER(varchar_field)) doesn't work...

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-08-11T14:38:39Z

   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