Re: plpgsql no longer exists

Bricklen Anderson wrote:
> Hi listers!
> I'll start with some details:
>
> select version();
> PostgreSQL 8.0.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.5
> (Debian 1:3.3.3-5)
>
> Upon compiling a new function that I was working on, I came across an
> error:
> "could not read block 0 of relation 1663/17239/16709: Bad address" which
> was being triggered by my trying to compile my function.
>
> The relation in question turned out to be pg_language. I reindexed that
> table and the "could not read block..." error went away, then I started
> to get the "...language "plpgsql" does not exist..." (as shown after the
> following function):
>
> -- note, this is only a test function, but it yields the same error:
> dev=# create function text() returns void as $$
> dev=# begin
> dev=# return;
> dev=# end;
> dev=# $$ language plpgsql;
> ERROR: language "plpgsql" does not exist
> HINT: You need to use "createlang" to load the language into the database.
>
> I then issued a "createlang plpgsql <this_db>"
>
> -- checked the pg_language table at this point (which I probably should
> have done before I went and ran the createlang command)
>
> dev=# select lanname,lanplcallfoid,lanvalidator from pg_language;
> lanname | lanplcallfoid | lanvalidator
> ---------------------------------------
> plpgsql | 17811 | 17812
> plpgsql | 17811 | 17812
> internal | 0 | 2246
> c | 0 | 2247
> plperlu | 17808 | 0
> plperl | 17808 | 0
> sql | 0 | 2248
>
> Apparently plpgsql does exist. It also now had duplicate entries for
> plpgsql. I replaced the contents of the table with the all of the same
> values, minus one of the duplicates and reindexed it. I restarted my
> postmaster, and the missing language error went away.
>
> Now when I am running any function, I am getting:
> "ERROR: cache lookup failed for language 17813" (or occasionally, 17810
> or 17809).
>
> Once I recompile the function, I no longer get that message. Is there
> anything else that I can check or do to make this stop happening? Or is
> this a sign of things to come (possible corruption, etc?)
>

I'm also seeing one of my functions fail with "ERROR: cache lookup failed for
function 0" -- although this function is using "language sql" rather than
plpgsql. This particular function is aggregating data from a view (which
currently has no data), so should just finish without doing anything like it
always has in the past.