Re: pg_dump failed sanity check and user defined types

> 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. 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.

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