Re: inconsistent state after crash recovery

(2013/08/02 21:19), Robert Haas wrote:
> On Fri, Aug 2, 2013 at 8:17 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>>> On Fri, Jul 26, 2013 at 8:27 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>>> would you expect crash recovery to notice the disappearance of a file
>>>> that was touched nowhere in the replayed actions?
>>
>>> Eh, maybe not. But should we try harder to detect the unexpected
>>> disappearance of one that is?
>>
>> We do, don't we? The replay stuff should complain unless it sees a drop
>> or truncate covering any unaccounted-for pages.
>
> Hmm. Yeah. But the OP seems to think it doesn't work.

Yes. I'm afraid that.

My attached script shows that crash recovery re-creates the lost
table file implicitly, and fills some of those blocks (maybe
lower ones) with zero without any notice. We can easily observe
it by using pg_filedump.

Thus, the table file can lose records, but DBA cannot recognize
it because no message is left in the server log.

I agree that this is not a PostgreSQL bug.

However, DBA still needs to detect this table corruption,
brought by several components which PostgreSQL relys on, to
consider restoring from database backup.

If PostgreSQL can detect and tell something about that, it
would be really helpful for DBA to make some critical decision.
I think PostgreSQL will be able to do that.

Regards,
--
Satoshi Nagayasu <snaga(at)uptime(dot)jp>
Uptime Technologies, LLC. http://www.uptime.jp