Forums PostgreSQL.fr

karthus

Membre

bulk collect

Bonjour.
Quel serait l'équivalent du bulk collect d'oracle en postgresql?
Merci

Marc Cousin

Membre

Re : bulk collect

Il n'y a pas.

=> Il faut faire une boucle et travailler enregistrement par enregistrement.

---

Marc.

karthus

Membre

Re : bulk collect

C'est bien ce que je pensais.
Je vais détailler un peu.
Je dois migrer une base de donnée oracle vers postgresql ( vous l'aurez compris ) , chaque journée des données sont enregistrées dans des tables temporaires , et chaque nuit un script sql ( que je dois traduire en pl/pgsql ) met à jour ces données en les enregistrant dans les tables principales , en faisant des update/insert. Les tables temporaires contiennent des milliers de lignes (pour ne pas dire parfois quelques centaines de milliers) et donc pour optimiser le traitement de nuit ils utilisent sous oracle une structure du genre OPEN CURSOR LOOP FETCH CURSOR BULK COLLECT INTO .......  LIMIT 1000 , pour faire les traitements par blocs de 1000. La première étape était de faire une traduction "bête" en pl/pgsql traitant ligne par ligne , mais cette méthode est beaucoup trop longue , vient la 2ème étape , l'optimisation pour accélérer les traitements , et là sa bloque , surtout s'il n'y a pas d'équivalent à bulk collect...
Je suis en version 8.2.12.

karthus

Membre

Re : bulk collect

Tout simplement est-ce qu'il y a une méthode ou autre pour que le traitement aille plus vite?

rjuju

Administrateur

Re : bulk collect

Le meilleur moyen pour optimiser ce traitement serait de remplacer votre traitement séquentiel par un ensemble de grosses requêtes faisant la même chose pour l'ensemble des lignes d'un coup, mais je ne sais pas si cela est possible dans votre cas.

---

Julien.
https://rjuju.github.io/

karthus

Membre

Re : bulk collect

Effectivement je ne pense pas que ce soit possible

gleu

Administrateur

Re : bulk collect

En fait, votre demande est très peu précise. Il est peut-être possible d'optimiser votre procédure mais vous donnez trop peu de détails pour qu'on puisse fournir un renseignement quelqu'il soit.

---

Guillaume.

karthus

Membre

Re : bulk collect

Voilà à quoi ressemble globalement une des fonctions:
CREATE OR REPLACE FUNCTION DATA_LOAD_LIGNE_INSERT (status numeric, message VARCHAR) RETURNS SETOF VARCHAR AS $$

DECLARE
       C_INSERT CURSOR FOR SELECT tmp.COLUMN..... FROM ma_table_temp tmp; (avec les JOIN et WHERE qui vont bien
       
       COLUMNS numeric;
BEGIN
       OPEN C_INSERT;
    WHILE FOUND LOOP
        FETCH C_INSERT INTO COLUMNS
    IF FOUND THEN
       INSERT INTO ma_table_principale (COLUMN) VALUES COLUMNS;
   END LOOP;
   CLOSE C_INSERT;
   (quelques traitements sans grandes importances)
RETURN;
END;
$$ LANGUAGE 'plpgsql';

rjuju

Administrateur

Re : bulk collect

A première vue, cette fonction insère un (des ?) champ d'une table temporaire dans une table principale.
Un traitement séquentiel est inutile, un simple
INSERT INTO ma_table_principale (COLUMN) SELECT tmp.COLUMN..... FROM ma_table_temp tmp; (avec les JOIN et WHERE qui vont bien
fera la même chose, sauf si bien sûr vous avez simplifié le contenu de la boucle et qu'il y manque des traitements spécifiques.
La fonction retourne un SETOF varchar, sont-ils renseignés dans la boucle ou plus tard ?

Dernière modification par rjuju (10/05/2012 15:39:23)

---

Julien.
https://rjuju.github.io/

karthus

Membre

Re : bulk collect

En fait l'id de la table est composé de la date + d'un numero de séquence de 8 chiffres donc...Obligé d'utiliser cette méthode , en tout cas c'est exactement la traduction pl/pgSQL du script pl/sql qu'ils ont fait ici , je ne fais que reprendre ce qui est déjà fait.

rjuju

Administrateur

Re : bulk collect

Il faudrait plus de détail sur "l'id" de la table et le traitement effectif pour pouvoir vous aider.
L'id est le nom de la table, la clé primaire ... ?

---

Julien.
https://rjuju.github.io/

karthus

Membre

Re : bulk collect

l'id est juste une clé primaire créée lorsque l'on insert une ligne de la table temporaire vers la table principale.

rjuju

Administrateur

Re : bulk collect

Si cet id est créé via un DEFAULT avec la concaténation d'un current_date (ou équivalent) et d'un nextval(), il sera généré de la même façon qu'une seule ligne ou un ensemble de lignes est insérée.

---

Julien.
https://rjuju.github.io/

karthus

Membre

Re : bulk collect

Oui mais ce n'est pas le problème. Le problème c'est qu'il me faut 25min pour insérer 73000 lignes dans une table de 100000 ( c'est une table test qui contient une partie de la table principale qui elle contient des millions de lignes )

gleu

Administrateur

Re : bulk collect

La solution proposée par rjuju sera toujours bien plus rapide qu'un curseur. Avez-vous essayé ?

---

Guillaume.

karthus

Membre

Re : bulk collect

Oui mais je ne peux pas faire comme ça. Pour faire de la même façon qu'un bulk collect...limit 1000 est-ce que je pourrais utiliser un curseur avec des tableaux pour mes variables COLUMNS, etc....?

rjuju

Administrateur

Re : bulk collect

Vous ne donnez qu'un aperçu de votre fonction, il est donc difficile de répondre. Mais pour copier des enregistrements d'une table vers une autre, un curseur sera lent, c'est pourquoi il est préférable de faire cela en une unique requête. Et dans votre exemple rien n'empêche de procéder comme cela. Si vous devez transformer un champ sur plusieurs lignes en tableau, cela peut également se faire en requête sql avec array_agg() par exemple.

---

Julien.
https://rjuju.github.io/

karthus

Membre

Re : bulk collect

En fait le curseur contient toutes les lignes de ma table temporaire , ensuite avec le FETCH je parcours chaque ligne du cursor pour les comparer avec les lignes de ma table principale pour savoir s'il faut faire un update ou un insert , je ne vois pas comment faire cela sans curseur.

rjuju

Administrateur

Re : bulk collect

En 2 requêtes de ce genre :

INSERT INTO table_finale SELECT * FROM table_temp LEFT JOIN table_finale USING (...) WHERE table_finale.champ IS NULL;
pour insérer les lignes manquantes, puis pour la maj :
UPDATE table_finale SET champ1 = upd.champ1, champ2 = upd.champ2 FROM (SELECT table_temp.* FROM table_temp JOIN table_finale USING (...)) upd;

---

Julien.
https://rjuju.github.io/

karthus

Membre

Re : bulk collect

Merci je vais essayer sa , même si sa risque d'être compliqué

karthus

Membre

Re : bulk collect

Bon , apparemment c'est impossible. Est-ce que sa ne sera pas mieux et plus rapide d'utiliser des tableaux de taille 1000 comme le bulk collect , de comparer toutes les lignes de ce tableau avec celles de ma table principale et de tout update ou insert d'un coup?

rjuju

Administrateur

Re : bulk collect

Vous pouvez utiliser array_agg sur chacun de vos champ et parcourir le tableau.
Exemple :

do
$$
DECLARE tab int[];
i integer;
val integer;
BEGIN
	select array_agg(s) into tab from generate_series(1,150) s;
	foreach val in array tab
	loop
		raise notice 'val : %',val;
	end loop;
END;
$$

Cela dit, si vous insérez 73000 lignes en 25 minutes, cela fait 20.5 ms par insert en moyenne, ce qui n'est pas énorme. Avez-vous mesuré combien de temps prenait un insert seul pour essayer de voir combien cette méthode pourrait vous faire gagner de temps ?

Dernière modification par rjuju (11/05/2012 16:08:04)

---

Julien.
https://rjuju.github.io/

karthus

Membre

Re : bulk collect

Oui c'est à peu près sa , en moyenne 25ms pour un insert ; plus rapide pour un update je pense. Mais ma table test contient 100000 lignes , la principale 1M. Je testerai cette méthode plus tard , j'ai quelques problèmes assez contraignant car mes fonctions ne fonctionne plus , du moins je n'ai pas de réponse , même un vacuum full ne se finit pas...

rjuju

Administrateur

Re : bulk collect

Attention, un vacuum full nécessite un verrou exclusif sur la table et peut être long si la table est volumineuse. Si vos fonctions ne répondent plus, vous pouvez vérifier qu'il n'y a pas une requête qui bloquerait d'autres requêtes.

---

Julien.
https://rjuju.github.io/

karthus

Membre

Re : bulk collect

bizarrement je ne peux même pas non plus supprimer ma table de test et sa table temporaire associé o_O. Sachant que je ne les utilise pas et que je suis le seul à utiliser cette db test.