Обсуждение: 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
>