Thread: pg_dumpall -> fails
so i've got my data recovered (thanks to oliver) and now
i wanna back it up with a pg_dumpall...
instead, i get 'failed sanity check, type with oid 779927 was no
found' in the oddest places...
% pg_dumpall
[snip!!]
CREATE FUNCTION "get_disid" (int4,varchar ) RETURNS int4 AS '
SELECT
id
FROM
_dis
WHERE
code = $2
AND
edu = $1
;
' LANGUAGE 'SQL';
CREATE FUNCTION "get_failed sanity check, type with oid 779927 was not found
disid" (text,text ) RETURNS int4 AS '
SELECT
get_disid( get_eduid($1), $2 )
;
' LANGUAGE 'SQL';
CREATE FUNCTION "get_courseid" (int4,varchar,varchar,bpchar ) RETURNS int4 AS '
SELECT
id
FROM
_course
WHERE
dis = $1 -- discipline ID
AND
code = $2 -- course abbrev
AND
language = $3 -- language
AND
medium = $4 -- media
;
' LANGUAGE 'SQL';
[snip]
CREATE FUNCTION "get_studentid" (varchar ) RETURNS int4 AS '
SELECT
who
FROM
_student
WHERE
_student.who = _who.id
AND
_who.login = $1;
' LANGUAGE 'SQL';
\connect - will
pg_dump failed on ed, exiting
so what does 'sanity check' mean, and why does it appear
in the middle of 'get_disid'?
--
will@serensoft.com
From: "will trillich" <will@serensoft.com> > so i've got my data recovered (thanks to oliver) and now > i wanna back it up with a pg_dumpall... > > instead, i get 'failed sanity check, type with oid 779927 was no > found' in the oddest places... > > > % pg_dumpall > > [snip!!] > > CREATE FUNCTION "get_disid" (int4,varchar ) RETURNS int4 AS ' > SELECT > id > FROM > _dis > WHERE > code = $2 > AND > edu = $1 > ; > ' LANGUAGE 'SQL'; > CREATE FUNCTION "get_failed sanity check, type with oid 779927 was not found > disid" (text,text ) RETURNS int4 AS ' > SELECT > get_disid( get_eduid($1), $2 ) > ; > ' LANGUAGE 'SQL'; > > so what does 'sanity check' mean, and why does it appear > in the middle of 'get_disid'? You haven't got a rogue CR somewhere in that function definition have you? (Edited on Windows, pasted on *nix?) Try "select oid,typname from pg_type where oid=779927" - My hunch is there's some problem with your table _dis. - Richard Huxton
will trillich wrote: >so i've got my data recovered (thanks to oliver) and now >i wanna back it up with a pg_dumpall... > >instead, i get 'failed sanity check, type with oid 779927 was no >found' in the oddest places... More than 1 place? >CREATE FUNCTION "get_failed sanity check, type with oid 779927 was not found >disid" (text,text ) RETURNS int4 AS ' > SELECT > get_disid( get_eduid($1), $2 ) > ; >' LANGUAGE 'SQL'; >[snip] > >CREATE FUNCTION "get_studentid" (varchar ) RETURNS int4 AS ' > SELECT > who > FROM > _student > WHERE > _student.who = _who.id > AND > _who.login = $1; >' LANGUAGE 'SQL'; >\connect - will >pg_dump failed on ed, exiting > > >so what does 'sanity check' mean, and why does it appear >in the middle of 'get_disid'? pg_dump is dumping your functions and for each function it looks up the argument type by the oid of the type. For one function there is a type whose oid is 779927, but there is no row in pg_type with that oid. I don't think that get_disid(text,text) is necessarily the culprit, because the routine aborts pg_dump as soon as it hits that error (src/bin/pg_dump/common.c:findTypeByOid()), whereas you seem to be getting a lot of text after the error. I suspect that stderr is getting sent and then the stdout buffer is flushed as the program exits. Look in pg_proc for a function that tries to use a type of 779927: select proname, proargtypes, prorettype from pg_proc; That will be the one that is causing the problem. (Of course the next question is how it got that way.) -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "These things have I written unto you that believe on the name of the Son of God; that ye may know that ye have eternal life, and that ye may believe on the name of the Son of God." I John 5:13
On Wed, Feb 28, 2001 at 12:52:10PM +0000, Oliver Elphick wrote:
> will trillich wrote:
> >so i've got my data recovered (thanks to oliver) and now
> >i wanna back it up with a pg_dumpall...
> >
> >instead, i get 'failed sanity check, type with oid 779927 was no
> >found' in the oddest places...
>
> More than 1 place?
well, it seems to move around. once per 'pg_dumpall' attempt,
but not always in the same spot...
> >CREATE FUNCTION "get_failed sanity check, type with oid 779927 was not found
> >disid" (text,text ) RETURNS int4 AS '
> > SELECT
> > get_disid( get_eduid($1), $2 )
> > ;
> >' LANGUAGE 'SQL';
> >[snip]
>
> pg_dump is dumping your functions and for each function it looks up
> the argument type by the oid of the type. For one function there
> is a type whose oid is 779927, but there is no row in pg_type with
> that oid.
turns out you're right again. :)
i'm doing the "build a structure while ramping up the learning
curve" thing, so i create and drop stuff regularly while i get
this humming the way i want it to...
-- this kind of thing
drop table XYZ;
create table XYZ ( ... );
drop function munge( XYZ );
create function munge( XYZ ) returns ... as ... ;
now i've moved my 'drop function' to BEFORE/ABOVE the drop table,
and all is well when i iterate to a new instance of sql code.
when i did as you recommended:
> Look in pg_proc for a function that tries to use a type of 779927:
>
> select proname, proargtypes, prorettype from pg_proc;
i found several functions with the same name, but different arg
types, because each was referring to a table-def that no longer
existed.
which explains the next thing:
> That will be the one that is causing the problem. (Of course the
> next question is how it got that way.)
so maybe extra hooks need to be added to be sure that procedures
that depend on tables being dropped, are also dropped at the same
time? just like the 'warning: dropping indexes and rules and
triggers based on this here table you're dropping...' thing?
--
It is always hazardous to ask "Why?" in science, but it is often
interesting to do so just the same.
-- Isaac Asimov, 'The Genetic Code'
will@serensoft.com
http://groups.yahoo.com/group/newbieDoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!
will trillich wrote: >On Wed, Feb 28, 2001 at 12:52:10PM +0000, Oliver Elphick wrote: >> will trillich wrote: >> >so i've got my data recovered (thanks to oliver) and now >> >i wanna back it up with a pg_dumpall... >> > >> >instead, i get 'failed sanity check, type with oid 779927 was no >> >found' in the oddest places... >> >> More than 1 place? > >well, it seems to move around. once per 'pg_dumpall' attempt, >but not always in the same spot... I think that confirms that its placing is due to stderr mixing with stdout and has no meaning in itself. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "These things have I written unto you that believe on the name of the Son of God; that ye may know that ye have eternal life, and that ye may believe on the name of the Son of God." I John 5:13
Am Mittwoch, 28. Februar 2001 10:43 schrieb will trillich: > so i've got my data recovered (thanks to oliver) and now > i wanna back it up with a pg_dumpall... > > instead, i get 'failed sanity check, type with oid 779927 was no > found' in the oddest places... I'm just guessing, but it is possible that you're using different versions of pg_dump and postmaster? I had a similar problem when I had an old 7.0 binary lying around in /usr/bin/ when trying to dump a 7.1 database. -- =================================================== Mario Weilguni KPNQwest Austria GmbH Senior Engineer Web Solutions Nikolaiplatz 4 tel: +43-316-813824 8020 graz, austria fax: +43-316-813824-26 http://www.kpnqwest.at e-mail: mario.weilguni@kpnqwest.com ===================================================
Mario Weilguni wrote:
>
> Am Mittwoch, 28. Februar 2001 10:43 schrieb will trillich:
> > so i've got my data recovered (thanks to oliver) and now
> > i wanna back it up with a pg_dumpall...
> >
> > instead, i get 'failed sanity check, type with oid 779927 was no
> > found' in the oddest places...
>
> I'm just guessing, but it is possible that you're using different versions of
> pg_dump and postmaster? I had a similar problem when I had an old 7.0 binary
> lying around in /usr/bin/ when trying to dump a 7.1 database.
no, it was that i'd been iterating through a series of
drop table xyz;
create table xyz ( ... );
drop function pdq ( xyz );
create function pdq ( xyz ) returns .... ;
when it reached the "drop function" it was referenceing an old
instance (oid) of the xyz table. then a new function was created
that referred to the new table (oid). i found six such functions
referring to nonexistent tables.
apparently postgresql does cascading correctly on rules, indexes,
and triggers when deleting tables that they depend on. but a function
argument that's defined in terms of a table -- apparently that slipped
through the cracks!
i.e.
create table eg( id int4 );
create index ed_id_idx on eg ( id );
create function go( eg ) .... ;
drop table eg; -- this also drops the index, but not the function
so, instead, now i
drop function pdq ( xyz );
drop table xyz;
create table xyz ( ... );
create function pdq ( xyz ) returns .... ;
dropping the function first, works like a charm.
--
mailto:will@serensoft.com
http://www.dontUthink.com/