Forums PostgreSQL.fr

srr2015

Membre

Alternative de commit, rollback dans une procédure stockée

Bonjour,

J'ai créé une fonction (procédure stockée) qui nécessite l'insertion dans une table avec plusieurs millliers d'enregistrements.
J'aimerais donc faire un commit par lot de 10000 enregistrements par exemple. Comme l'utilisation de commit et rollback n'est permise dans une fonction, existe-il une alternative pour que l'insertion soit effective après traitement de 10000 enregistrements?
Toutes suggestions sont les bienvenues.

Merci d'avance.

rjuju

Administrateur

Re : Alternative de commit, rollback dans une procédure stockée

Quel est le but d'avoir un commit par 10k enregistrements ? Si c'est une question de visibilité des données, il n'y a pas de solution simple, à part de déplacer la fonction dans un script externe. Si c'est pour minimiser les pertes en cas d'erreur, vous pouvez utiliser SAVEPOINT / ROLLBACK TO

---

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

srr2015

Membre

Re : Alternative de commit, rollback dans une procédure stockée

En fait, le problème c'est que l'exécution de la fonction va certainement prendre du temps (une journée et même plus). Donc au moins s'il y a commit, les données validées seront enregistrées dans la base.
Ci-après la fonction - qu'en pensez-vous ? La table appartement contient 104000 enregistrements ...
J'aimerais ajouter commit avant end loop mais ce n'est pas possible? comment faire ou comment optimiser cette fonction?

===============================
CREATE OR REPLACE FUNCTION generer_avis_imposition(var_exer_annee character varying, dat_avis_impos_date_limite date, int_operateur integer)
  RETURNS character varying AS
$BODY$DECLARE
    -- Declaration
BEGIN
    var_role_type  := 'IF';
   
    SELECT INTO enreg *
    FROM role
    WHERE exer_annee= var_exer_annee
    AND role_type = var_role_type
    AND role_homolo IS NULL;
   
    IF enreg.role_code IS NULL THEN
        --avis_impos_ref
        SELECT valeur_param_exer_val INTO int_role_num
        FROM valeur_param_exercice
        WHERE param_exer_key = 'SEQ_ROLE_IF' ;
       
        INSERT INTO role (exer_annee, role_type,role_num,role_article) VALUES (var_exer_annee, var_role_type,int_role_num,1)
        --recupere code apres insert
        RETURNING "role_code" INTO STRICT int_rode_code; 
       
        --mise à jour sequence
        UPDATE valeur_param_exercice
        SET valeur_param_exer_val = int_role_num + 1
        WHERE param_exer_key = 'SEQ_ROLE_IF' ;
    ELSE
        int_rode_code = enreg.role_code ;
    END IF;
   
    SELECT param_valeur INTO var_avis_impos_verificateur
    FROM param_sys
    WHERE param_key = 'P_NOM_VERIFICATEUR' ;

    FOR enreg IN (SELECT
                a.bien_code,
                parcel_adresse , appart_superf_batie , parcel_superf_non_batie ,pr.dec_decoup_terri_code as rue  ,quartier
                FROM
                appartement AS a
                INNER JOIN batiment AS ba ON ba.batim_code = a.batim_code
                INNER JOIN parcelle as pr ON pr.bien_code = ba.bien_code
                INNER JOIN proprietaire_bien AS p ON p.bien_code = a.bien_code
                INNER JOIN (SELECT A.type_decoup_code as type , A.decoup_terri_code as quartier , B.decoup_terri_code
                            FROM decoupage_territoire as A
                            INNER JOIN decoupage_territoire as B on A.decoup_terri_code = B.dec_decoup_terri_code
                            ) AS dec ON dec.decoup_terri_code = pr.dec_decoup_terri_code
                WHERE (a.appart_declare_if = 'f' OR a.appart_declare_if IS NULL)
                AND appart_superf_batie > 0)
                LOOP       
                                                                   
                SELECT INTO enreg_2 c.contri_nif , c.contri_adresse
                FROM proprietaire_bien as p
                INNER JOIN contribuable as c ON c.contri_nif = p.contri_nif
                WHERE p.bien_code = enreg.bien_code;

                t_avis_impos_adresse_contribuable := enreg_2.contri_adresse ;
                v_contri_nif := enreg_2.contri_nif ;                       

                --avis_impos_ref
                SELECT valeur_param_exer_val INTO int_valeur_param_exer_val
                FROM valeur_param_exercice
                WHERE param_exer_key = 'SEQ_AI' ;
               
                --liste contribuable
                t_avis_impos_contribuable := '' ;
                v_sep := '';
                FOR enreg_2 IN (SELECT *
                                FROM vw_contribuable
                                WHERE bien_code = enreg.bien_code ) LOOP
                   
                    t_avis_impos_contribuable :=  t_avis_impos_contribuable || v_sep || enreg_2.contri_nom_rs ;
                    v_sep := ',';
                END LOOP;
               
                v_avis_impos_ref = int_valeur_param_exer_val || '/' ||var_exer_annee ;
               
                t_avis_impos_adresse_bien     := enreg.parcel_adresse ;
                t_avis_impos_quartier         := enreg.quartier ;
               
                -- numéro article
                SELECT MAX(avis_impos_article::int) INTO int_max_avis_impos_article
                FROM avis_imposition
                WHERE role_code = int_rode_code ;
               
                IF int_max_avis_impos_article IS NULL THEN
                    int_avis_impos_article := 1 ;
                ELSE
                    int_avis_impos_article := int_max_avis_impos_article + 1 ;
                END IF;
               
                INSERT INTO avis_imposition (contri_nif,exer_annee,role_code ,avis_impos_ref , avis_impos_contribuable,avis_impos_adresse_bien , avis_impos_quartier ,avis_impos_bien ,avis_impos_adresse_contribuable , avis_impos_article ,avis_impos_date_limite ,avis_impos_verificateur ,operateur , avis_impos_genere)
                VALUES (v_contri_nif,var_exer_annee, int_rode_code , v_avis_impos_ref , t_avis_impos_contribuable , t_avis_impos_adresse_bien ,t_avis_impos_quartier , enreg.bien_code ,t_avis_impos_adresse_contribuable,int_avis_impos_article,dat_avis_impos_date_limite , var_avis_impos_verificateur , int_operateur ,'t')
                RETURNING "avis_impos_code" INTO STRICT int_avis_impos_code;
               
                --mise à jour sequence
                UPDATE valeur_param_exercice
                SET valeur_param_exer_val = int_valeur_param_exer_val + 1
                WHERE param_exer_key = 'SEQ_AI' ;
                ---Article role
               
                ---detail   
                res := regenerer_details_avis_if(int_avis_impos_code, enreg.bien_code , var_exer_annee , NULL ) ;
               
                SELECT
                    SUM( COALESCE( det_avis_if_taux_batie , 0) * COALESCE(det_avis_if_surf_batie,0) )
                    + SUM(COALESCE(det_avis_if_taux_non_batie,0) * COALESCE(det_avis_if_surf_non_batie ,0) ) INTO flo_avis_impos_mt_ppal
                FROM details_avis_if
                WHERE avis_impos_code =  int_avis_impos_code ;
               
                UPDATE avis_imposition
                SET avis_impos_mt_ppal = flo_avis_impos_mt_ppal ,
                avis_impos_reste_ppal = flo_avis_impos_mt_ppal
                WHERE avis_impos_code = int_avis_impos_code ;

                UPDATE appartement
                SET appart_declare_if = 't'
                WHERE bien_code = enreg.bien_code ;
               
                -- COMMIT;
    END LOOP;-- end appartement
   
    --mise à jour article role
    UPDATE role SET role_article = (SELECT MAX(avis_impos_article::int) FROM avis_imposition WHERE role_code = int_rode_code)  WHERE role_code = int_rode_code ;
   
    RETURN '1' ;
END;$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION regenere_avis_imposition(character varying, date, integer)
  OWNER TO postgres;
========================================

Merci.
Serge

rjuju

Administrateur

Re : Alternative de commit, rollback dans une procédure stockée

Le faire de rajouter un COMMIT intermédaire veut aussi dire qu'en cas d'erreur, vous avez la moitié du traitement qui est fait, et c'est en général délicat de revenir à un état cohérent.

Je n'ai pas regardé en détail votre procédure stockée, mais à première vue il me semble tout à fait possible de remplacer un traitement à base de curseurs par une (ou pusieurs si besoin) requêtes SQL, ce qui sera beaucoup plus performant.

---

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