Thread: Unable to create or drop plpgsql
Hi everyone, This is my first post to this group, so please be kind. My database used to have support for plpgsql, that is I used the createlang command line tool to add the language to the database and then added functions, and they worked. Since then I have moved the database to a new machine and a new directory (two separate moves). Now I can't drop the language nor create it! Here is the output when I try to run the createlang and droplang commands. [jason@user-44 jason]$ createlang plpgsql esi ERROR: function plpgsql_call_handler already exists with same argument types createlang: language installation failed [jason@user-44 jason]$ [jason@user-44 jason]$ droplang plpgsql esi droplang: language "plpgsql" is not installed in database esi [jason@user-44 jason]$ [jason@user-44 jason]$ droplang --list esi Procedural languages Name | Trusted? ------+---------- (0 rows) Any ideas on how to get plpgsql back into my database? Thanks, Jason Underdown
"Jason Underdown" <jasonu@xmission.com> writes: > [jason@user-44 jason]$ createlang plpgsql esi > ERROR: function plpgsql_call_handler already exists with same argument > types > createlang: language installation failed > [jason@user-44 jason]$ > [jason@user-44 jason]$ droplang plpgsql esi > droplang: language "plpgsql" is not installed in database esi You seem to have gotten stuck in a halfway-done state: the pg_language table entry for plpgsql isn't there, but the pg_proc entry for its supporting language handler is there. What you'll have to do is manually drop the pg_proc entry. Try this SQL command as superuser: drop function plpgsql_call_handler(); After that createlang should work. If this is on a current release, I'd be interested to know how you got into this state. regards, tom lane
----- Original Message ----- From: "Jason Underdown" <jasonu@xmission.com> To: <pgsql-general@postgresql.org> Sent: Friday, June 20, 2003 11:00 PM Subject: [GENERAL] Unable to create or drop plpgsql > Hi everyone, > > This is my first post to this group, so please be kind. > > My database used to have support for plpgsql, that is I used the createlang > command line tool to add the language to the database and then added > functions, and they worked. Since then I have moved the database to a new > machine and a new directory (two separate moves). Now I can't drop the > language nor create it! Here is the output when I try to run the createlang > and droplang commands. > I newer tried to move database on other machine that way, so I do not know all the konsequences, but it looks to me that you created some kind of mess with PGDATA and PGLIB directories. PGLIB directory should contain library with plpgsql. I suggest you to connect to database with psql and try this : DROP PROCEDURAL LANGUAGE 'plpgsql' CASCADE; CREATE OR REPLACE FUNCTION plpgsql_call_handler() RETURNS LANGUAGE_HANDLER AS 'PGLIB/plpgsql.so', 'plpgsql_call_handler' LANGUAGE 'c'; CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER "plpgsql_call_handler" LANCOMPILER 'PL/pgSQL'; where PGLIB is path to postgres libraryes. If this fails, post back the error message, so we can see the exact reason why CREATE LANGUAGE fails. Regards !
Tom, Thank you for your instructions. They fixed my problem! In answer to your questions, I am running: temp=# select Version(); version ------------------------------------------------------------- PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC 2.96 As for how I got into such a state, I don't know. But I suspect it happened either when transferring the database via a text file to a new machine. (I think I used pg_dump with the -d, and -O options) Or, maybe it happened when I updated from 7.2 to 7.3 via RPMs (from the postgresql.org site). I upgraded after moving the database. Thanks, Jason > -----Original Message----- > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > Sent: Friday, June 20, 2003 8:44 PM > To: Jason Underdown > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Unable to create or drop plpgsql > > > "Jason Underdown" <jasonu@xmission.com> writes: > > [jason@user-44 jason]$ createlang plpgsql esi > > ERROR: function plpgsql_call_handler already exists with same argument > > types > > createlang: language installation failed > > [jason@user-44 jason]$ > > [jason@user-44 jason]$ droplang plpgsql esi > > droplang: language "plpgsql" is not installed in database esi > > You seem to have gotten stuck in a halfway-done state: the pg_language > table entry for plpgsql isn't there, but the pg_proc entry for its > supporting language handler is there. What you'll have to do is > manually drop the pg_proc entry. Try this SQL command as superuser: > drop function plpgsql_call_handler(); > After that createlang should work. > > If this is on a current release, I'd be interested to know how you got > into this state. > > regards, tom lane >