The following bug has been logged online:

Bug reference: 1455
Logged by: Boris Makovecki
Email address: boris.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':g_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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

"Boris Makovecki" <boris.makovecki (AT) abak (DOT) net> writes:
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)

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.pa.us>
To: "Boris Makovecki" <boris.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



---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

"Boris Makovecki" <boris.makovecki (AT) abak (DOT) net> writes:
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, "

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org