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 <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] so that your
> message can get through to the mailing list cleanly
*** pg_dump.c.orig Mon Apr 15 09:45:58 2002
--- pg_dump.c Tue Jun 25 00:23:53 2002
***************
*** 2006,2012 ****
finfo[i].prorettype = strdup(PQgetvalue(res, i, i_prorettype));
finfo[i].retset = (strcmp(PQgetvalue(res, i, i_proretset), "t") == 0);
finfo[i].nargs = atoi(PQgetvalue(res, i, i_pronargs));
! finfo[i].lang = atoi(PQgetvalue(res, i, i_prolang));
finfo[i].usename = strdup(PQgetvalue(res, i, i_usename));
finfo[i].iscachable = (strcmp(PQgetvalue(res, i, i_iscachable), "t")
== 0);
finfo[i].isstrict = (strcmp(PQgetvalue(res, i, i_isstrict), "t") == 0);
--- 2006,2012 ----
finfo[i].prorettype = strdup(PQgetvalue(res, i, i_prorettype));
finfo[i].retset = (strcmp(PQgetvalue(res, i, i_proretset), "t") == 0);
finfo[i].nargs = atoi(PQgetvalue(res, i, i_pronargs));
! finfo[i].lang = atooid(PQgetvalue(res, i, i_prolang));
finfo[i].usename = strdup(PQgetvalue(res, i, i_usename));
finfo[i].iscachable = (strcmp(PQgetvalue(res, i, i_iscachable), "t")
== 0);
finfo[i].isstrict = (strcmp(PQgetvalue(res, i, i_isstrict), "t") == 0);
***************
*** 2289,2295 ****
resetPQExpBuffer(query);
appendPQExpBuffer(query,
! "SELECT Oid FROM pg_index i
WHERE i.indisprimary AND i.indrelid = %s ",
tblinfo[i].oid);
res2 = PQexec(g_conn, query->data);
if (!res2 || PQresultStatus(res2) != PGRES_TUPLES_OK)
--- 2289,2295 ----
resetPQExpBuffer(query);
appendPQExpBuffer(query,
! "SELECT Oid FROM pg_index i
WHERE i.indisprimary AND i.indrelid = oid(%s) ",
tblinfo[i].oid);
res2 = PQexec(g_conn, query->data);
if (!res2 || PQresultStatus(res2) != PGRES_TUPLES_OK)
***************
*** 2328,2340 ****
appendPQExpBuffer(query,
" SELECT c.relname "
" FROM pg_index i,
pg_class c "
! " WHERE i.indrelid =
%s"
" AND
i.indisprimary "
" AND c.oid =
i.indexrelid"
" UNION ALL "
" SELECT NULL "
" FROM pg_index i "
! " WHERE i.indrelid = %s"
" AND i.indisprimary "
" And NOT Exists(Select *
From pg_class c Where c.oid = i.indexrelid)",
tblinfo[i].oid,
tblinfo[i].oid);
--- 2328,2340 ----
appendPQExpBuffer(query,
" SELECT c.relname "
" FROM pg_index i,
pg_class c "
! " WHERE i.indrelid =
oid(%s)"
" AND
i.indisprimary "
" AND c.oid =
i.indexrelid"
" UNION ALL "
" SELECT NULL "
" FROM pg_index i "
! " WHERE i.indrelid =
oid(%s)"
" AND i.indisprimary "
" And NOT Exists(Select *
From pg_class c Where c.oid = i.indexrelid)",
tblinfo[i].oid,
tblinfo[i].oid);
***************
*** 2343,2349 ****
appendPQExpBuffer(query,
"SELECT c.relname "
"FROM pg_index i LEFT OUTER
JOIN pg_class c ON c.oid = i.indexrelid "
! "WHERE i.indrelid = %s"
"AND i.indisprimary ",
tblinfo[i].oid);
}
--- 2343,2349 ----
appendPQExpBuffer(query,
"SELECT c.relname "
"FROM pg_index i LEFT OUTER
JOIN pg_class c ON c.oid = i.indexrelid "
! "WHERE i.indrelid = oid(%s)"
"AND i.indisprimary ",
tblinfo[i].oid);
}
***************
*** 3035,3040 ****
--- 3035,3041 ----
query = createPQExpBuffer();
appendPQExpBuffer(query, "SELECT description FROM pg_description WHERE objoid
= ");
appendPQExpBuffer(query, oid);
+ appendPQExpBuffer(query, "::oid");
/*** Execute query ***/
***************
*** 3394,3400 ****
/* becomeUser(fout, finfo[i].usename); */
! sprintf(query, "SELECT lanname FROM pg_language WHERE oid = %u",
finfo[i].lang);
res = PQexec(g_conn, query);
if (!res ||
--- 3395,3401 ----
/* becomeUser(fout, finfo[i].usename); */
! sprintf(query, "SELECT lanname FROM pg_language WHERE oid = oid(%u)",
finfo[i].lang);
res = PQexec(g_conn, query);
if (!res ||
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
