Re: [HACKERS] drop table and pg_proc
Tatsuo Ishii [EMAIL PROTECTED] 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
Re: [HACKERS] drop table and pg_proc
Tatsuo Ishii [EMAIL PROTECTED] 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.c2001/01/12 15:41:29 1.49
--- common.c2001/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);
Re: [HACKERS] drop table and pg_proc
Tatsuo Ishii [EMAIL PROTECTED] 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
RE: [HACKERS] drop table and pg_proc
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
Re: [HACKERS] drop table and pg_proc
Add to TODO: * Enforce referential integrity for system tables Tatsuo Ishii [EMAIL PROTECTED] 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 [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
