Re: drop table and pg_proc

> Tatsuo Ishii <t-ishii(at)sra(dot)co(dot)jp> writes:
> > Suppose a function using table t1 as its argument:
> > create table t1(...
> > create fuction f1(t1) returns...
> > And if I drop t1 then do pg_dump, I would got something like:
> > failed sanity check, type with oid 1905168 was not found
> > This is because the type t1 does not exist anynmore. Since not being
> > able to make a back up of database is a critical problem, I think we
> > have to fix this.
>
> This is just one instance of the generic problem that we don't enforce
> referential integrity across system catalogs. Since this issue has
> always been there, I'm not inclined to panic about it (ie, I don't want
> to try to solve it for 7.1). But we should think about a long-term fix.
>
> > 1) remove that proc entry from pg_proc if t1 is deleted
> > 2) fix pg_dump so that it ignores sunch a bogus entry
> > 3) do both 1) and 2)
>
> Ultimately we should probably do both. #2 looks easier and is probably
> the thing to work on first. In general, pg_dump is fairly brittle when
> it comes to missing cross-references, eg, I think it fails to even
> notice a table that has no corresponding owner in pg_shadow (it should
> be doing an outer not inner join for that). It'd be worth fixing
> pg_dump so that it issues warnings about such cases but tries to plow
> ahead anyway.
>
> regards, tom lane

I'm working on #2. Here is a partial fix for pg_dump, FYI. If it looks
ok, I'll do more cleanup...

$ cvs diff -c common.c pg_dump.c
Index: common.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/bin/pg_dump/common.c,v
retrieving revision 1.49
diff -c -r1.49 common.c
*** common.c 2001/01/12 15:41:29 1.49
--- common.c 2001/01/21 01:38:48
***************
*** 86,95 ****
}
}

! /* should never get here */
! fprintf(stderr, "failed sanity check, type with oid %s was not found\n",
! oid);
! exit(2);
}

/*
--- 86,93 ----
}
}

! /* no suitable type name was found */
! return(NULL);
}

/*
***************
*** 114,120 ****
/* should never get here */
fprintf(stderr, "failed sanity check, opr with oid %s was not found\n",
oid);
! exit(2);
}


--- 112,120 ----
/* should never get here */
fprintf(stderr, "failed sanity check, opr with oid %s was not found\n",
oid);
!
! /* no suitable operator name was found */
! return(NULL);
}


Index: pg_dump.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/bin/pg_dump/pg_dump.c,v
retrieving revision 1.187
diff -c -r1.187 pg_dump.c
*** pg_dump.c 2001/01/12 15:41:29 1.187
--- pg_dump.c 2001/01/21 01:38:56
***************
*** 2928,2933 ****
--- 2928,2942 ----
char *elemType;

elemType = findTypeByOid(tinfo, numTypes, tinfo[i].typelem, zeroAsOpaque);
+ if (elemType == NULL)
+ {
+ fprintf(stderr, "Notice: type for oid %s is not dumped.\n",
+ tinfo[i].typelem);
+ resetPQExpBuffer(q);
+ resetPQExpBuffer(delq);
+ continue;
+ }
+
appendPQExpBuffer(q, ", element = %s, delimiter = ", elemType);
formatStringLiteral(q, tinfo[i].typdelim);
}
***************
*** 3086,3091 ****
--- 3095,3101 ----
char *listSep;
char *listSepComma = ",";
char *listSepNone = "";
+ char *rettypename;

if (finfo[i].dumped)
return;
***************
*** 3147,3152 ****
--- 3157,3177 ----
char *typname;

typname = findTypeByOid(tinfo, numTypes, finfo[i].argtypes[j], zeroAsOpaque);
+ if (typname == NULL)
+ {
+ fprintf(stderr, "Notice: function \"%s\" is not dumped\n",
+ finfo[i].proname);
+
+ fprintf(stderr, "Reason: the %d th arugument type name (oid %s) not found\n",
+ j, finfo[i].argtypes[j]);
+ resetPQExpBuffer(q);
+ resetPQExpBuffer(fn);
+ resetPQExpBuffer(delqry);
+ resetPQExpBuffer(fnlist);
+ resetPQExpBuffer(asPart);
+ return;
+ }
+
appendPQExpBuffer(fn, "%s%s",
(j > 0) ? "," : "",
typname);
***************
*** 3159,3169 ****
resetPQExpBuffer(delqry);
appendPQExpBuffer(delqry, "DROP FUNCTION %s;\n", fn->data );

resetPQExpBuffer(q);
appendPQExpBuffer(q, "CREATE FUNCTION %s ", fn->data );
appendPQExpBuffer(q, "RETURNS %s%s %s LANGUAGE ",
(finfo[i].retset) ? "SETOF " : "",
! findTypeByOid(tinfo, numTypes, finfo[i].prorettype, zeroAsOpaque),
asPart->data);
formatStringLiteral(q, func_lang);

--- 3184,3211 ----
resetPQExpBuffer(delqry);
appendPQExpBuffer(delqry, "DROP FUNCTION %s;\n", fn->data );

+ rettypename = findTypeByOid(tinfo, numTypes, finfo[i].prorettype, zeroAsOpaque);
+
+ if (rettypename == NULL)
+ {
+ fprintf(stderr, "Notice: function \"%s\" is not dumped\n",
+ finfo[i].proname);
+
+ fprintf(stderr, "Reason: return type name (oid %s) not found\n",
+ finfo[i].prorettype);
+ resetPQExpBuffer(q);
+ resetPQExpBuffer(fn);
+ resetPQExpBuffer(delqry);
+ resetPQExpBuffer(fnlist);
+ resetPQExpBuffer(asPart);
+ return;
+ }
+
resetPQExpBuffer(q);
appendPQExpBuffer(q, "CREATE FUNCTION %s ", fn->data );
appendPQExpBuffer(q, "RETURNS %s%s %s LANGUAGE ",
(finfo[i].retset) ? "SETOF " : "",
! rettypename,
asPart->data);
formatStringLiteral(q, func_lang);

[t-ishii(at)srapc1474 pg_dump]$