Re: "dumpProcLangs(): handler procedure for language

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(at)lightsource(dot)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(at)postgresql(dot)org)
> --
> Brian T. Fujito
> Chief Technical Officer
> www.christianity.com
> www.lightsource.com
> www.online-giving.com
> brian(at)lightsource(dot)com (703) 548-8900
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster