Обсуждение: pg_dump failed sanity check and user defined types

I have a script that dumps the content of a 7.0.2 database using
pg_dump.  I dump the data using the -acD flags, then the schema using
the -scD flags.  For databases with no user defined types, this works
fine.

However, I have one database with a user defined type and get the
following error from pg_dump:
    pg_dump -sc -D test > pg_dump.schema || true    failed sanity check, type with oid 3516132 was not found

Any clues about this?  Does it matter?

Thanks for your help.

Cheers,
Brook

Brook Milligan <brook@biology.nmsu.edu> writes:
> However, I have one database with a user defined type and get the
> following error from pg_dump:
>      pg_dump -sc -D test > pg_dump.schema || true
>      failed sanity check, type with oid 3516132 was not found
> Any clues about this?  Does it matter?

Sounds like you dropped a user type without remembering to drop all
the functions/operators defined for it.  Unfortunately there's no
safety cross-check in DROP TYPE (probably there should be).

It does matter, since IIRC pg_dump aborts when it finds such an
inconsistency; so you're getting an incomplete dump.

You should be able to find the offending entries by searching through
the system catalogs with queries likeselect * from pg_operator where oprleft = 3516132
        regards, tom lane

>      pg_dump -sc -D test > pg_dump.schema || true  >      failed sanity check, type with oid 3516132 was not
found
  Sounds like you dropped a user type without remembering to drop all  the functions/operators defined for it.
Unfortunatelythere's no  safety cross-check in DROP TYPE (probably there should be).
 

That's what I would have guessed, but I'm pretty sure that is not the
case (but I'm new to UDTs, so bear with me; maybe I'm not constructing
my script right).  See the script below that does the installation of
the types and functions.  The problem occurs after running this script
followed by the pg_dump above.

Is there some order dependency for dropping types and functions?
Should I not be dropping these before creating them (I do this to
allow rerunning the script)?  Does it have anything to do with the
fact that a single object.so provides all the entry points?
  You should be able to find the offending entries by searching through  the system catalogs with queries like   select
*from pg_operator where oprleft = 3516132
 

There are no rows found.

Cheers,
Brook

===========================================================================

-- type_xxx

DROP TYPE type_xxx;

DROP FUNCTION type_xxx_in (opaque);

CREATE FUNCTION type_xxx_in (opaque)   RETURNS type_xxx   AS '/path/to/object.so', 'type_xxx_in'   LANGUAGE 'c';

DROP FUNCTION type_xxx_out(opaque);

CREATE FUNCTION type_xxx_out(opaque)   RETURNS opaque   AS '/path/to/object.so', 'type_xxx_out'   LANGUAGE 'c';

CREATE TYPE type_xxx (   internallength = 72,   input = type_xxx_in,   output = type_xxx_out
);


-- type_yyy

DROP TYPE type_yyy;

DROP FUNCTION type_yyy_in (opaque);

CREATE FUNCTION type_yyy_in (opaque)   RETURNS type_yyy   AS '/path/to/object.so', 'type_yyy_in'   LANGUAGE 'c';

DROP FUNCTION type_yyy_out(opaque);

CREATE FUNCTION type_yyy_out(opaque)   RETURNS opaque   AS '/path/to/object.so', 'type_yyy_out'   LANGUAGE 'c';

CREATE TYPE type_yyy (   internallength = 76,   input = type_yyy_in,   output = type_yyy_out
);

-- type_zzz

DROP TYPE type_zzz;

DROP FUNCTION type_zzz_in (opaque);

CREATE FUNCTION type_zzz_in (opaque)   RETURNS type_zzz   AS '/path/to/object.so', 'type_zzz_in'   LANGUAGE 'c';

DROP FUNCTION type_zzz_out(opaque);

CREATE FUNCTION type_zzz_out(opaque)   RETURNS opaque   AS '/path/to/object.so', 'type_zzz_out'   LANGUAGE 'c';

CREATE TYPE type_zzz (   internallength = 112,   input = type_zzz_in,   output = type_zzz_out
);

-- conversions

DROP FUNCTION type_xxx (type_yyy);
CREATE FUNCTION type_xxx (type_yyy)    RETURNS type_xxx    AS '/path/to/object.so', 'type_xxx_from_type_yyy'
LANGUAGE'c';
 

DROP FUNCTION type_xxx (type_zzz);
CREATE FUNCTION type_xxx (type_zzz)    RETURNS type_xxx    AS '/path/to/object.so', 'type_xxx_from_type_zzz'
LANGUAGE'c';
 

DROP FUNCTION type_yyy (type_xxx);
CREATE FUNCTION type_yyy (type_xxx)    RETURNS type_yyy    AS '/path/to/object.so', 'type_yyy_from_type_xxx'
LANGUAGE'c';
 

DROP FUNCTION type_yyy (type_zzz);
CREATE FUNCTION type_yyy (type_zzz)    RETURNS type_yyy    AS '/path/to/object.so', 'type_yyy_from_type_zzz'
LANGUAGE'c';
 

DROP FUNCTION type_zzz (type_xxx);
CREATE FUNCTION type_zzz (type_xxx)    RETURNS type_zzz    AS '/path/to/object.so', 'type_zzz_from_type_xxx'
LANGUAGE'c';
 

DROP FUNCTION type_zzz (type_yyy);
CREATE FUNCTION type_zzz (type_yyy)    RETURNS type_zzz    AS '/path/to/object.so', 'type_zzz_from_type_yyy'
LANGUAGE'c';
 

>      pg_dump -sc -D test > pg_dump.schema || true     >      failed sanity check, type with oid 3516132 was not
found

The problem seems to be related to trying to install conversion
functions from one user defined type to another.  Scripts like the
following are fine:
    DROP TYPE xxx;
    DROP FUNCTION xxx_in (opaque);    CREATE FUNCTION xxx_in (opaque) RETURNS xxx AS '_OBJWD_/xxx.so', 'xxx_in'
LANGUAGE'c';
 
    DROP FUNCTION xxx_out(opaque);    CREATE FUNCTION xxx_out(opaque) RETURNS opaque AS '_OBJWD_/xxx.so', 'xxx_out'
LANGUAGE'c';
 
    CREATE TYPE xxx (internallength = 8, input = xxx_in, output = xxx_out);
    DROP TYPE yyy;
    DROP FUNCTION yyy_in (opaque);    CREATE FUNCTION yyy_in (opaque) RETURNS yyy AS '_OBJWD_/xxx.so', 'yyy_in'
LANGUAGE'c';
 
    DROP FUNCTION yyy_out(opaque);    CREATE FUNCTION yyy_out(opaque) RETURNS opaque AS '_OBJWD_/xxx.so', 'yyy_out'
LANGUAGE'c';
 
    CREATE TYPE yyy (internallength = 8, input = yyy_in, output = yyy_out);

But as soon as I add a conversion like the following to the end (I
presume conversion functions must follow the type definitions), I get
failed sanity checks.
    DROP FUNCTION xxx (yyy);    CREATE FUNCTION xxx (yyy) RETURNS xxx AS '_OBJWD_/xxx.so', 'xxx_int' LANGUAGE 'c';

I presume that notices like the following
    NOTICE:  ProcedureCreate: type 'xxx' is not yet defined

are fine, because you must create the I/O functions before the type.

So, how is one really supposed to create user defined types with
conversion functions without tripping on failed sanity checks?
Where else in the system catalogs can I look to find references to
the missing OIDs?

Thanks again for your help.

Cheers,
Brook

Brook Milligan <brook@biology.nmsu.edu> writes:
>> failed sanity check, type with oid 3516132 was not found

>    Sounds like you dropped a user type without remembering to drop all
>    the functions/operators defined for it.  Unfortunately there's no
>    safety cross-check in DROP TYPE (probably there should be).

> That's what I would have guessed, but I'm pretty sure that is not the
> case (but I'm new to UDTs, so bear with me; maybe I'm not constructing
> my script right).  See the script below that does the installation of
> the types and functions.  The problem occurs after running this script
> followed by the pg_dump above.

I can't duplicate that, either in current sources or 7.0.2.  Are you
sure you're blaming the right bit of script?

> Is there some order dependency for dropping types and functions?
> Should I not be dropping these before creating them (I do this to
> allow rerunning the script)?  Does it have anything to do with the
> fact that a single object.so provides all the entry points?

What you showed looks fine.

>    You should be able to find the offending entries by searching through
>    the system catalogs with queries like
>        select * from pg_operator where oprleft = 3516132

> There are no rows found.

You may need to dig into pg_dump and see exactly what it's complaining
about ... it's getting that OID from someplace ...
        regards, tom lane

Brook Milligan <brook@biology.nmsu.edu> writes:
> But as soon as I add a conversion like the following to the end (I
> presume conversion functions must follow the type definitions), I get
> failed sanity checks.

>      DROP FUNCTION xxx (yyy);

Sure.  By the time you execute that, you've already deleted the old
yyy type and created a new one.  So this is trying to delete a function
named xxx that takes the *new* yyy type, which there isn't one of (and
DROP FUNCTION complains accordingly).

The old function xxx(old-yyy-type) is still in the catalogs, and will
confuse pg_dump.  Moreover, there's no way to specify that function by
name, because there's no longer any name for its argument type.  If
you don't want to drop the whole DB, you'll have to delete the pg_proc
tuple by OID, after you figure out which one it is.  Tryselect oid,* from pg_proc where not exists(select 1 from
pg_typewhere oid = proargtypes[0]);
 
(ditto for prorettype and the other proargtypes entries).

My advice would be to make your script drop all the function definitions
before you drop the type names.

What we really need is some sort of "DROP TYPE foo CASCADE" command
that will clean up all the relevant entries at once ...
        regards, tom lane