Обсуждение: pg_class and relfilenode

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/

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-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

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

"muteki muteki" <muteki_f@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

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

Thanks for the information.  If I can simply do a drop-in replacement,
that may be something I can try.  But I am still thinking there could
be other cases database will get corrupted due to power failure and
will not be able to startup correctly again.  (missing some pg_files)

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

And you have pointed out my concern.  Even though we have WAL
enable, we have intentionally disabled both fsync and fdatasync
inside the kernel because of other reasons.  As long as there are
ways I can eliminate database being corrupted (or correctly and
automatically detected the corruption and drop the tables if
necessary), that should satisfy my need.

The persistence of the data is important for me, but disk performance
and availability of a funcational database has a higher priority for my
need.

Thanks,

--muteki

_________________________________________________________________
Let the advanced features & services of MSN Internet Software maximize your
online time. http://click.atdmt.com/AVE/go/onm00200363ave/direct/01/

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

Do you know the fix to allow forcefully drop a table (even though the
relfilenode doesn't exist) is in 7.2.4?

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

Thanks for the valuable information.   Actually, I have tried to focus on
chapter 3 of PostgreSQL 7.2 Documentation and having difficulty in finding
the information you mentioned above.  Am I reading the right document?

Thanks,

--muteki

_________________________________________________________________
Optimize your Internet experience to the max with the new MSN Premium
Internet Software. http://click.atdmt.com/AVE/go/onm00200359ave/direct/01/

On Wed, Feb 11, 2004 at 10:08:30PM -0800, muteki muteki wrote:
> And you have pointed out my concern.  Even though we have WAL
> enable, we have intentionally disabled both fsync and fdatasync
> inside the kernel because of other reasons.  As long as there are
> ways I can eliminate database being corrupted (or correctly and
> automatically detected the corruption and drop the tables if
> necessary), that should satisfy my need.

Well, simple choice. If you don't use fsync or fdatasync then there are no
guarentees for your data. Simple as that. I guess you need to evaluate
exactly how much you value it...
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> (... have gone from d-i being barely usable even by its developers
> anywhere, to being about 20% done. Sweet. And the last 80% usually takes
> 20% of the time, too, right?) -- Anthony Towns, debian-devel-announce

"muteki muteki" <muteki_f@hotmail.com> writes:
> And you have pointed out my concern.  Even though we have WAL
> enable, we have intentionally disabled both fsync and fdatasync
> inside the kernel because of other reasons.

[blinks...]  Surely you're joking?

If you insist on running a configuration as brain-dead as that, you
*will* get data corruption on power failures.  And it's your own
fault.  Don't expect any sympathy here.

            regards, tom lane

On Wed, Feb 11, 2004 at 10:08:30PM -0800, muteki muteki wrote:

> Thanks for the information.  If I can simply do a drop-in replacement,
> that may be something I can try.  But I am still thinking there could
> be other cases database will get corrupted due to power failure and
> will not be able to startup correctly again.  (missing some pg_files)

Under nromal operation, there is _no way_ you should get a corrupted
database from a power failure.  PostgreSQL's WAL system is designed
to handle machine crashes, but only. . .

> enable, we have intentionally disabled both fsync and fdatasync
> inside the kernel because of other reasons.

. . .if you don't do things like that.  That is, frankly, just a
recipe for disaster.  Your attempts to work around this are likely to
cause at least as much damage as the corruption you might get from
power failures.  I advise you very strongly to reconsider your
approach.

> The persistence of the data is important for me, but disk performance
> and availability of a funcational database has a higher priority for my
> need.

I think you are very likely to make your database all but completely
useless with this strategy.  What will you do if you get, say, a
corrupted pg_class table?  Can't drop that -- you'll have no database
that you can see.

A

--
Andrew Sullivan

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