BUG #1455: pg_dumpall fails
The following bug has been logged online:
Bug reference: 1455
Logged by: Boris Makovecki
Email address: boris.makovecki@abak.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.makovecki@abak.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@sss.pgh.pa.us>
To: "Boris Makovecki" <boris.makovecki@abak.net>
Cc: pgsql-bugs@postgresql.org
Date: Thu, 03 Feb 2005 01:02:01 -0500
Subject: Re: [BUGS] BUG #1455: pg_dumpall fails
Show quoted text
"Boris Makovecki" <boris.makovecki@abak.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.oidWorks 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@postgresql.org)
"Boris Makovecki" <boris.makovecki@abak.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, "