pg_class and relfilenode

From: "muteki muteki" <muteki_f(at)hotmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: pg_class and relfilenode
Date: 2004-02-11 04:29:50
Message-ID: Law10-F85Xr6nbb3MOs0001d97e@hotmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

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/

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2004-02-11 04:31:54 Re: Join query on 1M row table slow
Previous Message Tom Lane 2004-02-11 04:02:08 Re: Changing schema owner?