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

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

This is just one instance of the generic problem that we don't enforce
referential integrity across system catalogs. Since this issue has

Wouldn't be easy to do for views (rules) anyway - table oids are somewhere
in the body of rule, they are not just keys in column. Also, triggers are
handled by Executor and we don't use it for DDL statements. I think it's ok,
we have just add "isdurty" column to some tables (to be setted when some of
refferenced objects deleted/altered and to be used as flag that
"re-compiling"
is required) and new table to remember object relationships.

Guys here, in Sectorbase, blames PostgreSQL a much for this thing -:)
They are Oracle developers and development under PostgreSQL makes
them quite unhappy. Probably, work in this area will be sponsored
by my employer (with me as superviser and some guys in Russia as
developers), we'll see.

Vadim

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

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

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