Thread: drop table and pg_proc
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
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, Pennsylvania19026
> 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]$
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