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

The following bug has been logged online:

Bug reference:      4120
Logged by:          Pedro Gimeno
Email address:      pgsql-002@personal.formauri.es
PostgreSQL version: 8.2.5
Operating system:   Debian Etch + Backports
Description:        ERROR: cache lookup failed for function 0
Details:

I have an application running under Windows (tried both with XP and 2000)
which when run, gets the server to a point in which every comparison of
varchar < varchar gives the above error:

postgres=# select ''::varchar < ''::varchar;
ERROR:  cache lookup failed for function 0

It doesn't seem to matter where it's run: that last command was run in the
Debian box, so it doesn't look like a client-side problem. However I've been
unable to force that state by any other means but running my application.

Changing the direction of the comparison makes things work again, i.e. if I
write "SELECT b > a" instead of "SELECT a < b" it works.

Is there anything I can do to help tracking down this problem?

-- Pedro Gimeno

"Pedro Gimeno" <pgsql-002@personal.formauri.es> writes:
> I have an application running under Windows (tried both with XP and 2000)
> which when run, gets the server to a point in which every comparison of
> varchar < varchar gives the above error:

> 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.

            regards, tom lane

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

Pedro Gimeno <pgsql-002@personal.formauri.es> writes:
> Tom Lane wrote:
> 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

Ah.  You have actually created a "shell" operator varchar < varchar
by having written the commutator link.  The error message could be
more helpful, perhaps, but I can't get too excited about it considering
that it's a consequence of a bogus operator definition.  Either provide
the < operator or drop the COMMUTATOR property.

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

I don't think we'll do anything about it, no.

            regards, tom lane