Insertion multiple et update

Gold.Strike · 11/04/2012 11:18:21

Bonjour,

Afin d'optimiser les performances d'un module de synchronisation développé en .Net, j'utilise des requêtes d'insertion multiple sur les tables contenant les plus volumineuses.

Ces requêtes sont donc de la forme suivante :

INSERT T_TITRE (TIT_CODE, TIT_LIBELLE)
       VALUES  ('M.'    , 'Monsieur',
                'Mlle.' , 'Mademoiselle'
                'Mme.'  , 'Madame')

-> Cela apporte un gain considérable en terme de performances.

Cependant, je suis bloqué dans certains cas, car la méthode de traitement va d'abord tenter une mise à jour (update), puis une insertion (insert) si la ligne n'est pas trouvée.

public void updateGroupTeam(bool isGroup, int id, int club_id)
{
	using (NpgsqlConnection c = (NpgsqlConnection)conn.getConnexion())
	{
		using (NpgsqlCommand command = new NpgsqlCommand())
		{
			c.Open();
			command.Connection = c;
			command.Parameters.Clear();
			int n = -1;
			// update, sinon insertion
			command.CommandText = " UPDATE group_team  SET  id=id WHERE team_id=:team_id AND group_id = :group_id ";
			command.Parameters.Add(new NpgsqlParameter("group_id", DbType.Int64)).Value = (long)id;
			command.Parameters.Add(new NpgsqlParameter("team_id", DbType.Int64)).Value = (long)club_id;
			try
			{
				n = command.ExecuteNonQuery();
			}
			catch (Exception e)
			{
				log.Error("updateGroupTeam() UPDATE : " + e.Message);
			}

			if (n <= 0)
			{
				// insertion
				command.CommandText = " INSERT INTO group_team( team_id, group_id) VALUES ( :team_id, :group_id);";
				command.Parameters.Clear();
				command.Parameters.Add(new NpgsqlParameter("group_id", DbType.Int64)).Value = (long)id;
				command.Parameters.Add(new NpgsqlParameter("team_id", DbType.Int64)).Value = (long)club_id;
				try
				{
					n = command.ExecuteNonQuery();
				}
				catch (Exception e)
				{
					log.Error("updateGroupTeam() INSERT : " + e.Message);
				}
			}
		}
	}
}

A partir de là, je ne vois pas comment mettre en place une insertion multiple, étant donné qu'il faudrait tester le résultat de chaque ligne individuellement...

Auriez vous une idée?
Ou y a t'il une autre méthode plus adaptée que l'insertion multiple?

Merci,

gleu · 11/04/2012 18:33:40

J'aurais plutôt tendance à tester les UPDATE pour toutes les lignes. Chaque UPDATE échoué, je conserve l'information dans un buffer. Puis je fais mes insertions à partir de toutes les données du buffer. J'essaierais d'utiliser COPY plutôt qu'INSERT à ce moment-là pour avoir encore plus de performances. Évidemment, pour que cela fonctionne, il faut que seul un utilisateur utilise ce programme à un moment donné.

Gold.Strike · 11/04/2012 19:03:34

Je ne vois justement pas comment garder l'informations des UPDATE échoués dans un buffer?

J'utilise actuellement la commande "n = command.ExecuteNonQuery();" pour exécuter l'ensemble des lignes à updater. Mais si il y a une ligne qui est déja présente, l'exception rencontrée n'exécute pas le reste des lignes...

Je ne vois pas non plus comment utiliser la commande COPY au lieu du INSERT dans mon cas? Les données insérées proviennent d'un XML récupéré chez un prestataire via un webservice.

Pour la contrainte concernant l'utilisateur, cela sera le cas. Le module de synchronsiation sera exécuté soit par un batch, soit par un administrateur. Il ne devrait pas y avoir de risques de ce coté donc.

SQLpro · 11/04/2012 20:00:12

Créez une vue, implémentez une règle pour un INSERT massif et dans votre règles faites deux requêtes :
INSERT INTO ...
...
WHERE NOT EXISTS (...)

et

UPDATE ...
...
WHERE EXISTS (...)

Au niveau client, traitez tout comme une simple insertion.

A +

gleu · 11/04/2012 23:06:47

Pour les détails sur le langage C#, je ne saurais pas vous dire. Mais c'est très simple à faire en C

Gold.Strike · 12/04/2012 09:58:51

SQLpro a écrit :

Créez une vue, implémentez une règle pour un INSERT massif et dans votre règles faites deux requêtes :
INSERT INTO ...
...
WHERE NOT EXISTS (...)
et
UPDATE ...
...
WHERE EXISTS (...)
Au niveau client, traitez tout comme une simple insertion.
A +

Aurais tu un exemple d'implémentation d'une telle méthode?
Est on obligé de passer par une vue, ou je peux utiliser simplement une procédure stockée?

gleu a écrit :

Pour les détails sur le langage C#, je ne saurais pas vous dire. Mais c'est très simple à faire en C

La aussi, si tu as un exemple, même en C, je suis preneur ;-)
Après je trouverais bien l'équivalent en C#...

Mais quand tu me disais de tester toutes les lignes d'UPDATE, c'était donc un "UPDATE multiple", permettant de retrouver les lignes ayant échouées?

Dernière modification par Gold.Strike (12/04/2012 10:02:01)

dverite · 12/04/2012 16:27:31

Quelle est la version de postgresql utilisée?

Gold.Strike · 12/04/2012 17:18:40

dverite a écrit :

Quelle est la version de postgresql utilisée?

Il s'agit de PostgreSQL 9.0

Gold.Strike · 13/04/2012 10:42:42

J'ai essayé de créer une fonction pour ma table "group_team" :

CREATE FUNCTION merge_group_team(teamid BIGINT, groupid BIGINT, roundid BIGINT) RETURNS VOID AS 
$$ 
BEGIN 
    LOOP 
        -- first try to update the row 
        UPDATE group_team SET id = id 
        WHERE team_id = teamid AND group_id = groupid AND round_id = roundid; 
        IF found THEN 
            RETURN; 
        END IF; 
        -- not there, so try to insert the key 
        -- if someone else inserts the same key concurrently, 
        -- we could get a unique-key failure 
        BEGIN 
            INSERT INTO group_team(team_id,group_id,round_id) VALUES (teamid, groupid, roundid); 
            RETURN; 
        EXCEPTION WHEN unique_violation THEN 
            -- do nothing, and loop to try the UPDATE again 
        END; 
    END LOOP; 
END; 
$$ 
LANGUAGE plpgsql;

Cette fonction marche bien pour l'ajout d'une ligne.
Mais comment l'utiliser/l'adapter pour que je puisse faire des insertions multiples?

Merci,

gleu · 13/04/2012 11:22:25

Je ne vois pas comment cette fonction pourrait bien marcher avec un "SET id=id" dans l'UPDATE.

Gold.Strike · 13/04/2012 13:44:10

gleu a écrit :

Je ne vois pas comment cette fonction pourrait bien marcher avec un "SET id=id" dans l'UPDATE.

L'intérêt est sans doute limité, du moins pour cette table là. J'ai uniquement adapté la méthode existant dans l'application, qui traite un enregsitrement à la fois, en tentant d'abord un UPDATE, puis un INSERT. L'UPDATE évite surtout de réaliser un INSERT vu qu'il ne mets rien à jour...

public void updateGroupTeam(bool isGroup, int id, int club_id)
{
	using (NpgsqlConnection c = (NpgsqlConnection)conn.getConnexion())
	{
		using (NpgsqlCommand command = new NpgsqlCommand())
		{
			c.Open();
			command.Connection = c;
			command.Parameters.Clear();
			int n = -1;
			// update, sinon insertion
			command.CommandText = " UPDATE group_team  SET  id=id WHERE team_id=:team_id AND group_id = :group_id ";
			command.Parameters.Add(new NpgsqlParameter("group_id", DbType.Int64)).Value = (long)id;
			command.Parameters.Add(new NpgsqlParameter("team_id", DbType.Int64)).Value = (long)club_id;
			try
			{
				n = command.ExecuteNonQuery();
			}
			catch (Exception e)
			{
				log.Error("updateGroupTeam() UPDATE : " + e.Message);
			}

			if (n <= 0)
			{
				// insertion
				command.CommandText = " INSERT INTO group_team( team_id, group_id) VALUES ( :team_id, :group_id);";
				command.Parameters.Clear();
				command.Parameters.Add(new NpgsqlParameter("group_id", DbType.Int64)).Value = (long)id;
				command.Parameters.Add(new NpgsqlParameter("team_id", DbType.Int64)).Value = (long)club_id;
				try
				{
					n = command.ExecuteNonQuery();
				}
				catch (Exception e)
				{
					log.Error("updateGroupTeam() INSERT : " + e.Message);
				}
			}
		}
	}
}

Maintenant pour les autres tables traitées, l'intérèt est réel, car il on y fait bien des mises à jour :

command.CommandText = " UPDATE \"statistics\" "
					 + "    SET competition_id=:competition_id, competition_name=:competition_name,  "
					 + "        season_name=:season_name, goals=:goals, penalty_goals=:penalty_goals, appearances=:appearances, yellow_cards=:yellow_cards, "
					 + "        second_yellow_cards=:second_yellow_cards, red_cards=:red_cards, substitute_in=:substitute_in, substitute_out=:substitute_out,  "
					 + "        subs_on_bench=:subs_on_bench, minutes_played=:minutes_played, shirtnumber=:shirtnumber "
					 + "   WHERE membership_id = :membership_id AND season_id = :season_id; "

Je cherche donc à conerver l'appel unique pour réalier un UPDATE/INSERT tout en permettant de faire des traitements par lots...

gleu · 14/04/2012 09:09:26

Avec un appel unique à UPDATE, il est impossible de savoir ce qui n'a pas été modifié. À la rigueur, il est possible de connaître l'ensemble des lignes modifiées en utilisant la clause UNIQUE, mais pas celles qui ne l'a pas été.

Donc il faut faire un UPDATE à la fois et chaque UPDATE qui ne passe pas doit passer par un INSERT.

Une autre solution serait de faire tout d'abord un SELECT pour connaître les éléments disponibles, faire un INSERT massif de ces éléments, puis un UPDATE massif des autres.

dverite · 14/04/2012 15:17:57

Le MERGE en masse est faisable par un UPDATE multiple + INSERT multiple pour toute une collection de valeurs, dans une fonction procédurale, les paramètres des requêtes étant passés dans des tableaux.

Voici un exemple pour des données de type clef/valeur dans une table a(id int primary key, val int).
Les valeurs à insérer ou remplacer sont passées dans arrval et les clefs dans arrkey (ces tableaux doivent impérativement être de taille identique pour que les unnest() des sous-requêtes sortent les valeurs alignées côte à côte).

CREATE FUNCTION multi_upsert(arrval int[], arrkey int[]) returns void as $$
begin
  UPDATE a SET val=s.v FROM
    (SELECT unnest(arrval) as k,unnest(arrkey) as v) s
    WHERE id=s.k;

  INSERT INTO a(id,val) select k,v
    from (SELECT unnest(arrval) as k,unnest(arrkey) as v) s
      WHERE not exists(select 1 from a where id=s.k);

end
$$ language plpgsql;

Cette fonction ne gère pas le problème les mises à jour concurrentes, mais le code C# montré ne le faisant pas non plus, ce n'est apparemment pas nécessaire. Sinon on pourrait ajouter un réessai sur EXCEPTION comme dans la doc de postgres ou un verrouillage explicite avant l'INSERT.

Côté performance, le gain à espérer est important par rapport à la séquence INSERT-UPDATE dans le code client, du fait non seulement du groupement en deux requêtes, mais surtout de la réduction au minimum des aller-retours client-serveur sur le réseau, surtout si c'est un vrai réseau (pas localhost).

Dernière modification par dverite (14/04/2012 15:18:15)

Gold.Strike · 18/04/2012 11:00:59

dverite a écrit :

Le MERGE en masse est faisable par un UPDATE multiple + INSERT multiple pour toute une collection de valeurs, dans une fonction procédurale, les paramètres des requêtes étant passés dans des tableaux.
Voici un exemple pour des données de type clef/valeur dans une table a(id int primary key, val int).
Les valeurs à insérer ou remplacer sont passées dans arrval et les clefs dans arrkey (ces tableaux doivent impérativement être de taille identique pour que les unnest() des sous-requêtes sortent les valeurs alignées côte à côte).
CREATE FUNCTION multi_upsert(arrval int[], arrkey int[]) returns void as $$
begin
  UPDATE a SET val=s.v FROM
    (SELECT unnest(arrval) as k,unnest(arrkey) as v) s
    WHERE id=s.k;

  INSERT INTO a(id,val) select k,v
    from (SELECT unnest(arrval) as k,unnest(arrkey) as v) s
      WHERE not exists(select 1 from a where id=s.k);

end
$$ language plpgsql;
Cette fonction ne gère pas le problème les mises à jour concurrentes, mais le code C# montré ne le faisant pas non plus, ce n'est apparemment pas nécessaire. Sinon on pourrait ajouter un réessai sur EXCEPTION comme dans la doc de postgres ou un verrouillage explicite avant l'INSERT.
Côté performance, le gain à espérer est important par rapport à la séquence INSERT-UPDATE dans le code client, du fait non seulement du groupement en deux requêtes, mais surtout de la réduction au minimum des aller-retours client-serveur sur le réseau, surtout si c'est un vrai réseau (pas localhost).

Cette méthode a effectivement l'air très intéressante.
Il faut que je trouve maintenant comment remplir ce tableau : suffit il de passer un tableau d'entier en C# comme paramètre pour qu'il soit traité dans la fonction plsql?
Enfin, comment puis je relever toutes les exceptions rencontrés, afin de les faire remonter pour les loguer depuis mon application?

Alan2007 · 18/04/2012 11:05:14

Je voudrais savoir merci! Reconnaissant!

dverite · 18/04/2012 18:21:47

Gold.Strike a écrit :

Il faut que je trouve maintenant comment remplir ce tableau : suffit il de passer un tableau d'entier en C# comme paramètre pour qu'il soit traité dans la fonction plsql?

Il ya 2 options: soit arranger les valeurs directement dans la requête sous forme de tableau, exemple

select multi_upsert(array[7,3,-3,2], array[1,2,3,4]);

soit utiliser le passage en paramètre de tableaux C# avec Npgsql, puisque c'est visiblement géré d'après la doc:
http://npgsql.projects.postgresql.org/d … anual.html
(chapitre "Working with arrays")

Enfin, comment puis je relever toutes les exceptions rencontrés, afin de les faire remonter pour les loguer depuis mon application?

A la première exception, la transaction va être annulée et l'erreur remontée à l'appelant, c'est le comportement par défaut.

Gold.Strike · 24/04/2012 18:24:23

Je relance un peu ce topic, car je n'ai toujours pas réussi à obtenir le comportement attendu...

J'ai mis à jour ma fonction plpgsql afin de pouvoir relever les éventuelles exceptions rencontrés : je les insère actuellement dans une table "synchronisation_errors_logs".

-- Function: merge_group_team(bigint, bigint, bigint)
-- DROP FUNCTION merge_group_team(bigint, bigint, bigint);
CREATE OR REPLACE FUNCTION merge_group_team(teamid bigint, groupid bigint, roundid bigint)
  RETURNS integer AS
$BODY$ 
DECLARE
	curtime timestamp;
	table_name text;
	error text;
	request text;
	
BEGIN 
	UPDATE group_team SET id = id 
	WHERE team_id = teamid AND group_id = groupid AND round_id = roundid; 
	IF found THEN 
	    RETURN 1; 
	END IF; 
	INSERT INTO group_team(team_id,group_id,round_id) VALUES (teamid, groupid, roundid); 
	RETURN 1; 
	EXCEPTION WHEN OTHERS THEN
		curtime := 'now';
		table_name := 'group_team';
		error := cast(sqlstate as varchar);
		request := 'team_id =' || cast(teamid as varchar) || ', group_id =' || cast(groupid as varchar) || ', round_id =' || cast(roundid as varchar);
		INSERT INTO synchronisation_errors_logs("date", error, table_name, request) 
		VALUES(curtime, error, table_name, request);
		RETURN 0;
		
END; 
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION merge_group_team(bigint, bigint, bigint) OWNER TO postgres;

Je parviens donc à loguer les différentes lignes qui ne peuvent être insérées ou mises à jour. Cependant, si je parivens à récupéré le code de l'erreur avec "sqlstate", le message lié avec "sqlerrm" ne me retourne rien. Peut être est ce normal pour l'erreur 23505?

Au niveau C#, ma procédure est donc appelé via un DataAdapter, auquel on a bindé le DataTable contenant les valeurs à traiter.

public void updateGroupTeamProcedure(DataTable dtGroupTeam)
{
	log.Debug("updateGroupTeamProcedure() => Entree");
	using (NpgsqlConnection c = (NpgsqlConnection)conn.getConnexion())
	{
		using (NpgsqlCommand command = new NpgsqlCommand("merge_group_team", c))
		{
			command.CommandType = CommandType.StoredProcedure;
			command.UpdatedRowSource = UpdateRowSource.None;

			command.Parameters.Clear();
			command.Parameters.Add("@teamid", NpgsqlTypes.NpgsqlDbType.Bigint, 4, dtGroupTeam.Columns[0].ColumnName);
			command.Parameters.Add("@groupid", NpgsqlTypes.NpgsqlDbType.Bigint, 4, dtGroupTeam.Columns[1].ColumnName);
			command.Parameters.Add("@roundid", NpgsqlTypes.NpgsqlDbType.Bigint, 4, dtGroupTeam.Columns[2].ColumnName);

			NpgsqlDataAdapter adpt = new NpgsqlDataAdapter();
			adpt.InsertCommand = command;
			// Specify the number of records to be Inserted/Updated in one go. Default is 1.
			//adpt.UpdateBatchSize = 50;
			
			c.Open();
			try
			{
				int recordsInserted = adpt.Update(dtGroupTeam); 
			}
			catch (Exception e)
			{
				log.Error("Erreur : " + e.Message);
			}
		}
	}
	log.Debug("updateGroupTeamProcedure() => Sortie");
}

Cela ne me permets pas de savoir si il y a eu des erreurs sur une des lignes traitées, car je le recordsInsert me retourne systématiquement le nombre de lignes du DataTable : n'y aurait il pas moyen de "compter" uniquement les lignes ayant été mises à jour?

Je cherche donc à convertir la fonction plpgsql afin qu'elle prenne en paramètre les tableaux C#, afin de pouvoir réaliser la boucle directement dans cette fonction, et de pouvoir tenir un compteur...
Mais est ce bien de cette façon qu'il faut la structurer?

CREATE OR REPLACE FUNCTION merge_group_team_array(teamid bigint[], groupid bigint[], roundid bigint[])
  RETURNS integer AS

Merci,

Dernière modification par Gold.Strike (24/04/2012 18:26:06)

Marc Cousin · 25/04/2012 15:38:57

Pour le premier point avec sqlerrm, je ne vois pas… ici ça marche très bien:

CREATE OR REPLACE FUNCTION public.merge_group_team(teamid bigint, groupid bigint, roundid bigint)
 RETURNS integer
 LANGUAGE plpgsql
AS $function$ 
DECLARE
curtime timestamp;
table_name text;
error text;
request text;

BEGIN 
UPDATE group_team SET id = id 
WHERE team_id = teamid AND group_id = groupid AND round_id = roundid; 
IF found THEN 
    RETURN 1; 
END IF; 
INSERT INTO group_team(team_id,group_id,round_id) VALUES (teamid, groupid, roundid); 
RETURN 1; 
EXCEPTION WHEN OTHERS THEN
curtime := 'now';
table_name := 'group_team';
error := cast(sqlstate as varchar) || ' ' || sqlerrm;
request := 'team_id =' || cast(teamid as varchar) || ', group_id =' || cast(groupid as varchar) || ', round_id =' || cast(roundid as varchar);
INSERT INTO synchronisation_errors_logs("date", error, table_name, request) 
VALUES(curtime, error, table_name, request);
RETURN 0;

END; 
$function$

Me retourne bien

 2012-04-25 15:30:43.125885 | 23505 la valeur d'une clé dupliquée rompt la contrainte unique « group_team_team_id_group_id_round_id_idx » | group_team | team_id =2, group_id =1, round_id =1

Pour le C#, aucune idée, je ne programme pas en C#

Pour ce qui est du comptage, je ne comprend pas, c'est à peu près ce que fait déjà la fonction, elle retourne un compteur d'enregistrements traités. On Si on veut différencier les insérés des mis à jour, on peut lui faire retourner un type composite qui contienne les deux chiffres. Ou bien une valeur différent quand c'est insert ou update, vu qu'elle ne traite qu'un seul enregistrement.

Sinon, oui, on peut faire une fonction qui traite un tableau. Par contre, Là c'est 3 tableaux indépendants, ce qui me semble assez peu «naturel» en terme de programmation. J'aurais plutôt fait un tableau d'un type composite… quelque chose comme:

CREATE TYPE n_uplet_group_team as (teamid bigint, groupid bigint, roundid bigint);
CREATE OR REPLACE FUNCTION merge_group_team_array(tableau_id n_uplet_group_team[])
  RETURNS integer AS

Ça fera certainement une itération plus élégante dans la fonction, en plus que de vérifier qu'on a le même nombre des 3 attributs

Gold.Strike · 25/04/2012 17:42:41

Marc Cousin a écrit :

Pour le premier point avec sqlerrm, je ne vois pas… ici ça marche très bien:

CREATE OR REPLACE FUNCTION public.merge_group_team(teamid bigint, groupid bigint, roundid bigint)
 RETURNS integer
 LANGUAGE plpgsql
AS $function$ 
DECLARE
curtime timestamp;
table_name text;
error text;
request text;

BEGIN 
UPDATE group_team SET id = id 
WHERE team_id = teamid AND group_id = groupid AND round_id = roundid; 
IF found THEN 
    RETURN 1; 
END IF; 
INSERT INTO group_team(team_id,group_id,round_id) VALUES (teamid, groupid, roundid); 
RETURN 1; 
EXCEPTION WHEN OTHERS THEN
curtime := 'now';
table_name := 'group_team';
error := cast(sqlstate as varchar) || ' ' || sqlerrm;
request := 'team_id =' || cast(teamid as varchar) || ', group_id =' || cast(groupid as varchar) || ', round_id =' || cast(roundid as varchar);
INSERT INTO synchronisation_errors_logs("date", error, table_name, request) 
VALUES(curtime, error, table_name, request);
RETURN 0;

END; 
$function$

Me retourne bien

 2012-04-25 15:30:43.125885 | 23505 la valeur d'une clé dupliquée rompt la contrainte unique « group_team_team_id_group_id_round_id_idx » | group_team | team_id =2, group_id =1, round_id =1

Alors j'ai retesté, et ca ne marche toujours pas chez moi...
J'ai déclaré la colonne "error" de la table "synchronisation_errors_logs" de type "text", mais j'ai systématiquement en résultat une chaine vide quand j'essaie de "loguer" sqlerrm : je perds même le code de sqlstate...

Exemple :
si je mets :

error := cast(sqlstate as varchar) || ' ' || sqlerrm;

=> j'obtiens : 1739;"2012-04-25 17:13:44.437";"''";"competition_id =533, name =Arab Champions League";"competition"
si je mets

error := cast(sqlstate as varchar);

=> j'obtiens : 17391740;"2012-04-25 17:20:07.093";"42702";"competition_id =533, name =Arab Champions League";"competition"
Y aurait il un paramètre du serveur qui pourrait expliquer ce comportement?

Marc Cousin a écrit :

Pour le C#, aucune idée, je ne programme pas en C#
Pour ce qui est du comptage, je ne comprend pas, c'est à peu près ce que fait déjà la fonction, elle retourne un compteur d'enregistrements traités. On Si on veut différencier les insérés des mis à jour, on peut lui faire retourner un type composite qui contienne les deux chiffres. Ou bien une valeur différent quand c'est insert ou update, vu qu'elle ne traite qu'un seul enregistrement.
Sinon, oui, on peut faire une fonction qui traite un tableau. Par contre, Là c'est 3 tableaux indépendants, ce qui me semble assez peu «naturel» en terme de programmation. J'aurais plutôt fait un tableau d'un type composite… quelque chose comme:
CREATE TYPE n_uplet_group_team as (teamid bigint, groupid bigint, roundid bigint);
CREATE OR REPLACE FUNCTION merge_group_team_array(tableau_id n_uplet_group_team[])
  RETURNS integer AS
Ça fera certainement une itération plus élégante dans la fonction, en plus que de vérifier qu'on a le même nombre des 3 attributs

Effectivement ta fonction a l'air plus propre que ce qu'on fait actuellement...
En fait on souhaiterait pouvoir compter le nombre de lignes traitées qui ont été mises à jour ou insérées. Les lignes ayant soulevées une exception ne doivent elle pas être comptabilisées...
Or au retour de mon appel C#, je récupère systématiquement le nombre de lignes du tableau passé en passé en paramètre, même si certaines de ces lignes n'ont pas été mises à jour/insérées.

Object result = command.ExecuteScalar();

Nous avons du coup décomposé le traitement en 2 procédures stockées :
- la première, "merge", qui traite un enregistrment à la fois, et qui retourne 1 si l'insert/update s'est bien passé, ou 0 si une exception est rencontrée

-- Function: merge_group_team(bigint, bigint, bigint)
-- DROP FUNCTION merge_group_team(bigint, bigint, bigint);
CREATE OR REPLACE FUNCTION merge_group_team(teamid bigint, groupid bigint, roundid bigint)
  RETURNS integer AS
$BODY$ 
DECLARE
	curtime timestamp;
	table_name text;
	error text;
	request text;
BEGIN 
	-- on essaie d'abord de faire un UPDATE
 	UPDATE group_team 
 	SET id = id 
 	WHERE team_id = teamid AND group_id = groupid AND round_id = roundid; 
	IF found THEN 
	    RETURN 1; 
	END IF; 
	-- si l'enregistrment n'est pas trouvé, on fais un INSERT
	INSERT INTO group_team(team_id,group_id,round_id) 
	VALUES (teamid, groupid, roundid); 
	RETURN 1; 
	-- gestion des exceptions
	EXCEPTION WHEN OTHERS THEN
		curtime := 'now';
		table_name := 'group_team';
		error := cast(sqlstate as varchar);
		request := 'team_id =' || cast(teamid as varchar) || ', group_id =' || cast(groupid as varchar) || ', round_id =' || cast(roundid as varchar);
		INSERT INTO synchronisation_errors_logs("date", error, table_name, request) 
		VALUES(curtime, error, table_name, request);
		RETURN 0;
		
END; 
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION merge_group_team(bigint, bigint, bigint) OWNER TO postgres;

- la seconde, "loop", qui fait un parcours de tables, et qui appelle pour chaque enregistrment la procédure "merge" en mettant à jour un compteur

-- Function: merge_group_team_loop(bigint[], bigint[], bigint[])
-- DROP FUNCTION merge_group_team_loop(bigint[], bigint[], bigint[]);
CREATE OR REPLACE FUNCTION merge_group_team_loop(teamid bigint[], groupid bigint[], roundid bigint[])
  RETURNS integer AS
$BODY$ 
DECLARE	
	my_teamid bigint;
	my_groupid bigint;
	my_roundid bigint;
	compteur int;
	retour int;
BEGIN 
	-- initalisation du compteur
	compteur := 0;
	-- parcours de la liste
	FOR i IN 1..array_upper(teamid,1) LOOP
		-- récupération des valeurs des listes
		my_teamid := teamid[i];
		my_groupid := groupid[i];
		my_roundid := roundid[i];
		-- appel à la fonction de merge
		retour := merge_group_team(my_teamid, my_groupid, my_roundid);
		-- MAJ du compteur
		compteur := compteur + retour;	
	END LOOP; 
	-- retour du nombre d'enregistrements mis à jour
	RETURN compteur;
END; 
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION merge_group_team_loop(bigint[], bigint[], bigint[]) OWNER TO postgres;

Alors comme tu le signales, notre méthode n'est pas très propre, car nous créons un tableau par paramètre! Mais je ne vois pas trop comment créer de tableau composite, ni comment il serait déclarer dans le code C#.

Dernière modification par Gold.Strike (25/04/2012 17:46:32)

Marc Cousin · 26/04/2012 16:37:43

C'est étrange, cette histoire de sqlerrm qui disparaît. Tu peux me donner ta version exacte ?

Gold.Strike · 27/04/2012 17:58:19

Marc Cousin a écrit :

C'est étrange, cette histoire de sqlerrm qui disparaît. Tu peux me donner ta version exacte ?

Il s'agit de la version de PosgreSQL 9.0...

Je viens également de remarquer qu'on a un problème aux niveaux de nos paramètres : il arrive que certains éléments de la liste soient nuls (date non renseignée, chaine non renseignée, etc...)
Cela provoque une erreur au niveau du code C# : System.NullReferenceException.

Pour faire des tests, j'ai construit mes listes à la main contenant des données nulles :

// Création des listes
List<long> l_round_id = new List<long>();
List<long>  l_season_d = new List<long>();
List<DateTime?> l_end_date = new List<DateTime?>();
List<String> l_groups = new List<string>();
List<DateTime?> l_last_updated = new List<DateTime?>();
List<String> l_name = new List<string>();
List<String> l_ordermethod = new List<string>();
List<DateTime?> l_start_date = new List<DateTime?>();
List<String> l_type = new List<string>();
List<String> l_has_outgroup_matches = new List<string>();

// Remplissage des listes
l_round_id.Add(6786);
l_round_id.Add(6787);
l_round_id.Add(6788);
l_season_d.Add(115);
l_season_d.Add(115);
l_season_d.Add(115);
l_end_date.Add(null);
l_end_date.Add(Utils.parseDatetimeNullable("27/07/2009"));
l_end_date.Add(Utils.parseDatetimeNullable("27/04/2012"));
l_groups.Add("0");
l_groups.Add("1");
l_groups.Add("2");
l_last_updated.Add(Utils.parseDatetimeNullable("27/07/2009"));
l_last_updated.Add(Utils.parseDatetimeNullable("27/07/2009"));
l_last_updated.Add(Utils.parseDatetimeNullable("27/04/2012"));
l_name.Add(null);
l_name.Add("Round 2");
l_name.Add("Round 3");
l_ordermethod.Add(null);
l_ordermethod.Add("Order");
l_ordermethod.Add("Order");
l_start_date.Add(Utils.parseDatetimeNullable("27/07/2009"));
l_start_date.Add(Utils.parseDatetimeNullable("27/07/2009"));
l_start_date.Add(Utils.parseDatetimeNullable("27/04/2012"));
l_type.Add("Table");
l_type.Add("Cup");
l_type.Add("Cup");
l_has_outgroup_matches.Add("yes");
l_has_outgroup_matches.Add("no");
l_has_outgroup_matches.Add("no");

J'appelle ensuite ma procédure stockée en passant en paramètre ces différentes listes :

int nbMaj = 0;
using (NpgsqlConnection c = (NpgsqlConnection)conn.getConnexion())
{
	using (NpgsqlCommand command = new NpgsqlCommand("merge_round_loop", c))
	{
		command.CommandType = CommandType.StoredProcedure;
		command.UpdatedRowSource = UpdateRowSource.None;
		// Affectation des paramètres
		command.Parameters.Clear();
		command.Parameters.Add("l_round_id", NpgsqlTypes.NpgsqlDbType.Array | NpgsqlTypes.NpgsqlDbType.Integer);
		command.Parameters.Add("l_season_d", NpgsqlTypes.NpgsqlDbType.Array | NpgsqlTypes.NpgsqlDbType.Integer);
		command.Parameters.Add("l_end_date", NpgsqlTypes.NpgsqlDbType.Array | NpgsqlTypes.NpgsqlDbType.Date);
		command.Parameters.Add("l_groups", NpgsqlTypes.NpgsqlDbType.Array | NpgsqlTypes.NpgsqlDbType.Text);
		command.Parameters.Add("l_last_updated", NpgsqlTypes.NpgsqlDbType.Array | NpgsqlTypes.NpgsqlDbType.Timestamp);
		command.Parameters.Add("l_name", NpgsqlTypes.NpgsqlDbType.Array | NpgsqlTypes.NpgsqlDbType.Text);
		command.Parameters.Add("l_ordermethod", NpgsqlTypes.NpgsqlDbType.Array | NpgsqlTypes.NpgsqlDbType.Text);
		command.Parameters.Add("l_start_date", NpgsqlTypes.NpgsqlDbType.Array | NpgsqlTypes.NpgsqlDbType.Date);
		command.Parameters.Add("l_type", NpgsqlTypes.NpgsqlDbType.Array | NpgsqlTypes.NpgsqlDbType.Text);
		command.Parameters.Add("l_has_outgroup_matches", NpgsqlTypes.NpgsqlDbType.Array | NpgsqlTypes.NpgsqlDbType.Text);
		command.Parameters[0].Value = l_round_id;
		command.Parameters[1].Value = l_season_id;
		command.Parameters[2].Value = l_end_date;
		command.Parameters[3].Value = l_groups;
		command.Parameters[4].Value = l_last_updated;
		command.Parameters[5].Value = l_name;
		command.Parameters[6].Value = l_ordermethod;
		command.Parameters[7].Value = l_start_date;
		command.Parameters[8].Value = l_type;
		command.Parameters[9].Value = l_has_outgroup_matches;
		try{
			// Ouverture de la connexion et exécution
			c.Open();
			Object result = command.ExecuteScalar();
			//nbMaj = (int)command.ExecuteScalar();
		}
		catch(Exception e)
		{
		}
	}
}
if (nbMaj < l_round_id.Count)
	log.Error("updateRoundProcedureArray() - Toutes les lignes n'ont pas été insérées/updatées : " + nbMaj.ToString() + "/" + l_round_id.Count.ToString());

Ma procédure stockée "Loop" parcourt ces listes pour faire la mise à jour via la procédure stockée :

-- Function: merge_round_loop(integer[], integer[], date[], text[], timestamp without time zone[], text[], text[], date[], text[], text[])
-- DROP FUNCTION merge_round_loop(integer[], integer[], date[], text[], timestamp without time zone[], text[], text[], date[], text[], text[]);
CREATE OR REPLACE FUNCTION merge_round_loop(round_id integer[], season_id integer[], end_date date[], groups text[], last_updated timestamp without time zone[], "name" text[], ordermethod text[], start_date date[], "type" text[], has_outgroup_matches text[])
  RETURNS text AS
$BODY$ 
DECLARE	
	-- valeurs à insérer
	my_round_id integer;
	my_season_id integer;
	my_end_date date;
	my_groups text;
	my_last_updated timestamp without time zone;
	my_name text;
	my_ordermethod text;
	my_start_date date;
	my_type text;
	my_has_outgroup_matches text;
	-- compteur et retour d'appel à la fonction
	compteur integer;
	retour integer;
	retour_text varchar;
	-- exception
	curtime timestamp;
	table_name text;
	error text;
	request text;
BEGIN 
	-- initalisation du compteur
	compteur := 0;
	retour_text := '';
	
	-- parcours de la liste
	FOR i IN 1..array_upper(round_id,1) LOOP
	
		-- récupération des valeurs des listes
 		my_round_id := round_id[i];
		my_season_id := season_id[i];
		my_end_date := end_date[i];
		my_groups := groups[i];
		my_last_updated := last_updated[i];
		my_name := name[i];	
		my_ordermethod := ordermethod[i];
		my_start_date := start_date[i];
		my_type = type[i];
		my_has_outgroup_matches := has_outgroup_matches[i];
		
		-- appel à la fonction de merge
		retour := merge_round(my_round_id, my_season_id, my_end_date, my_groups, my_last_updated, my_name, my_ordermethod, my_start_date, my_type, my_has_outgroup_matches);
		
		-- MAJ du compteur
		compteur := compteur + retour;

	END LOOP; 

	-- retour du nombre d'enregistrements mis à jour
	RETURN compteur;

END; 
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION merge_round_loop(integer[], integer[], date[], text[], timestamp without time zone[], text[], text[], date[], text[], text[]) OWNER TO postgres;

Et c'est donc là que mon application me retourne l'exception :

L'exception System.NullReferenceException s'est produite
  Message=La référence d'objet n'est pas définie à une instance d'un objet.
  Source=Npgsql
  StackTrace:
       à NpgsqlTypes.NpgsqlTypesHelper.DefinedType(Object item)
       à NpgsqlTypes.ArrayNativeToBackendTypeConverter.WriteItem(NpgsqlNativeTypeInfo TypeInfo, Object item, StringBuilder sb)
       à NpgsqlTypes.ArrayNativeToBackendTypeConverter.WriteEnumeration(NpgsqlNativeTypeInfo TypeInfo, IEnumerable col, StringBuilder sb)
       à NpgsqlTypes.ArrayNativeToBackendTypeConverter.WriteItem(NpgsqlNativeTypeInfo TypeInfo, Object item, StringBuilder sb)
       à NpgsqlTypes.ArrayNativeToBackendTypeConverter.FromArray(NpgsqlNativeTypeInfo TypeInfo, Object NativeData)
       à NpgsqlTypes.NpgsqlNativeTypeInfo.ConvertToBackendPlainQuery(Object NativeData)
       à NpgsqlTypes.NpgsqlNativeTypeInfo.ConvertToBackend(Object NativeData, Boolean ForExtendedQuery)
       à Npgsql.NpgsqlCommand.GetClearCommandText()
       à Npgsql.NpgsqlCommand.GetCommandText()
       à Npgsql.NpgsqlQuery.WriteToStream(Stream outputStream)
       à Npgsql.NpgsqlReadyState.QueryEnum(NpgsqlConnector context, NpgsqlCommand command)
       à Npgsql.NpgsqlConnector.QueryEnum(NpgsqlCommand queryCommand)
       à Npgsql.NpgsqlCommand.GetReader(CommandBehavior cb)
       à Npgsql.NpgsqlCommand.ExecuteScalar()
       à ptrs.dal.implementations.postgresql8.DalImportGsm.updateRoundProcedureArray(List`1 l_round_id, List`1 l_season_id, List`1 l_end_date, List`1 l_groups, List`1 l_last_updated, List`1 l_name, List`1 l_ordermethod, List`1 l_start_date, List`1 l_type, List`1 l_has_outgroup_matches) dans C:\projet\PTRS\DataAccessLayer\implementations\postgresql8\DalImportGsm.cs:ligne 2017
  InnerException:

Quand j'appelle la même fonction depuis Postgre, en passant des paramètres nuls, je n'ai cependant pas de soucis :

select merge_round_loop(
	array[1,6787,53], 
	array[2,2763,53], 
	array[cast('01/07/2009' as date),null,cast('27/04/2012' as date)], 
	array['0',null,'3'], 
	array[cast('26/04/2012' as date),null,cast('27/04/2012' as date)], 
	array['Saison régulière','Round 2','Round 3'], 
	array[null,'Order 2','Order 3'], 
	array[cast('02/06/2008' as date),null,cast('27/04/2012' as date)], 
	array['Table',null,'Table'], 
	array['no',null,'no']);

Les 3 lignes s'insèrent bien, avec les colonnes contenant des valeurs nulles...

Auriez vous une explciation?
Existe t'il un moyen de gérer des paramètres "nullables"?

Merci d'avance,

Dernière modification par Gold.Strike (27/04/2012 18:00:23)

Gold.Strike · 30/04/2012 09:55:29

Après une mise à jour de la DLL "Npgsqll" les éléments nuls sont gérés correctement.

Il me reste donc 2 problématiques à gérer :
- le fait que "sqlerrm" ne me retourne rien dans les fonctions plpgsql car le seul code erreur SQL n'est pas vraiment suffisant pour identifier un problème
- le fait de passer 1 array par paramètre, avec le risque d'avoir des arrays de tailles différentes

Forums PostgreSQL.fr

#1 11/04/2012 11:18:21

Insertion multiple et update

#2 11/04/2012 18:33:40

Re : Insertion multiple et update

#3 11/04/2012 19:03:34

Re : Insertion multiple et update

#4 11/04/2012 20:00:12

Re : Insertion multiple et update

#5 11/04/2012 23:06:47

Re : Insertion multiple et update

#6 12/04/2012 09:58:51

Re : Insertion multiple et update

#7 12/04/2012 16:27:31

Re : Insertion multiple et update

#8 12/04/2012 17:18:40

Re : Insertion multiple et update

#9 13/04/2012 10:42:42

Re : Insertion multiple et update

#10 13/04/2012 11:22:25

Re : Insertion multiple et update

#11 13/04/2012 13:44:10

Re : Insertion multiple et update

#12 14/04/2012 09:09:26

Re : Insertion multiple et update

#13 14/04/2012 15:17:57

Re : Insertion multiple et update

#14 18/04/2012 11:00:59

Re : Insertion multiple et update

#15 18/04/2012 11:05:14

Re : Insertion multiple et update

#16 18/04/2012 18:21:47

Re : Insertion multiple et update

#17 24/04/2012 18:24:23

Re : Insertion multiple et update

#18 25/04/2012 15:38:57

Re : Insertion multiple et update

#19 25/04/2012 17:42:41

Re : Insertion multiple et update

#20 26/04/2012 16:37:43

Re : Insertion multiple et update

#21 27/04/2012 17:58:19

Re : Insertion multiple et update

#22 30/04/2012 09:55:29

Re : Insertion multiple et update

Pied de page des forums