BUG #5504: cache lookup failed for function

"Jan Merka" <merka@highsphere.net> writes:

After installing postgresql 8.4.4 from sources, I am getting an error
ERROR: cache lookup failed for function 2071

Test case:

SELECT date '2010-01-01' + interval '1 month';
ERROR: cache lookup failed for function 2071

Works for me:

psql (8.4.4)
Type "help" for help.

regression=# SELECT date '2010-01-01' + interval '1 month';
?column?
---------------------
2010-02-01 00:00:00
(1 row)

The error indicates a failure to fetch the pg_proc entry for
date_pl_interval, which might indicate system catalog corruption,
or it might mean there is something wrong with your postgres executable.
Can you do the command in other database(s) in the same installation?
What do you get from "select * from pg_proc where oid = 2071;" ?
(If nothing, also try it with enable_indexscan and enable_bitmapscan
turned off.)

regards, tom lane

On Sunday, June 13, 2010, Tom Lane wrote:

"Jan Merka" <merka@highsphere.net> writes:

After installing postgresql 8.4.4 from sources, I am getting an error
ERROR: cache lookup failed for function 2071

Test case:

SELECT date '2010-01-01' + interval '1 month';
ERROR: cache lookup failed for function 2071

Works for me:

psql (8.4.4)
Type "help" for help.

regression=# SELECT date '2010-01-01' + interval '1 month';
?column?
---------------------
2010-02-01 00:00:00
(1 row)

The error indicates a failure to fetch the pg_proc entry for
date_pl_interval, which might indicate system catalog corruption,

You are correct, at least one database must be corrupted.

In the corrupted database:
=> SELECT * from pg_proc WHERE oid = 2071;
proname | pronamespace | proowner | prolang | procost | prorows | provariadic
| proisagg | proiswindow | p
rosecdef | proisstrict | proretset | provolatile | pronargs | pronargdefaults
| prorettype | proargtypes |
proallargtypes | proargmodes | proargnames | proargdefaults | prosrc | probin
| proconfig | proacl
---------+--------------+----------+---------+---------+---------+-------------+----------+-------------+--
---------+-------------+-----------+-------------+----------+-----------------+------------+-------------+-
---------------+-------------+-------------+----------------+--------+--------+-----------+--------
(0 rows)

In the 'postgres' database:
=> SELECT * from pg_proc WHERE oid = 2071;
proname | pronamespace | proowner | prolang | procost | prorows |
provariadic | proisagg | proisw
indow | prosecdef | proisstrict | proretset | provolatile | pronargs |
pronargdefaults | prorettype | proar
gtypes | proallargtypes | proargmodes | proargnames | proargdefaults |
prosrc | probin | proconfi
g | proacl
------------------+--------------+----------+---------+---------+---------+-------------+----------+-------
------+-----------+-------------+-----------+-------------+----------+-----------------+------------+------
-------+----------------+-------------+-------------+----------------+------------------+--------+---------
--+--------
date_pl_interval | 11 | 10 | 12 | 1 | 0 |
0 | f | f
| f | t | f | i | 2 |
0 | 1114 | 1082
1186 | | | | |
date_pl_interval | |
|

or it might mean there is something wrong with your postgres executable.
Can you do the command in other database(s) in the same installation?
What do you get from "select * from pg_proc where oid = 2071;" ?
(If nothing, also try it with enable_indexscan and enable_bitmapscan
turned off.)

I have dumped the database with corrupted pg_proc and restored into a new
database and the error is now gone.

Would you like me to investigate more about the database corruption?

Cheers,

Jan