> -----Original Message----- > From: Richard Huxton [mailto:dev@xxxxxxxxxxxx] > Sent: 29 mars 2006 17:10 > To: Eric Lauzon > Cc: pgsql-performance@xxxxxxxxxxxxxx > 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.
