Très longue transaction

Bonjour!

Je rencontre un gros problème de performances.
Mon application reçoit des données régulièrement, et après traîtmenet, j'insère les informations pertinentes dans la base de données. ; dans la majorité des cas, tout se passe bien et la transaction globale dure moins d'une seconde. Cependant, de temps à autre (une fois sur vingt ?), la transaction met un temps extraordinairement long pour s'achever (avec succès!) ; extraordinairement long signifie en l'occurence environ 1h30 (une heure et trente minutes!!!) mesuré directement dans les logs de postgresql (duration).
Au moment où le problème se produit, je n'ai a priori pas de connexion concurrente, tout au plus une. Je ne pose pas de lock sur mes tables (donc pas de deadlock) ; d'ailleurs le fichier de log indique qu'il ne se passe rien en parallèle...

Ma question est donc simple : qu'est-ce qui peut causer un tel problème ?

Yves

Afin de faciliter votre compréhension, je donne ici des informations complémentaires sur les tables utilisées et les opérations effectuées pendant la transaction:

tables impactées:

CREATE TABLE X
(
Idx serial NOT NULL UNIQUE PRIMARY KEY,
V VARCHAR NOT NULL UNIQUE
);

CREATE TABLE Y
(
Idx serial NOT NULL UNIQUE PRIMARY KEY,
W VARCHAR NOT NULL UNIQUE
);

CREATE TABLE XY
(
Idx serial NOT NULL UNIQUE PRIMARY KEY,
Vx int REFERENCES X(idx),
Vy int REFERENCES Y(idx),
UNIQUE (Vx, Vy)
);

CREATE TABLE Z (
Idx serial NOT NULL UNIQUE PRIMARY KEY,
A int NOT NULL,
B DATE NOT NULL,
C CHAR(4) NOT NULL,
xy int REFERENCES XY(idx),
nbliste VARCHAR
...
UNIQUE (A, B, C)
);

CREATE INDEX date_idx ON Z(Date);

CREATE TABLE XYZ (
Idx int REFERENCES Z(idx) ON DELETE CASCADE,
IdxXY int REFERENCES XY(idx),
UNIQUE (Idx,IdxXY)
);

CREATE TABLE H (
Idx int REFERENCES Z(idx) ON DELETE CASCADE,
data bytea,
UNIQUE (Idx)
);

Donc toutes les tables sont de structure assez simple ; elles sont normalement accédées par l'intermédiaire de leur clé primaire.
Les données A,B,C de la table Z sont juste indiquées parce qu'elles établissent une clé unique, sinon elles n'ont rien de spécial.
Liste contient des informations de couples de données (V,W) qui seront d'une part insérées dans les tables X,Y,XY, et d'autre part dans la table XYZ permettant d'établir le lien global associant la liste de couples associés à un élément de Z.
La table H contient un binaire de taille éventuellement importante, associé de manière biunivoque à un élément de Z ; cet élément aurait pu appartenir directement à la table Z, mais il m'a semblé plus judicieux (??) de le séparer.

Un objet à insérer est donc consituté de:
a, b, c : un triplet identifiant l'objet de manière unique
v, w : un couple spécifique
(v1,w1), (v2,w2), ... (vn,wn) : une liste d'autres couples (de nature similaire, mais sémantiquement différent, du couple précédent)
date : la date de l'opération
data : un binaire

Les données sont insérées en plusieurs étapes, par l'intermédiaires de procédures stockées que je détaillerai ultérieurement.
En pratique:
1) vérifier que l'élément de clé (a,b,c) n'existe pas déjà (select sur clé unique)
2) placer le couple (v,w) dans les table X,Y,XY et récupérer l'index résultant (opérations simples sur des tables indexées)
3) insérer le nouvel élément de clé (a, b, c) dans la table Z (l'index précédent placé dans xy)
4) récupérer l'index associé au nouvel élément dans sa table Z_idx_seq
5) analyser la liste des couples (v1,w1), (v2,w2), ... (vn,wn) (découpage d'une chaine de caractères) :
5a) pour chaque nouveau couple, l'insérer si nécessaire dans les tables X,Y,XY et récupérer l'index résultant
5b) puis insérer le couple (index Z, index couple) dans la table XYZ
6) vérifier (et le cas échéant mettre à jour) le nombre d'éléments de la liste pour l'objet
7) insérer les données associées à l'objet dans la table H

Pour information, les procédures stockées (simplifées) sont, pour les plus importantes:

La première, qui, gère les données des tables X,Y,XY:
---------------------------------------------------------------------------------------------
Essentiellement, elle prend un couple (W,V), place les valeurs dans les tables et retourne l'index associé au couple.
Cette procédure permet de masquer entièrement les tables X,Y,XY pour toute opération
---------------------------------------------------------------------------------------------
// This function inserts (W,V) into the sublying tables and returns the associated XY index.
CREATE OR REPLACE FUNCTION getXY (IN W text, IN V text, OUT idx integer) AS $$
DECLARE
idxY integer;
idxX integer;
res integer := 0;
Y0 text = lower(W);
X0 text = lower(V);
BEGIN
idx:=-1;

//insert W into Y if not present and get the associated index in all cases
SELECT idx FROM Y WHERE Y=Y0 INTO idxY;
IF NOT FOUND THEN
INSERT INTO Y VALUES (DEFAULT, Y0);
GET DIAGNOSTICS res = ROW_COUNT;
IF res=0 THEN RETURN; END IF;
SELECT currval('Y_idx_seq') INTO idxY;
END IF;

//insert V into X if not present and get the associated index in all cases
SELECT idx FROM X WHERE X=X0 INTO idxX;
IF NOT FOUND THEN
INSERT INTO X VALUES (DEFAULT, X0);
GET DIAGNOSTICS res = ROW_COUNT;
IF res=0 THEN RETURN; END IF;
SELECT currval('X_idx_seq') INTO idxX;
END IF;

//insert (idxX,idxY) into XY if not present and get the associated index in all cases
SELECT idx INTO idx FROM XY
WHERE Vy=idxY AND Vx=idxX;
IF NOT FOUND THEN
INSERT INTO XY VALUES (DEFAULT, Vx, Vy);
GET DIAGNOSTICS res = ROW_COUNT;
IF res=0 THEN RETURN; END IF;
SELECT currval('XY_idx_seq') INTO idx;
END IF;

END
$$ LANGUAGE plpgsql VOLATILE;
---------------------------------------------------------------------------------------------

La seconde permet de faire l'opération équivalente, mais sur une liste de couple de données.
Cette procedure permet d'inserer une liste de couple associée à un index d'objets sans jamais voir les tables X,Y,XYZ
---------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION setXYZ (IN idx integer, IN liste text, OUT nb integer) AS $$
DECLARE
n integer := 0;
idxXY integer := 0;
BEGIN
IF liste<>'' THEN
LOOP
-- some simple code to find the next element to insert into X,Y,XY
SELECT * FROM setXY(elt) INTO idxXY;
-- check that the couple is not present (we don't want doubles here)
SELECT t.idxDest FROM XYZ t WHERE t.idx=idx AND d.IdxXY=idxXY;
IF NOT FOUND THEN
INSERT INTO XYZ VALUES (idx, idxXY);
n := n+1;
END IF;
-- some exit con dition here where the list is finished;
END LOOP;
END IF;
nb := n;
END
$$ LANGUAGE plpgsql VOLATILE;
---------------------------------------------------------------------------------------------

La trosième insère les données dans la table Z an faisant appel aux précédentes.
---------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION insertZ
(
IN abc text,
IN vw text,
IN listevw text,
IN nbdansliste integer,
OUT idx integer
) AS $$
DECLARE
nb integer := 0;
idxvw integer;
idxZ integer := -1;
BEGIN
-- checks if element abc is already in Z
idx:=findZ(abc);
IF idx=1 THEN
RETURN;
END IF;
-- push the first (V,W) into the XYZ tables and get the associated index
SELECT * FROM setXY(vw) INTO idxvw;
-- push the Z table data into the table ; this should work since we already know abc is not present
IF idxvw<>-1 THEN
INSERT INTO Z VALUES (DEFAULT, a, b, c, idxvw, nbdansliste, ...);
GET DIAGNOSTICS nb = ROW_COUNT;
END IF;
IF nb=1 THEN
SELECT currval('Z_idx_seq') INTO idxZ;
-- push the list of (V,W) into the tables, with the association to the new element we put in Z
SELECT * INTO nb FROM setXYZ(idxZ,listevw);
-- update the number of elements in the list if we did not pass the right number of elements in the first place (duplicates maybe)
IF nb<>0 THEN
IF nbdansliste<>nb THEN
UPDATE Z SET nbliste=nb WHERE idx=idxZ;
END IF;
idx := idxZ;
END IF;
END IF;
END
$$ LANGUAGE plpgsql VOLATILE;

La dernière opération (insertion du binaire dans H) est un simple insert sans rien de spécial.

L'ensemble est appelé par du code java s'éxécutant dans tomcat (thread indépendant) ; lors de la réception d'un nouvel objet, le code se déroule ainsi:

_db : connexion already created
_insertZ : prepared statement for calling insertZ
_insertData : a simple prepared statement for INSERT INTO h VALUES (?, ?)

public int SQLimport() throws Exception {
int idx;
if (_db==null || !_db.isAlive()) return NODB;
_insertZ.setString(1,abc);
_insertZ.setString(2,vw);
_insertZ.setString(3,listevw);
_insertZ.setInt(4,nblistevw);

_db.setAutoCommit(false);
ResultSet rs = _is.executeQuery(_insert);
if (rs.next()) {
idx = rs.getInt(1);
rs.close();
if (idx>0)
StoreData(idx);
}
if (idx>=0) _db.commit();
else _db.rollback();
_db.setAutoCommit(true);
}

private void StoreData(int idx) throws Exception {
FileInputStream fis = null;
try {
fis = new FileInputStream(_file);
_insertData.setInt(1, idx);
_insertData.setBinaryStream(2, fis, (int)_file.length());
_is.executeUpdate(_insertBody);
}
finally {
if (fis!=null) fis.close();
}
}