Re: pg_dump failed sanity check and user defined types - Mailing list pgsql-hackers

>      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';