Subject: Re: Database possible corruption , unsolvable mystery
From: Mark Lewis <mark.lewis@mir3.com>
To: Eric Lauzon <eric.lauzon@abovesecurity.com>
Cc: pgsql-performance@postgresql.org
In-Reply-To: 
 <F7B73864DD39FA40B6C56B3CE0D4D1CBF00796@asdc003.abovesecurite.lan>
References: <F7B73864DD39FA40B6C56B3CE0D4D1CBF00796@asdc003.abovesecurite.lan>
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Organization: MIR3, Inc.
Date: Wed, 29 Mar 2006 14:16:51 -0800
Message-Id: <1143670611.5634.11.camel@archimedes>
Mime-Version: 1.0

Can you post an explain analyze for the delete query?  That will at
least tell you if it is the delete itself which is slow, or a trigger /
referential integrity constraint check.  Which version of PG is this?

-- Mark Lewis

On Wed, 2006-03-29 at 12:58 -0500, Eric Lauzon wrote:
> Greetings,
>=20
> 	We have an issue where we have a database with many tables.
> 	The layout of the database is 3 set of look alike tables with different =
names.
> 	Each set of tables has some referential integrety that point back to the=
 main
> 	control table.
>=20
> 	On two set of tables we are able to efficiently delete referential and m=
ain record
> 	without a problems, but on the 3rd set we have an issue where the contro=
l table is clugged
> 	and delete seem to take forever , as example on the first two set a dele=
te of 60K record take about
> 	4 second to 10 second but on the 3rd set it can take as long as 3hours.
>=20
> 	This seem to be only affecting one database , the schema and way of doin=
g is replicated elsewhere
> 	and if efficient.
>=20
> 	The question is, even after droping 3rd set integrity , dumping the tabl=
e data , deleting the table,
> 	doing a manual checkpoint and recreating the table with the dump data , =
with or without referential
> 	integrity , the problems still araise.
>=20
> 	If we copy the data from the live table and do a create table aaa as sel=
ect * from problematic_table;
> 	the table aaa operations are normal and efficient.
>=20
> 	This is why our investigation brought us to the folowing questions:
>=20
> 	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]]?
>=20
> 	2. If the data files are corrupted and we re-create is it possible it us=
es the same files thus creating the same issue?
>=20
>  	3. Since we know that all the tables has that problems is there an inte=
rnal table with undisclosed references to tables data files?
> =09
> 	I hope the questions were clear.
>=20
> 	Have a good day, and thank you in advance.
>=20
>=20
> Eric Lauzon
> [Recherche & D=C3=A9veloppement]
> Above S=C3=A9curit=C3=A9 / Above Security
> T=C3=A9l  : (450) 430-8166
> Fax : (450) 430-1858
>=20
> AVERTISSEMENT CONCERNANT LA CONFIDENTIALIT=C3=89=20
>=20
> Le pr=C3=A9sent message est =C3=A0 l'usage exclusif du ou des destinatair=
es mentionn=C3=A9s ci-dessus. Son contenu est confidentiel et peut =C3=AAtr=
e assujetti au secret professionnel. Si vous avez re=C3=A7u le pr=C3=A9sent=
 message par erreur, veuillez nous en aviser imm=C3=A9diatement et le d=C3=
=A9truire en vous abstenant d'en faire une copie, d'en divulguer le contenu=
 ou d'y donner suite.
>=20
> CONFIDENTIALITY NOTICE
>=20
> This communication is intended for the exclusive use of the addressee ide=
ntified above. Its content is confidential and may contain privileged infor=
mation. If you have received this communication by error, please notify the=
 sender and delete the message without copying or disclosing it.
>=20
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>=20
>                http://archives.postgresql.org