By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,571 Members | 967 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,571 IT Pros & Developers. It's quick & easy.

pg_class and relfilenode

P: n/a
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_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

Nov 22 '05 #1
Share this Question
Share on Google+
3 Replies

P: n/a
> e.g.
pg_shadow_usename_index
pg_stat_activity
pg_stat_database


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

Nov 22 '05 #2

P: n/a
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*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 22 '05 #3

P: n/a
"muteki muteki" <mu******@hotmail.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_usename_index
pg_stat_activity
pg_stat_database


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 YourEmailAddressHere" to ma*******@postgresql.org)

Nov 22 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.