[HACKERS] pg_dump failed sanity check and user defined types

[Brook Milligan]

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

[Brook Milligan]

> 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