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.

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)

Comments?
--
Tatsuo Ishii

Thu, 11 Jan 2001 21:42:10 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
drop table and pg_proc
Tatsuo Ishii wrote:
> 
> 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.
> 
> 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)

I have the same problem with views. If I create a view, drop/recreate
the tables to which it references, pg_dump fails unless I also drop and
recreate the view. I have seen similar behavior with indexes based on
user functions, when a function is dropped and recreated.

I suspect that this is because all these things get an OID, and the OIDs
change when things get modified. There should be a way to reassign
dependencies, perhaps vacuum should be able to do this?



-- 
http://www.mohawksoft.com

Thu, 11 Jan 2001 07:58:34 -0500
mlw <markw@mohawksoft.com>
Re: drop table and pg_proc
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

Thu, 11 Jan 2001 12:25:16 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: drop table and pg_proc 
Add to TODO:

	* Enforce referential integrity for system tables

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


-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Fri, 12 Jan 2001 00:12:14 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: drop table and pg_proc
> 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 %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@srapc1474 pg_dump]$ 

Sun, 21 Jan 2001 10:56:43 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
Re: drop table and pg_proc 
Tatsuo Ishii <t-ishii@sra.co.jp> writes:
> I'm working on #2. Here is a partial fix for pg_dump, FYI. If it looks
> ok, I'll do more cleanup...

Looks OK as far as it goes.  The other flavor of problems that pg_dump
has in this area are in doing inner joins across system catalogs ...

			regards, tom lane

Mon, 22 Jan 2001 18:33:22 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: drop table and pg_proc 