Help! Can't pg_dump anything: handler procedure for procedural language plpgsql not found

[Mark Mikulec]

Hi there,

I wonder if anyone can shed some light on a very frustrating problem.

I'm running a debian linux 3.0 "woody" server, nothing special, with
the latest version of postres that apt-get will allow me, which I
*think* it;s 7.1 something, I don't know how to figure out the
postgres version. Anywho - I'm trying to backup my databases, which I
did at one point, but I have no idea what happened, could have been an
upgrade. My Dbs have large objects in them, so I would do the
following as per the man pages:

pg_dump -S postgres -Ft -b mydb > mydb.tar

But i'd always get the following error message:

pg_dump: handler procedure for procedural language plpgsql not found

And even with the most simple of pg_dumps, I would still get that
error, with a 0 sized file to show for it.

Does anyone have any ideas on how to fix this?

Thanks,
Mark

[Tom Lane]

mm****@brocku.ca (Mark Mikulec) writes:

But i'd always get the following error message:
pg_dump: handler procedure for procedural language plpgsql not found

Do you have any functions written in plpgsql? (I'm guessing not, or you
would have other problems besides pg_dump not working.) If not, you can
just delete the pg_language entry for plpgsql:

DELETE FROM pg_language WHERE lanname = 'plpgsql';

If you do have any such functions, you should drop them first.
(Alternatively, let pg_dump find them for you, and drop them in response
to its complaints.)

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Mark Mikulec]

Unfortunately,

That didn't work.

Thanks anyway.
----- Original Message -----
From: "Tom Lane" <tg*@sss.pgh.pa.us>
To: "Mark Mikulec" <mm****@badger.ac.brocku.ca>
Cc: <pg***********@postgresql.org>
Sent: Tuesday, August 12, 2003 9:54 AM
Subject: Re: [GENERAL] Help! Can't pg_dump anything: handler procedure for
procedural language plpgsql not found

mm****@brocku.ca (Mark Mikulec) writes:

But i'd always get the following error message:
pg_dump: handler procedure for procedural language plpgsql not found

Do you have any functions written in plpgsql? (I'm guessing not, or you
would have other problems besides pg_dump not working.) If not, you can
just delete the pg_language entry for plpgsql:

DELETE FROM pg_language WHERE lanname = 'plpgsql';

If you do have any such functions, you should drop them first.
(Alternatively, let pg_dump find them for you, and drop them in response
to its complaints.)

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Mark Mikulec]

Ok,

I'm really getting the impression that something is fishy about my version
of Postgres.

So I created the language using createlang, (BTW - I'd create it with a user
that has the ability to create db's and users, not the super Postgres -
should I always do it with the superuser postgres?) - it ended up giving an
error of: ERROR: Zero-Length delimited identifier.

Tried pg_dump - still didn't work. So I took tom lane's suggestion, only I
used the droplang command and after that, it succeeded! Ok, so try to
restore, I dropped my test database, created it again, then called
pg_restore on my tar, and I got:

pg_restore: [archiver (db)] could not execute query: ERROR: function
plpgsql_call_handler already exists with same argument types

Which doesn't makes to sense to me, because I dropped the language! I'm
connecting as a superuser I created, into a newly created database. How
could this be?

Thanks in advance,
Mark

----- Original Message -----
From: "Richard Huxton" <de*@archonet.com>
To: "Mark Mikulec" <mm****@badger.ac.brocku.ca>;
<pg***********@postgresql.org>
Sent: Tuesday, August 12, 2003 4:35 AM
Subject: Re: [GENERAL] Help! Can't pg_dump anything: handler procedure for
procedural language plpgsql not found

On Tuesday 12 August 2003 01:41, Mark Mikulec wrote:

Hi there,

I wonder if anyone can shed some light on a very frustrating problem.

I'm running a debian linux 3.0 "woody" server, nothing special, with
the latest version of postres that apt-get will allow me, which I
*think* it;s 7.1 something, I don't know how to figure out the
postgres version.
psql --version

Anywho - I'm trying to backup my databases, which I
did at one point, but I have no idea what happened, could have been an
upgrade. My Dbs have large objects in them, so I would do the
following as per the man pages:

pg_dump -S postgres -Ft -b mydb > mydb.tar

But i'd always get the following error message:

pg_dump: handler procedure for procedural language plpgsql not found

You presumably have a plpgsql function defined but have lost the

definitions to handle this (I can't think how). Try a "createlang plpgsql mydbname" (man createlang for details).

If you use apt-get to update PG's code, I'd recommend dumping databases before the upgrade, just in case.
--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Tom Lane]

"Mark Mikulec" <mm****@brocku.ca> writes:

Tried pg_dump - still didn't work. So I took tom lane's suggestion, only I
used the droplang command and after that, it succeeded! Ok, so try to
restore, I dropped my test database, created it again, then called
pg_restore on my tar, and I got: pg_restore: [archiver (db)] could not execute query: ERROR: function
plpgsql_call_handler already exists with same argument types Which doesn't makes to sense to me, because I dropped the language!

Sounds to me like you have a definition (possibly a broken one) in
the template1 database, where it will be copied by CREATE DATABASE.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings