Message-ID: <442B05B0.5080507@archonet.com>
Date: Wed, 29 Mar 2006 23:09:52 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 1.5 (X11/20051201)
MIME-Version: 1.0
To: Eric Lauzon <eric.lauzon@abovesecurity.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Database possible corruption , unsolvable mystery
References: <F7B73864DD39FA40B6C56B3CE0D4D1CBF00796@asdc003.abovesecurite.lan>
In-Reply-To: 
 <F7B73864DD39FA40B6C56B3CE0D4D1CBF00796@asdc003.abovesecurite.lan>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Eric Lauzon wrote:
> This is why our investigation brought us to the folowing questions:
> 
> 1. Are postgresql data file name are hashed references to table
> name(as oracle)? [~path to data EX:/var/log/pgsql/data/[arbitraty
> numbers]/[datafile]]?

OID numbers - look in the contrib directory/package for the oid2name 
utility.

> 2. If the data files are corrupted and we re-create is it possible it
> uses the same files thus creating the same issue?

No

> 3. Since we know that all the tables has that problems is there an
> internal table with undisclosed references to tables data files?  I
> hope the questions were clear.

You mean a system table that could account for your problems since it 
refers to some of your tables but not others? No.

The obvious places to start are:
1. vacuum analyse verbose on the tables in question
    This should show whether there are a lot of "dead" rows
2. explain analyse on problem queries
    To see if the query plans are correct
3. SELECT * FROM pg_stat_???
    Assuming you have statistics gathering turned on, this might show 
unusual table accesses.

HTH
-- 
   Richard Huxton
   Archonet Ltd