Re: drop table and pg_proc - Mailing list pgsql-hackers

> Tatsuo Ishii <t-ishii@sra.co.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
%sLANGUAGE ",                       (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@srapc1474 pg_dump]$