Forums PostgreSQL.fr

mimst

Membre

Problème sur requete UPDATE avec select for update

Voici ma requete posant problème :

UPDATE message SET is_dequeued = true
WHERE id IN (SELECT f2.id FROM message f2 WHERE f2.id_queue = 5 AND f2.is_dequeued = false ORDER BY f2.id ASC FOR UPDATE SKIP LOCKED  LIMIT 1)
returning *

Lorsque nous avons cette requête avec "LIMIT 1", la requête va parfois nous retourner plusieurs résultats... alors que lorsqu'on exécute uniquement le "select" il retourne uniquement 1 ligne....

Et ceci est aléatoire...dans la plupart des cas on a bien le bon résultat avec une ligne retournée et parfois il en retourne plusieurs...

Je ne vois pas d où ca vient...

Dernière modification par mimst (03/12/2019 16:08:51)

gleu

Administrateur

Re : Problème sur requete UPDATE avec select for update

Il serait intéressant d'avoir le plan d'exécution de la requête quand elle fournit le mauvais résultat ou un exemple complet qu'on puisse reproduire.

---

Guillaume.

mimst

Membre

Re : Problème sur requete UPDATE avec select for update

Si j'arrive à reproduire l'erreur car cela n'arrive pas souvent....j exécuterai un plan d’exécution pour en savoir plus.

Nous souhaitons dépiler des messages présent dans des queues en mettant à jour une colonne is_dequeued à TRUE.
Pour cela on récupère les messages d'une queue donnée Q et qui sont à is_dequeued = FALSE et on met à jour cette colonne à TRUE pour les messages récupérées.

ps: la limite n'est pas toujours de 1 c'est pour cela que nous avons un IN et non pas un = pour la clause WHERE

dverite

Membre

Re : Problème sur requete UPDATE avec select for update

Qu'un LIMIT 1 ramène plus qu'une ligne parait assez extraordinaire. Peut-on en savoir plus sur le contexte:

- quelle est la définition de la table, index compris?
- le niveau d'isolation  est-il read committed (le niveau par défaut) ou autre?
- est-ce que toutes les transactions qui utilisent la table sont dans le même niveau d'isolation?
- qu'est-ce qu'il y a comme autres comme opérations concurrentes probables sur la table (INSERT? DELETE?)
- est-ce que message.id peut changer? est-ce que message.id_queue peut changer?

---

@DanielVerite
http://blog-postgresql.verite.pro/

pitpoule

Membre

Re : Problème sur requete UPDATE avec select for update

Qu'un LIMIT 1 ramène plus qu'une ligne parait assez extraordinaire.

je pense que c'est plutôt le "returning" de l'update qui retourne plusieurs lignes

dverite

Membre

Re : Problème sur requete UPDATE avec select for update

je pense que c'est plutôt le "returning" de l'update qui retourne plusieurs lignes

C'est aussi comme ça que je le comprends, mais si on suppose que message.id est une clef primaire,
un UPDATE message SET ... WHERE id IN (...) RETURNING *
qui retourne plusieurs lignes, ça laisse penser que la sous-requête du IN (...) retourne plusieurs valeurs.

Ceci dit je me demande si un index corrompu sur message.id pourrait provoquer ce phénomène bizarre indépendamment du détail de ce qu'il y a dans la clause IN.

Dernière modification par dverite (04/12/2019 15:50:29)

---

@DanielVerite
http://blog-postgresql.verite.pro/

dverite

Membre

Re : Problème sur requete UPDATE avec select for update

Pour la théorie de l'index corrompu, il y a un module pour tester ça:

https://www.postgresql.org/docs/current/amcheck.html

---

@DanielVerite
http://blog-postgresql.verite.pro/

mimst

Membre

Re : Problème sur requete UPDATE avec select for update

Voici les réponse que je peux apporter:

Qu'un LIMIT 1 ramène plus qu'une ligne parait assez extraordinaire. Peut-on en savoir plus sur le contexte:

- quelle est la définition de la table, index compris?

Voici la définition de la table MESSAGE:
CREATE TABLE MESSAGE
    (   id BIGSERIAL NOT NULL,
        date_creation TIMESTAMP(6) WITH TIME ZONE DEFAULT now() NOT NULL,
        identifier CHARACTER VARYING(110) NOT NULL,
        ...
        queue_id INTEGER,
        is_dequeued BOOLEAN DEFAULT false NOT NULL,
        contenu JSON,
        CONSTRAINT pk_message PRIMARY KEY (id),
        CONSTRAINT fk_message_queue FOREIGN KEY (queue_id) REFERENCES "queue" ("id"),
        CONSTRAINT unique_message_identifier UNIQUE (identifier)
    );

et les index correspondants:

TABLE_CAT TABLE_SCHEM TABLE_NAME NON_UNIQUE INDEX_QUALIFIER INDEX_NAME                TYPE ORDINAL_POSITION COLUMN_NAME ASC_OR_DESC CARDINALTIY PAGES FILTER_CONDITION
--------- ----------- ---------- ---------- --------------- ------------------------- ---- ---------------- ----------- ----------- ----------- ----- ----------------
(null)               core        message                false                (null)          pk_message                           3                1                id          A           0.0         29    (null)           
(null)               core        message                false                (null)          unique_message_identifier      3                1                identifier  A           0.0         76    (null)           

- le niveau d'isolation  est-il read committed (le niveau par défaut) ou autre?

C'est le niveau par défaut (read commited)

- est-ce que toutes les transactions qui utilisent la table sont dans le même niveau d'isolation?

- qu'est-ce qu'il y a comme autres comme opérations concurrentes probables sur la table (INSERT? DELETE?)

Sinon aucune autre opération effectuée en même temps

- est-ce que message.id peut changer? est-ce que message.id_queue peut changer?

Cette requête nous l'avons extraite de notre programme et nous l’exécutons manuellement pour la tester et nous arrivons par moment à refaire le problème. Donc les identifiants sont fixés à la main dans la requête et le problème est toujours là

dverite

Membre

Re : Problème sur requete UPDATE avec select for update

- est-ce que message.id peut changer? est-ce que message.id_queue peut changer?

Cette requête nous l'avons extraite de notre programme et nous l’exécutons manuellement pour la tester et nous arrivons par moment à refaire le problème. Donc les identifiants sont fixés à la main dans la requête et le problème est toujours là

Ce que je voulais dire par un changement c'est plutôt de savoir si pendant que cet UPDATE met à jour is_dequeued il peut y avoir une autre transaction qui chercher à changer le id_queue de la même ligne.

Mais si l'exécuteur utilise l'index unique sur id pour faire l'update et que cet index est corrompu ça pourrait expliquer pourquoi il peut trouver plusieurs lignes correspondant à un même id, c'est pourquoi je pense qu'il faudrait vérifier ça.

---

@DanielVerite
http://blog-postgresql.verite.pro/