Обсуждение: 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