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 | 2248Apparently 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.
Import Notes
Reply to msg id not found: 4247174A.3020306@shaw.caReference msg id not found: 4247174A.3020306@shaw.ca
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