Re: pg_dump failed sanity check and user defined types

Brook Milligan <brook(at)biology(dot)nmsu(dot)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. Try
select oid,* from pg_proc where not exists
(select 1 from pg_type where 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