I am currently having the corrupted tables issues described in the following link (possibly caused by power failure, which happens pretty often) http://archives.postgresql.org/pgsql-admin/2003-04/msg00012.php. Since we have our systems being deployed to numerous remote systems (psql 7.2.3), upgrading the entire database (with data migration) will be the least preferable solution. I am thinking of creating a script that periodically check the relfilenode inside the pg_class and if there is any mismatch between what pg_class described and what actually exists in the file system, the script will touch that file and try to drop the table. However, after I wrote the script and started testing it, I found that there are already some files (mainly pg_*) tables/indexes are not being consistent with the filesystem.
e.g. pg_shadow_usename_index pg_stat_activity pg_stat_database
And my script look something like: select oid, * from pg_database where datname = <db> select oid, relname from pg_class and touch <base>/DB#/FILE# for everything found inside pg_class if it doesn't exist on the file system.
Is it going to harm the database if I blindly touch those files? Or is there any workaround/trick/patch I can apply to version 7.2.3 without upgrading the whole database? Something like a patch/trick which can force drop a table will be my main goal.
Thanks,
--muteki
_________________________________________________________________
Create your own personal Web page with the info you use most, at My MSN. http://click.atdmt.com/AVE/go/onm00200364ave/direct/01/
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
