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