BUG #1455: pg_dumpall fails

The following bug has been logged online:

Bug reference: 1455
Logged by: Boris Makovecki
Email address: boris(dot)makovecki(at)abak(dot)net
PostgreSQL version: 8.0.1
Operating system: Windows 2K3 web server
Description: pg_dumpall fails
Details:

I'm trying to migrate postgre DB from 7.4 to 8.0. When I start pg_dumpall
(8.0) and connect it to 7.4 it fails with error:

pg_dump: SQL command failed
pg_dump: Error message from server: ERROR: cannot cast type "unknown" to
text
pg_dump: The command was: SELECT proretset, prosrc, probin, null::text as
proargnames, provolatile, proisstrict, prosecdef, (SELECT lanname FROM
pg_catalog.pg_language WHERE oid = prolang) as lanname FROM
pg_catalog.pg_proc WHERE oid = '17926077'::pg_catalog.oid

oid 17926077 stand for recursive function:

CREATE OR REPLACE FUNCTION meta.entity_inherit_from(int4)
RETURNS varchar AS
'DECLARE
id_e ALIAS FOR $1;
rez varchar;
a int4;

BEGIN
SELECT INTO a lng_inherit_from_fkeyi_ref FROM meta.entity WHERE
lng_entity_pkeyi=id_e;
IF a IS NULL THEN
rez= \'\' || id_e;
ELSE
rez=meta.entity_inherit_from(a) || \',\' || id_e;
END IF;
RETURN rez;
END;
'
LANGUAGE 'plpgsql' VOLATILE;

please advice,
regards,
Boris Makovecki
ABAK.NET

"Boris Makovecki" <boris(dot)makovecki(at)abak(dot)net> writes:
> I'm trying to migrate postgre DB from 7.4 to 8.0. When I start pg_dumpall
> (8.0) and connect it to 7.4 it fails with error:

> pg_dump: SQL command failed
> pg_dump: Error message from server: ERROR: cannot cast type "unknown" to
> text
> pg_dump: The command was: SELECT proretset, prosrc, probin, null::text as
> proargnames, provolatile, proisstrict, prosecdef, (SELECT lanname FROM
> pg_catalog.pg_language WHERE oid = prolang) as lanname FROM
> pg_catalog.pg_proc WHERE oid = '17926077'::pg_catalog.oid

Works fine for me, using a 7.4.7 server. Possibly you are hitting some
bug in an earlier 7.4.* release? (I don't see anything promising
looking in the CVS logs, though.) What *exactly* is the server version?

regards, tom lane

Currently I'm using 7.4.0 version. I'll first upgarade to 7.4.7 and then try
to upgarade to 8.0.1,

thanks,
Boris Makovecki
-----Original Message-----
From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Boris Makovecki" <boris(dot)makovecki(at)abak(dot)net>
Cc: pgsql-bugs(at)postgresql(dot)org
Date: Thu, 03 Feb 2005 01:02:01 -0500
Subject: Re: [BUGS] BUG #1455: pg_dumpall fails

> "Boris Makovecki" <boris(dot)makovecki(at)abak(dot)net> writes:
> > I'm trying to migrate postgre DB from 7.4 to 8.0. When I start
> pg_dumpall
> > (8.0) and connect it to 7.4 it fails with error:
>
> > pg_dump: SQL command failed
> > pg_dump: Error message from server: ERROR: cannot cast type
> "unknown" to
> > text
> > pg_dump: The command was: SELECT proretset, prosrc, probin,
> null::text as
> > proargnames, provolatile, proisstrict, prosecdef, (SELECT lanname
> FROM
> > pg_catalog.pg_language WHERE oid = prolang) as lanname FROM
> > pg_catalog.pg_proc WHERE oid = '17926077'::pg_catalog.oid
>
> Works fine for me, using a 7.4.7 server. Possibly you are hitting some
> bug in an earlier 7.4.* release? (I don't see anything promising
> looking in the CVS logs, though.) What *exactly* is the server
> version?
>
> regards, tom lane
>
> ---------------------------(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)

"Boris Makovecki" <boris(dot)makovecki(at)abak(dot)net> writes:
> I'm trying to migrate postgre DB from 7.4 to 8.0. When I start pg_dumpall
> (8.0) and connect it to 7.4 it fails with error:

> pg_dump: SQL command failed
> pg_dump: Error message from server: ERROR: cannot cast type "unknown" to
> text

I believe we have finally worked out an explanation for this failure:
you had a table named "text" and some functions in the same schema.
Due to some sloppy coding (probably mine :-() in 8.0 pg_dump, this
confused things. Here is the patch if you still need it.

regards, tom lane

Index: pg_dump.c
===================================================================
RCS file: /cvsroot/pgsql/src/bin/pg_dump/pg_dump.c,v
retrieving revision 1.400.4.4
diff -c -r1.400.4.4 pg_dump.c
*** pg_dump.c 30 Apr 2005 08:19:44 -0000 1.400.4.4
--- pg_dump.c 7 Jun 2005 14:01:42 -0000
***************
*** 5115,5121 ****
{
appendPQExpBuffer(query,
"SELECT proretset, prosrc, probin, "
! "null::text as proargnames, "
"provolatile, proisstrict, prosecdef, "
"(SELECT lanname FROM pg_catalog.pg_language WHERE oid = prolang) as lanname "
"FROM pg_catalog.pg_proc "
--- 5115,5121 ----
{
appendPQExpBuffer(query,
"SELECT proretset, prosrc, probin, "
! "null as proargnames, "
"provolatile, proisstrict, prosecdef, "
"(SELECT lanname FROM pg_catalog.pg_language WHERE oid = prolang) as lanname "
"FROM pg_catalog.pg_proc "
***************
*** 5126,5132 ****
{
appendPQExpBuffer(query,
"SELECT proretset, prosrc, probin, "
! "null::text as proargnames, "
"case when proiscachable then 'i' else 'v' end as provolatile, "
"proisstrict, "
"'f'::boolean as prosecdef, "
--- 5126,5132 ----
{
appendPQExpBuffer(query,
"SELECT proretset, prosrc, probin, "
! "null as proargnames, "
"case when proiscachable then 'i' else 'v' end as provolatile, "
"proisstrict, "
"'f'::boolean as prosecdef, "
***************
*** 5139,5145 ****
{
appendPQExpBuffer(query,
"SELECT proretset, prosrc, probin, "
! "null::text as proargnames, "
"case when proiscachable then 'i' else 'v' end as provolatile, "
"'f'::boolean as proisstrict, "
"'f'::boolean as prosecdef, "
--- 5139,5145 ----
{
appendPQExpBuffer(query,
"SELECT proretset, prosrc, probin, "
! "null as proargnames, "
"case when proiscachable then 'i' else 'v' end as provolatile, "
"'f'::boolean as proisstrict, "
"'f'::boolean as prosecdef, "