Vous n'êtes pas identifié(e).
Bonjour.
Quel serait l'équivalent du bulk collect d'oracle en postgresql?
Merci
Hors ligne
Il n'y a pas.
=> Il faut faire une boucle et travailler enregistrement par enregistrement.
Marc.
Hors ligne
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.
Hors ligne
Tout simplement est-ce qu'il y a une méthode ou autre pour que le traitement aille plus vite?
Hors ligne
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/
Hors ligne
Effectivement je ne pense pas que ce soit possible 
Hors ligne
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.
Hors ligne
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';
Hors ligne
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/
Hors ligne
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.
Hors ligne
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/
Hors ligne
l'id est juste une clé primaire créée lorsque l'on insert une ligne de la table temporaire vers la table principale.
Hors ligne
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/
Hors ligne
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 )
Hors ligne
La solution proposée par rjuju sera toujours bien plus rapide qu'un curseur. Avez-vous essayé ?
Guillaume.
Hors ligne
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....?
Hors ligne
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/
Hors ligne
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.
Hors ligne
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/
Hors ligne
Merci je vais essayer sa , même si sa risque d'être compliqué
Hors ligne
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?
Hors ligne
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/
Hors ligne
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...
Hors ligne
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/
Hors ligne
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.
Hors ligne