Thread: "dumpProcLangs(): handler procedure for language plpgsql not found" on 7.0.3

hi there,

I'm running postgres-7.0.3 on RH7.1 using RedHat RPMs.
I recently added plpgsql as a language to one of my databases,
and now when I try to do a pg_dump, I get:

"dumpProcLangs(): handler procedure for language plpgsql not found"

If I drop the language, pg_dump works fine, but if I add it back (and
even if I restart postgres), I get the same error.

That's on our production box.  Strangely enough, If I do the same thing
on my dev box (also RH 7.1 w/ 7.0.3), I don't get the error and the dump
works fine, picking up my triggers and all.

I've gone through and compared all rpm-installed files on the two boxes
and they're identical...

Any ideas on how to fix that error?

thanks,
brian




--
Brian T. Fujito
www.lightsource.com

Brian Fujito <brian@lightsource.com> writes:
> I'm running postgres-7.0.3 on RH7.1 using RedHat RPMs.
> I recently added plpgsql as a language to one of my databases,
> and now when I try to do a pg_dump, I get:

> "dumpProcLangs(): handler procedure for language plpgsql not found"

> If I drop the language, pg_dump works fine, but if I add it back (and
> even if I restart postgres), I get the same error.

What exactly are you doing to drop and re-add the language?  I should
think CREATE LANGUAGE would fail if the handler proc isn't there.

(Also, are you doing pg_dump or pg_dumpall?  If the latter, maybe the
failure is occurring in a different database than the one you are
changing.)

But having said that, 7.0.3 is ancient history ... you really are
overdue for an upgrade.  With my Red Hat fedora on, I'd say the same
about your choice of OS version too.

            regards, tom lane

Thanks for your input--

I've tried both ways:

createlang/droplang from the command line as user postgres

and:

CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS
        '/usr/lib/pgsql/plpgsql.so' LANGUAGE 'C';

CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
        HANDLER plpgsql_call_handler
          LANCOMPILER 'PL/pgSQL';

I'm using pg_dump (not pg_dumpall) on the specific database on which
I created the language.

I realize 7.0.3 is ancient (same with 7.1)... I just don't have the
time to deal with an upgrade quite yet.  Soon enough :)  In the mean
time, a stop-gap solution would definitely be appreciated.

Thank you,
Brian


On Mon, 2002-12-09 at 14:28, Tom Lane wrote:
> Brian Fujito <brian@lightsource.com> writes:
> > I'm running postgres-7.0.3 on RH7.1 using RedHat RPMs.
> > I recently added plpgsql as a language to one of my databases,
> > and now when I try to do a pg_dump, I get:
>
> > "dumpProcLangs(): handler procedure for language plpgsql not found"
>
> > If I drop the language, pg_dump works fine, but if I add it back (and
> > even if I restart postgres), I get the same error.
>
> What exactly are you doing to drop and re-add the language?  I should
> think CREATE LANGUAGE would fail if the handler proc isn't there.
>
> (Also, are you doing pg_dump or pg_dumpall?  If the latter, maybe the
> failure is occurring in a different database than the one you are
> changing.)
>
> But having said that, 7.0.3 is ancient history ... you really are
> overdue for an upgrade.  With my Red Hat fedora on, I'd say the same
> about your choice of OS version too.
>
>             regards, tom lane

Brian Fujito <brian@lightsource.com> writes:
>> What exactly are you doing to drop and re-add the language?  I should
>> think CREATE LANGUAGE would fail if the handler proc isn't there.

> I've tried both ways:

> createlang/droplang from the command line as user postgres

> and:

> CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS
>         '/usr/lib/pgsql/plpgsql.so' LANGUAGE 'C';

> CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
>         HANDLER plpgsql_call_handler
>           LANCOMPILER 'PL/pgSQL';

Hrmph.  Looks perfectly standard from here; I don't see why pg_dump is
failing to find the handler.  It would help to see what the server-side
view of the transaction is like.  Would you run pg_dump after setting
query logging on (from memory, I think export PGOPTIONS="-d2" will work
in 7.0, but too tired to check it) and then show us the tail end of the
postmaster log after pg_dump fails?

            regards, tom lane

PS: a wild-*ss guess: 7.0 wasn't too clean in handling OIDs above 2
billion; is it possible your pg_language OID for plpgsql is over 2G?

On 10 Dec 2002 at 0:02, Tom Lane wrote:

> Brian Fujito <brian@lightsource.com> writes:
> >> What exactly are you doing to drop and re-add the language?  I
> >> should think CREATE LANGUAGE would fail if the handler proc isn't
> >> there.
>
> > I've tried both ways:
>
> > createlang/droplang from the command line as user postgres
>
> > and:
>
> > CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS
> >         '/usr/lib/pgsql/plpgsql.so' LANGUAGE 'C';
>
> > CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
> >         HANDLER plpgsql_call_handler
> >           LANCOMPILER 'PL/pgSQL';
>
> Hrmph.  Looks perfectly standard from here; I don't see why pg_dump is
> failing to find the handler.  It would help to see what the
> server-side view of the transaction is like.  Would you run pg_dump
> after setting query logging on (from memory, I think export
> PGOPTIONS="-d2" will work in 7.0, but too tired to check it) and then
> show us the tail end of the postmaster log after pg_dump fails?
>
>    regards, tom lane
>
> PS: a wild-*ss guess: 7.0 wasn't too clean in handling OIDs above 2
> billion; is it possible your pg_language OID for plpgsql is over 2G?

Followed by another wild guess.  Could the path be the problem?
Looking at my notes (http://www.freebsddiary.org/postgresql-
pgsql.php) I see that at one time I supplied a pathname :

CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS
'/usr/local/pgsql/lib/plpgsql.so' LANGUAGE 'C';

Please let us know.
--
Dan Langille : http://www.langille.org/

Thanks again for your input.  I think you're right about the OID's.

Watching the query logs, the pg_dump failed just after:
021210.00:58:25.578 [25527] query: SELECT * FROM pg_language WHERE
lanispl ORDER BY oid



MYDB=> select * from pg_language where lanispl order by oid;
 lanname | lanispl | lanpltrusted | lanplcallfoid | lancompiler
---------+---------+--------------+---------------+-------------
 plpgsql | t       | t            |   -1983262688 | PL/pgSQL
(1 row)

MYDB=> select oid from pg_proc where proname = 'plpgsql_call_handler';
     oid
-------------
 -1983262688
(1 row)

lifeaudioV2=> select * from pg_language where lanplcallfoid =
-1983262688;
 lanname | lanispl | lanpltrusted | lanplcallfoid | lancompiler
---------+---------+--------------+---------------+-------------
(0 rows)

MYDB=>


So yes, it looks like the OID's got messed up.  That negative number
looks suspiciously like an overflow on a max int.  Is there *any* way to
fix this, or should I just deal with it and upgrade?

BTW, does this mean that other data in the system could be corrupted?

If an upgrade is the best way out, are there any particular versions
that would a) fix the problem and b) allow for a clean transition?

I'm using Perl's DBI::Pg, PHP's pgsql extensions, and JDBC for
postgres... Just a bit weary of making transitions :-)  I understand
I'll have to perform a pg_dump for the transition to work, which I can
do by turning off the triggers, for now.


Thank you, Tom, for all your help.
Brian


On Tue, 2002-12-10 at 00:02, Tom Lane wrote:
> Brian Fujito <brian@lightsource.com> writes:
> >> What exactly are you doing to drop and re-add the language?  I should
> >> think CREATE LANGUAGE would fail if the handler proc isn't there.
>
> > I've tried both ways:
>
> > createlang/droplang from the command line as user postgres
>
> > and:
>
> > CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS
> >         '/usr/lib/pgsql/plpgsql.so' LANGUAGE 'C';
>
> > CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
> >         HANDLER plpgsql_call_handler
> >           LANCOMPILER 'PL/pgSQL';
>
> Hrmph.  Looks perfectly standard from here; I don't see why pg_dump is
> failing to find the handler.  It would help to see what the server-side
> view of the transaction is like.  Would you run pg_dump after setting
> query logging on (from memory, I think export PGOPTIONS="-d2" will work
> in 7.0, but too tired to check it) and then show us the tail end of the
> postmaster log after pg_dump fails?
>
>             regards, tom lane
>
> PS: a wild-*ss guess: 7.0 wasn't too clean in handling OIDs above 2
> billion; is it possible your pg_language OID for plpgsql is over 2G?
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
--
Brian T. Fujito
Chief Technical Officer
www.christianity.com
www.lightsource.com
www.online-giving.com
brian@lightsource.com       (703) 548-8900

Nope- checked the filename.  The libs match up.  Thanks for your help,
though--I appreciate it.  Please see my reply to Tom regarding OID's...
any insight on how to fix that?

thank you all,
brian


On Tue, 2002-12-10 at 00:26, Dan Langille wrote:
> On 10 Dec 2002 at 0:02, Tom Lane wrote:
>
> > Brian Fujito <brian@lightsource.com> writes:
> > >> What exactly are you doing to drop and re-add the language?  I
> > >> should think CREATE LANGUAGE would fail if the handler proc isn't
> > >> there.
> >
> > > I've tried both ways:
> >
> > > createlang/droplang from the command line as user postgres
> >
> > > and:
> >
> > > CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS
> > >         '/usr/lib/pgsql/plpgsql.so' LANGUAGE 'C';
> >
> > > CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
> > >         HANDLER plpgsql_call_handler
> > >           LANCOMPILER 'PL/pgSQL';
> >
> > Hrmph.  Looks perfectly standard from here; I don't see why pg_dump is
> > failing to find the handler.  It would help to see what the
> > server-side view of the transaction is like.  Would you run pg_dump
> > after setting query logging on (from memory, I think export
> > PGOPTIONS="-d2" will work in 7.0, but too tired to check it) and then
> > show us the tail end of the postmaster log after pg_dump fails?
> >
> >    regards, tom lane
> >
> > PS: a wild-*ss guess: 7.0 wasn't too clean in handling OIDs above 2
> > billion; is it possible your pg_language OID for plpgsql is over 2G?
>
> Followed by another wild guess.  Could the path be the problem?
> Looking at my notes (http://www.freebsddiary.org/postgresql-
> pgsql.php) I see that at one time I supplied a pathname :
>
> CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS
> '/usr/local/pgsql/lib/plpgsql.so' LANGUAGE 'C';
>
> Please let us know.
> --
> Dan Langille : http://www.langille.org/
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
--
Brian T. Fujito
Chief Technical Officer
www.christianity.com
www.lightsource.com
www.online-giving.com
brian@lightsource.com       (703) 548-8900

I have had similar troubles, related to oid overflow. I had to modify pg_dump
to properly cast queries that contain oids. This is against 7.1.3 source. The
patch was hacked quickly, in order to get a corrupted database reloaded, and
this while I was traveling in another country... so it is far from perfect but
saved my database(s). It also fixes other oid-related troubles of pg_dump.

See attached file.

Daniel

>>>Brian Fujito said:
 > Thanks for your input--
 >
 > I've tried both ways:
 >
 > createlang/droplang from the command line as user postgres
 >
 > and:
 >
 > CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS
 >         '/usr/lib/pgsql/plpgsql.so' LANGUAGE 'C';
 >
 > CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
 >         HANDLER plpgsql_call_handler
 >           LANCOMPILER 'PL/pgSQL';
 >
 > I'm using pg_dump (not pg_dumpall) on the specific database on which
 > I created the language.
 >
 > I realize 7.0.3 is ancient (same with 7.1)... I just don't have the
 > time to deal with an upgrade quite yet.  Soon enough :)  In the mean
 > time, a stop-gap solution would definitely be appreciated.
 >
 > Thank you,
 > Brian
 >
 >
 > On Mon, 2002-12-09 at 14:28, Tom Lane wrote:
 > > Brian Fujito <brian@lightsource.com> writes:
 > > > I'm running postgres-7.0.3 on RH7.1 using RedHat RPMs.
 > > > I recently added plpgsql as a language to one of my databases,
 > > > and now when I try to do a pg_dump, I get:
 > >
 > > > "dumpProcLangs(): handler procedure for language plpgsql not found"
 > >
 > > > If I drop the language, pg_dump works fine, but if I add it back (and
 > > > even if I restart postgres), I get the same error.
 > >
 > > What exactly are you doing to drop and re-add the language?  I should
 > > think CREATE LANGUAGE would fail if the handler proc isn't there.
 > >
 > > (Also, are you doing pg_dump or pg_dumpall?  If the latter, maybe the
 > > failure is occurring in a different database than the one you are
 > > changing.)
 > >
 > > But having said that, 7.0.3 is ancient history ... you really are
 > > overdue for an upgrade.  With my Red Hat fedora on, I'd say the same
 > > about your choice of OS version too.
 > >
 > >             regards, tom lane
 >
 >
 >
 > ---------------------------(end of broadcast)---------------------------
 > TIP 3: if posting/reading through Usenet, please send an appropriate
 > subscribe-nomail command to majordomo@postgresql.org so that your
 > message can get through to the mailing list cleanly

Brian,

No data in the system is corrupted, just most of the dump and output routines
in order PostgreSQL versions assume that oid == int (signed int), which is not
true since few releases... or maybe never was true.

Note that if you reference anything by oid, you should change your queries from

SELECT whatever FROM wherever WHERE oid = value;

to

SELECT whatever FROM wherever WHERE oid = oid(value);

This has bitten me many times already :)

I guess all your dump troubles are related to oid overflow.

Daniel

>>>Brian Fujito said:
 > Thanks again for your input.  I think you're right about the OID's.
 >
 > Watching the query logs, the pg_dump failed just after:
 > 021210.00:58:25.578 [25527] query: SELECT * FROM pg_language WHERE
 > lanispl ORDER BY oid
 >
 >
 >
 > MYDB=> select * from pg_language where lanispl order by oid;
 >  lanname | lanispl | lanpltrusted | lanplcallfoid | lancompiler
 > ---------+---------+--------------+---------------+-------------
 >  plpgsql | t       | t            |   -1983262688 | PL/pgSQL
 > (1 row)
 >
 > MYDB=> select oid from pg_proc where proname = 'plpgsql_call_handler';
 >      oid
 > -------------
 >  -1983262688
 > (1 row)
 >
 > lifeaudioV2=> select * from pg_language where lanplcallfoid =
 > -1983262688;
 >  lanname | lanispl | lanpltrusted | lanplcallfoid | lancompiler
 > ---------+---------+--------------+---------------+-------------
 > (0 rows)
 >
 > MYDB=>
 >
 >
 > So yes, it looks like the OID's got messed up.  That negative number
 > looks suspiciously like an overflow on a max int.  Is there *any* way to
 > fix this, or should I just deal with it and upgrade?
 >
 > BTW, does this mean that other data in the system could be corrupted?
 >
 > If an upgrade is the best way out, are there any particular versions
 > that would a) fix the problem and b) allow for a clean transition?
 >
 > I'm using Perl's DBI::Pg, PHP's pgsql extensions, and JDBC for
 > postgres... Just a bit weary of making transitions :-)  I understand
 > I'll have to perform a pg_dump for the transition to work, which I can
 > do by turning off the triggers, for now.
 >
 >
 > Thank you, Tom, for all your help.
 > Brian
 >
 >
 > On Tue, 2002-12-10 at 00:02, Tom Lane wrote:
 > > Brian Fujito <brian@lightsource.com> writes:
 > > >> What exactly are you doing to drop and re-add the language?  I should
 > > >> think CREATE LANGUAGE would fail if the handler proc isn't there.
 > >
 > > > I've tried both ways:
 > >
 > > > createlang/droplang from the command line as user postgres
 > >
 > > > and:
 > >
 > > > CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS
 > > >         '/usr/lib/pgsql/plpgsql.so' LANGUAGE 'C';
 > >
 > > > CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
 > > >         HANDLER plpgsql_call_handler
 > > >           LANCOMPILER 'PL/pgSQL';
 > >
 > > Hrmph.  Looks perfectly standard from here; I don't see why pg_dump is
 > > failing to find the handler.  It would help to see what the server-side
 > > view of the transaction is like.  Would you run pg_dump after setting
 > > query logging on (from memory, I think export PGOPTIONS="-d2" will work
 > > in 7.0, but too tired to check it) and then show us the tail end of the
 > > postmaster log after pg_dump fails?
 > >
 > >             regards, tom lane
 > >
 > > PS: a wild-*ss guess: 7.0 wasn't too clean in handling OIDs above 2
 > > billion; is it possible your pg_language OID for plpgsql is over 2G?
 > >
 > > ---------------------------(end of broadcast)---------------------------
 > > TIP 2: you can get off all lists at once with the unregister command
 > >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
 > --
 > Brian T. Fujito
 > Chief Technical Officer
 > www.christianity.com
 > www.lightsource.com
 > www.online-giving.com
 > brian@lightsource.com       (703) 548-8900
 >
 >
 >
 > ---------------------------(end of broadcast)---------------------------
 > TIP 4: Don't 'kill -9' the postmaster