Thread: plpgsql no longer exists
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?) Thanks for any help! Cheers, Bricklen
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.
Bricklen Anderson <bricklen@shaw.ca> writes:
>> 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?)
Well, the original error sounds like a disk drive lossage ... you might
want to think about replacing that drive sometime soon, before it drops
data from someplace more critical than an index.
In the meantime, look through the pg_proc.prolang column for entries
that don't match the OID of any row in pg_language. Probably you
could just UPDATE the ones that are wrong to make them match the OIDs
of the new rows. I'd suggest a dump and restore of the database
after you think you have it right, just to make sure everything
is sane and to get pg_depend back in sync.
regards, tom lane
Tom Lane wrote:
> Bricklen Anderson <bricklen@shaw.ca> writes:
>
>>>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?)
>
>
> Well, the original error sounds like a disk drive lossage ... you might
> want to think about replacing that drive sometime soon, before it drops
> data from someplace more critical than an index.
>
> In the meantime, look through the pg_proc.prolang column for entries
> that don't match the OID of any row in pg_language. Probably you
> could just UPDATE the ones that are wrong to make them match the OIDs
> of the new rows. I'd suggest a dump and restore of the database
> after you think you have it right, just to make sure everything
> is sane and to get pg_depend back in sync.
>
> regards, tom lane
>
Hi Tom,
Thanks for the tips. I tried to match up pg_proc.prolang and _any_ OID in the
pg_language table. There were NO matches at all. This seemed a bit odd, so I
checked an other db and that one had virtually the same numbers (and they didn't
match either).
=# select distinct prolang from pg_proc;
prolang
---------
12
13
14
17813
63209
63212
63213
63214
(8 rows)
=# select * from pg_language ;
lanname | lanispl | lanpltrusted | lanplcallfoid | lanvalidator | lanacl
----------+---------+--------------+---------------+--------------+---------------
plpgsql | t | t | 17811 | 17812 |
internal | f | f | 0 | 2246 |
c | f | f | 0 | 2247 |
plperlu | t | f | 17808 | 0 |
sql | f | t | 0 | 2248 | {=U/postgres}
plperl | t | t | 17808 | 0 |
(6 rows)
Is it possible that I am misreading the output or your suggestion?
Bricklen Anderson <banderson@presinet.com> writes:
> =# select distinct prolang from pg_proc;
> prolang
> ---------
> 12
> 13
> 14
> 17813
> 63209
> 63212
> 63213
> 63214
> (8 rows)
That looks fine ...
> =# select * from pg_language ;
Try "select oid,lanname from pg_language".
regards, tom lane
Tom Lane wrote: >>=# select distinct prolang from pg_proc; >> prolang >>--------- >> 12 >> 13 >> 14 >> 17813 >> 63209 >> 63212 >> 63213 >> 63214 >>(8 rows) > > > That looks fine ... > > >>=# select * from pg_language ; > > > Try "select oid,lanname from pg_language". > > regards, tom lane Sorry, I see that I forgot to specify the oid. Thanks again