Forums PostgreSQL.fr

vince44

Membre

Pouvoir lancer deux fonctions qui utilisent la même table

Bonjour,

Je suis actuellement sur un postGresql 9.1 (depuis peu car migration depuis mysql).
J'ai certains calculs qui s'exécutent en base (base type décisionnel), pour une base mon calcul est trop lent alors je voulais pourvoir lancer plusieurs fois la procédure. J'ai fait ce qu'il fallait, mais un problème dans ma procédure m'embête...

En gros le problème:
- au début de la procédure je mets à jour un champ dans un table pour dire que je suis en cour de traitement puis j'effectue mes calculs. -> En solo ça marche
- Lorsque je lance le deuxième il retape sur les mêmes lignes (que j'ai locké) car mon update fait au début de ma procédure n'est pas commité, il essai donc de locker les mêmes ligne...

Ma question:
- Est-il possible à l'intérieur de ma fonction de faire un 2 commits (un au début puis un autre à la fin).

Si ça peut aider voici la procedure:

CREATE OR REPLACE FUNCTION execCalc()
RETURNS VOID
AS $$
DECLARE statsLocked boolean DEFAULT false;
DECLARE statId INT DEFAULT 0;
DECLARE _tstamp TIMESTAMP;
DECLARE _tag varchar(50) DEFAULT '';
DECLARE deviceId varchar(50) DEFAULT '';
DECLARE _ctx1 varchar(300) DEFAULT '';
DECLARE _ctx2 varchar(300) DEFAULT '';
DECLARE _ctx3 varchar(300) DEFAULT '';
DECLARE _ctx4 varchar(300) DEFAULT '';
DECLARE _ctx5 varchar(300) DEFAULT '';
DECLARE first smallint DEFAULT 0;
DECLARE beginDate TIMESTAMP;
DECLARE endDate TIMESTAMP;
DECLARE curStats refcursor;
DECLARE _bloc int DEFAULT 2500;
BEGIN
    --SELECT pg_try_advisory_lock(12345) INTO statsLocked;
   
    --IF statsLocked = true THEN
   
        PERFORM 1 FROM fill_general_tables();
       
        select cast(value as integer) into _bloc from parametrage where key = 'bloc_exec';
       
        --BEGIN;
        --START TRANSACTION READ COMMITTED
       
            --LOCK TABLE stats_raw_temp IN ACCESS SHARE MODE;
           
            CREATE TEMPORARY TABLE temp_stats_raw_temp ON commit DROP AS
            SELECT * FROM stats_raw_temp WHERE "lock" = false ORDER BY stat_id ASC LIMIT (select _bloc) FOR UPDATE NOWAIT;
       
                  ------C'est ce update là que je voudrais commiter en cours de travaille.
            UPDATE stats_raw_temp SET "lock" = true WHERE stat_id IN (SELECT stat_id FROM temp_stats_raw_temp);
           
        --END;
        --COMMIT;

       
         PERFORM pg_sleep(60);

   
        --PERFORM pg_advisory_unlock(12345);
       
        PERFORM 1 FROM processvisit();

        OPEN curStats FOR SELECT "stat_id","stat_time","tag","device_id","ctx1","ctx2","ctx3","ctx4","ctx5" FROM temp_stats_raw_temp ORDER BY stat_id ASC;
        LOOP
            FETCH curStats INTO statId, _tstamp, _tag, deviceId, _ctx1, _ctx2, _ctx3, _ctx4, _ctx5;
            EXIT WHEN NOT FOUND;
            IF first = 0 THEN
                beginDate := _tstamp;
                first := 1;
            END IF;
            endDate := _tstamp;
            PERFORM 1 FROM processStats(statId, _tstamp, _tag, deviceId, _ctx1, _ctx2, _ctx3, _ctx4, _ctx5);
        end LOOP;
   
        DELETE FROM stats_raw_temp WHERE stat_id IN (SELECT stat_id FROM temp_stats_raw_temp);
        PERFORM 1 FROM processStatsEnd(beginDate, endDate);
       
        PERFORM 1 FROM calc_visit_duration_daily();
        PERFORM 1 FROM calc_visit_duration_monthly();
        PERFORM 1 FROM calc_visit_duration_yearly();   
       
    --END IF;
   
EXCEPTION WHEN TRANSACTION_ROLLBACK then
    UPDATE stats_raw_temp SET "lock" = false WHERE stat_id IN (SELECT stat_id FROM temp_stats_raw_temp);
END;
$$ LANGUAGE PLPGSQL;

gleu

Administrateur

Re : Pouvoir lancer deux fonctions qui utilisent la même table

Est-il possible à l'intérieur de ma fonction de faire un 2 commits (un au début puis un autre à la fin).

Non, ce n'est pas possible.

Qu'est-ce que vous voulez réellement faire ?

---

Guillaume.

rjuju

Administrateur

Re : Pouvoir lancer deux fonctions qui utilisent la même table

Bonjour.
Hélas, tant que la procédure stockée n'est pas finie, les modifications ne seront pas visibles à l'extérieur de la transaction.

Il y a eu une discussion qui portait un peu sur le même sujet il y a peu, des éléments de contournement ont été apportés, peut-être cela pourra vous aiguiller vers une solution ?

http://forums.postgresql.fr/viewtopic.php?id=1713

---

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

Marc Cousin

Membre

Re : Pouvoir lancer deux fonctions qui utilisent la même table

Vous pourriez vous en sortir quand même en récupérant les enregistrements sur lesquels travailler un par un avec des select for update nowait dans chaque bloc de PL (et dans ce cas là, même pas la peine de s'embêter avec un champs LOCK)

Je vous explique sans coder l'algo (la flemme , et vous avez l'air de vous en sortir déjà correctement en PL )

FOREACH pk_temp IN SELECT champs_primary_key FROM stats_raw_temp ORDER BY stat_id ASC
LOOP
  SELECT * INTO enreg_a_traiter FROM  stats_raw_temp WHERE primary_key=pk_temp NOWAIT;
  IF FOUND
    mon_compteur:=mon_compteur +1;
    INSERT INTO  temp_stats_raw_temp SELECT enreg_a_traiter.*;
  END IF
  EXIT WHEN mon_compteur > limite_max_a_verrouiller;
END LOOP

L'idée est que chaque session va verrouiller des enregistrements de la liste des enregistrements utilisables avec les SELECT FOR UPDATE unitaires, à hauteur du nombre max à verrouiller. Le verrouillage va être plus lent qu'avec la méthode précédente, mais je présume que ce n'est pas là que le traitement est lent mais dans l'appel des fonctions:

        PERFORM 1 FROM calc_visit_duration_daily();
        PERFORM 1 FROM calc_visit_duration_monthly();
        PERFORM 1 FROM calc_visit_duration_yearly();   

En espérant avoir été assez clair et que ça aide… Par ailleurs, le champ lock devient inutile, vu qu'on utilise les verrous du moteur postgres directement.

---

Marc.

vince44

Membre

Re : Pouvoir lancer deux fonctions qui utilisent la même table

Merci à tous pour vos réponses,

Marc Cousin je vais tenter ta solution.