Re: "dumpProcLangs(): handler procedure for language - Mailing list pgsql-admin

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