Re: BUG #5504: cache lookup failed for function

On Sunday, June 13, 2010, Tom Lane wrote:
> "Jan Merka" <merka(at)highsphere(dot)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
>
> regards, tom lane