Re: Database possible corruption , unsolvable mystery

> -----Original Message-----
> From: Richard Huxton [mailto:dev(at)archonet(dot)com]
> Sent: 29 mars 2006 17:10
> To: Eric Lauzon
> Cc: pgsql-performance(at)postgresql(dot)org
> Subject: Re: [PERFORM] Database possible corruption ,
> unsolvable mystery
>
> 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.

This will give me the location of the databases file for a specific
table or index?

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

humm why would it affect only original table , and copy of that table
renamed back to the original table name
but not the copy.

example:
original table name : table_problem <issue>
copy name : table_problem_copy <no issue>
renamed copyed table: table_problem <issue>

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

Well actualy its affecting only one table in a set of 5 table
(referential integrity)
and the table affected if the [referenced table] so it might be system
related, but
as stated if all the data is copied to a create table
copy_of_problematic_table as select * from problematic_table
there is 0 issue but as soon as copy_of_problematic_table is renamed to
problematic_table the problems is back.

But we have 2 orther set of 5 table in the same database built exactly
the same way and it dosen't
seem affected by the same problems, this is why i am wandering why the
problems is recurent if
internal postgresql data file are name bound ...and i am not taking
about the OID.

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

Btw i can't give vacuum info right now because the source database is
being dumped for complete re-insertion.

Mabey later if this dosen't fix the problem , and as of information its
7.4.6 [i know its not the most rescent]
but it is the way it is right now and we suspect the problem might have
come from a power outage while there was
a full vacuum and the reason why its only one table that has been
affected is probably because it was the table being vacummed,
but this is only an assumption right now and more info will folow if the
problems persis after a full restore.

Thanks you :)
-elz

AVERTISSEMENT CONCERNANT LA CONFIDENTIALITE

Le present message est a l'usage exclusif du ou des destinataires mentionnes ci-dessus. Son contenu est confidentiel et peut etre assujetti au secret professionnel. Si vous avez recu le present message par erreur, veuillez nous en aviser immediatement et le detruire en vous abstenant d'en faire une copie, d'en divulguer le contenu ou d'y donner suite.

CONFIDENTIALITY NOTICE

This communication is intended for the exclusive use of the addressee identified above. Its content is confidential and may contain privileged information. If you have received this communication by error, please notify the sender and delete the message without copying or disclosing it.