Re: BUG #4120: ERROR: cache lookup failed for function 0

Tom Lane wrote:

>> postgres=# select ''::varchar < ''::varchar;
>> ERROR:  cache lookup failed for function 0
>
> Interesting.  The symptom sounds vaguely familiar, though I can't find
> any relevant-looking CVS log entries right now.  I'd suggest updating to
> 8.2.7 before you spend much time looking at this.  If it's still there
> in 8.2.7, probably the most productive thing is to work on extracting
> a reproducible test case to send in.  I'd suggest approaching that by
> turning on log_statement to get a trace of everything the application
> does up to the failure, and then (assuming you can duplicate the failure
> by running that script) trimming it down to eliminate any private
> information.

Upgrading to 8.2.7 didn't fix it. Upon further investigation, I found
that I was getting the same error when I put a certain schema of my
database in the search_path. That led me at first to erroneously think
that when it started to fail it had become into a broken state. Actually
it happens every time that I put that particular schema in the
search_path. That reminded me of something and it turned out to be the
right track.

Here's a minimal test case:

pruebas=# CREATE FUNCTION vc_gt(varchar,varchar) RETURNS boolean
IMMUTABLE STRICT LANGUAGE SQL AS 'SELECT CAST($1 AS TEXT) > CAST($2 AS
TEXT);';
CREATE FUNCTION
pruebas=# CREATE OPERATOR > (
            PROCEDURE = vc_gt,
    LEFTARG = character varying,
    RIGHTARG = character varying,
    COMMUTATOR = <,
    RESTRICT = scalargtsel,
    JOIN = scalargtjoinsel
);
CREATE OPERATOR
pruebas=# SELECT ''::varchar < ''::varchar;
ERROR:  cache lookup failed for function 0

This was part of my workaround for bug #4083. I only needed MAX so I
only wrote the > operator. BTW, has #4083 been dismissed?

-- Pedro Gimeno