PostgreSQL La base de donnees la plus sophistiquee au monde.

Forums PostgreSQL.fr

Le forum officiel de la communauté francophone de PostgreSQL

Vous n'êtes pas identifié(e).

#1 11/05/2022 13:20:33

maitrepylos
Membre

id exponentielle sur un merge

Bonjour,
j'ai une fonction qui fait un genre de merge

DECLARE
    v_email_ad varchar;
    v_email_perso varchar; 
    v_annee_academique integer; 
    v_email_ordre integer; 
    v_classe varchar;
    v_matric_rs integer; 
    v_numero integer; 
    v_statut varchar; 
    v_qualite varchar; 
    v_groupe varchar;
    v_orientation varchar; 
    v_resultat_final varchar; 
    v_id_people integer;
    v_numero_localisa integer;
    v_date_debut date;
    v_date_fin date;
    BEGIN
        FOR v_email_ad, v_email_perso, v_annee_academique, v_email_ordre, v_classe, v_matric_rs, v_numero, v_statut, v_qualite, v_groupe, v_orientation, v_resultat_final, v_id_people, v_numero_localisa,v_date_debut,v_date_fin
        IN SELECT email_ad, email_perso, annee_academique, email_ordre, classe, matric_rs, numero, statut, qualite, groupe, orientation, resultat_final, id_people, numero_localisa,date_debut,date_fin
        FROM brain.etudiants_update
        LOOP
            INSERT INTO brain.etudiants (email_ad, email_perso, annee_academique, email_ordre, classe, matric_rs, numero, statut, qualite, groupe, orientation, resultat_final,id_people,numero_localisa,date_debut,date_fin) 
                VALUES(v_email_ad, v_email_perso, v_annee_academique, v_email_ordre, v_classe, v_matric_rs, v_numero, v_statut, v_qualite, v_groupe, v_orientation, v_resultat_final, v_id_people,v_numero_localisa,v_date_debut,v_date_fin)
                ON CONFLICT ON CONSTRAINT update_id_student DO UPDATE SET 
                email_ad = excluded.email_ad, 
                email_perso = excluded.email_perso, 
                classe =  excluded.classe, 
                statut = excluded.statut, 
                qualite = excluded.qualite,
                groupe = excluded.groupe, 
                orientation = excluded.orientation, 
                resultat_final = excluded.resultat_final,
                id_people = excluded.id_people,
                numero_localisa = excluded.numero_localisa,
                date_debut = excluded.date_debut,
                date_fin = excluded.date_fin;
        END LOOP;
        truncate table only brain.etudiants_update restart identity cascade;
    return true;
    END

Ce que je ne comprend pas c'est que cette fonction à une influence sur mon id serial et que celui-ci atteint des nombres grandiose.
Auriez-vous une piste pour arrêter ce phénomène ?

D'avance merci/

Hors ligne

#2 11/05/2022 16:27:47

gleu
Administrateur

Re : id exponentielle sur un merge

Le INSERT INTO ON CONFLICT va d'abord tenter le INSERT et donc incrémenter la séquence liée à l'identifiant. Si jamais le INSERT ne peut se faire à cause d'un conflit et qu'un UPDATE a lieu, la séquence reste incrémentée. Ça n'est pas directement lié à l'insertion INSERT ON CONFLICT, mais plutôt à la séquence, vous auriez exactement le même problème avec un INSERT qui échouerait un grand nombre de fois. Donc si vous faites un INSERT ON CONFLICT qui se termine par un UPDATE sur un million de lignes, votre séquence aura augmentée d'un million à la fin du INSERT ON CONFLICT.


Guillaume.

Hors ligne

#3 19/05/2022 10:31:53

maitrepylos
Membre

Re : id exponentielle sur un merge

Merci pour la réponse.
Du coup, est-il possible de modifier la séquence pour utiliser les id's non utilisés ?

Hors ligne

#4 19/05/2022 10:43:36

rjuju
Administrateur

Re : id exponentielle sur un merge

Non, par définition une séquence peut avoir des valeurs non utilisées.

Hors ligne

#5 19/05/2022 10:58:42

maitrepylos
Membre

Re : id exponentielle sur un merge

Ok, du coup, faut que je trouve une autre façon de gérer mes id, j'imagine que j'aurais moins de souci avec un uuid ? car vu la doc un bigserial c'est 2 exp 31

Hors ligne

#6 19/05/2022 12:16:08

rjuju
Administrateur

Re : id exponentielle sur un merge

Un serial va jusqu'à 2^31, un bigserial va jusqu'à 2^63, ce qui devrait être suffisant

Hors ligne

#7 19/05/2022 13:38:40

maitrepylos
Membre

Re : id exponentielle sur un merge

Oui largement, j'avais lu 2.31 pour le bigserial
Merci de l'info.

Hors ligne

#8 19/05/2022 13:40:38

genamiga
Membre

Re : id exponentielle sur un merge

Un moyen de ne pas incrémenter si échec d'INSERT ?

Hors ligne

#9 19/05/2022 14:02:03

maitrepylos
Membre

Re : id exponentielle sur un merge

D'après l’explication de Guillaume, non !

Hors ligne

#10 19/05/2022 17:14:43

rjuju
Administrateur

Re : id exponentielle sur un merge

Non, la sequence doit être incrémentée pour pouvoir tenter la modification avec la valeur retournée, que celle-ci échoue ou pas.

Hors ligne

#11 15/09/2022 10:04:57

maitrepylos
Membre

Re : id exponentielle sur un merge

Je reviens car pour ne pas incrémenter inutilement, j'ai ajouter ceci dans mon script, a adapter en fonction des ses tables et séquences (Merci Alban)

SELECT setval('testing_hash_id_seq', MAX(id), true) FROM testing_hash;

Hors ligne

#12 15/09/2022 13:56:36

rjuju
Administrateur

Re : id exponentielle sur un merge

Vous comprenez bien que cette mise à jour se fait en tenant compte des règles d'isolation standard, et vous pouvez donc réinitialiser à une valeur inférieure à ce qui pourrait exister une fois une transaction concurrente committée, ce qui aurait comme conséquence de retourner des valeurs déjà utilisées.  Et dans ce cas, de nombreuses requêtes pourraient échouer, soit immédiatement soit longtemps après, en fonction de l'activité concurrente.

Hors ligne

#13 15/09/2022 14:11:07

maitrepylos
Membre

Re : id exponentielle sur un merge

rjuju a écrit :

Vous comprenez bien que cette mise à jour se fait en tenant compte des règles d'isolation standard, et vous pouvez donc réinitialiser à une valeur inférieure à ce qui pourrait exister une fois une transaction concurrente committée, ce qui aurait comme conséquence de retourner des valeurs déjà utilisées.  Et dans ce cas, de nombreuses requêtes pourraient échouer, soit immédiatement soit longtemps après, en fonction de l'activité concurrente.

Je n'avais pas pris cet élément en compte, car dans mon cas et pour cette db, je n'ai pas de transaction concurrente.
Merci pour le rappel à l'ordre smile

Hors ligne

Pied de page des forums