pg_class and relfilenode

[muteki muteki]

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...4/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_usena me_index
pg_stat_activit y
pg_stat_databas e

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

[William ZHANG]

> e.g.

pg_shadow_usena me_index
pg_stat_activit y
pg_stat_databas e

They are system views and correspond to no file.
See PostgreSQL's documents.

[Andrew Sullivan]

On Tue, Feb 10, 2004 at 08:29:50PM -0800, muteki muteki wrote:

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...4/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

You don't need to do any data migration to get off 7.2.3 -- 7.2.4 is
a drop-in replacement, so you should at least do that upgrade first.

But I don't think that power failures would be enough to cause the
kind of problem you're describing, unless you're running without
fsync or something. Care to give more details?

In any case, I think that your script is mighty dangerous. It sounds
like a recipe for data loss to me. Postgres is considerably more
robust than this, and I think you're trying to cover up some serious
problems that you may have, likely with your hardware.

A
--
Andrew Sullivan

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[Tom Lane]

"muteki muteki" <mu******@hotma il.com> writes:

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.
At the very least you should be running 7.2.4. We do not make
dot-releases for entertainment value, we make them because there
are critical bug fixes. In particular, this 7.2.4 fix:
* Ensure pg_clog updates are sync'd to disk before marking checkpoint complete
might well have something to do with your problems.
I found that there are already some files
(mainly pg_*) tables/indexes are not being consistent
with the filesystem. e.g.
pg_shadow_usena me_index
pg_stat_activit y
pg_stat_databas e

Views don't have underlying files at all. Shared tables live in a
different directory. You had better study the system catalogs more
carefully before you start writing scripts to hack them.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)