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 03/12/2019 12:09:13

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)

Hors ligne

#2 03/12/2019 14:31:17

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.

Hors ligne

#3 03/12/2019 16:24:02

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

Hors ligne

#4 04/12/2019 13:37:55

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?

Hors ligne

#5 04/12/2019 15:34:14

pitpoule
Membre

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

dverite a écrit :

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

Hors ligne

#6 04/12/2019 15:50:00

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)

Hors ligne

#7 04/12/2019 15:53:43

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

Hors ligne

#8 06/12/2019 09:46:15

mimst
Membre

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

Voici les réponse que je peux apporter:

dverite a écrit :

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à

Hors ligne

#9 06/12/2019 15:28:28

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.

Hors ligne

#10 11/05/2022 16:15:26

mimst
Membre

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

Je reviens pour le problème qui n'a pas été corrigé et qui arrive très rarement....mais arrive toujours
Voici la requête 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)

Quand le problème survient et que j'exécute uniquement le SELECT, elle retourne également parfois plus d'1 lignes. Comment est ce possible ?

En allant plus loin au niveau des index, pas de corruption. Par contre le plan d'exécution est différent lorsque la requête fonctionne (retourne 1 ligne) et lorsqu'elle déconne (retourne plus d'1 ligne).


Voici les plans d'exécutions:


Plan quand la requête fonctionne

Operation              Node Cost Actual Rows Actual Loops Startup Cost Total Cost Plan Rows Plan Width Strategy Sort Method Sort Space Used Sort Space Type Parent Relationship Relation Name Alias        Join Type Join Filter                      Sort Columns Hash Cond Filter                                 
---------------------- --------- ----------- ------------ ------------ ---------- --------- ---------- -------- ----------- --------------- --------------- ------------------- ------------- ------------ --------- -------------------------------- ------------ --------- --------------------------------------
ModifyTable            29,5 %    1           1            2.06         4.19       1         1301                                                                                message       message                                                                                                                                         
  Nested Loop          5,1 %     1           1            2.06         4.19       1         1301                                                            Member                                         Semi      (message.id = "ANY_subquery".id)                                                                                         
    Seq Scan           14,1 %    4           1            0.0          2.04       4         1268                                                            Outer               message       message                                                                                                                                         
    Materialize          0,0 %     1           4            2.06         2.09       1         40                                                              Inner                                                                                                                                                                             
      Subquery Scan  3,8 %     1           1            2.06         2.08       1         40                                                              Outer                             ANY_subquery                                                                                                                                     
        Limit               1,3 %     1           1            2.06         2.07       1         14                                                              Subquery                                                                                                                                                                           
          LockRows     32,1 %    1           1            2.06         2.07       1         14                                                              Outer                                                                                                                                                                             
            Sort            7,7 %     1           1            2.06         2.06       1         14                  quicksort   25              Memory          Outer                                                                                     m2.id                                                                                   
              Seq Scan  6,4 %     4           1            0.0          2.05       1         14                                                              Outer               message       m2                                                                             ((NOT is_dequeued) AND (queue_id = 5))



Plan quand la requête ne fonctionne pas et retourne plus d'1 résultat:

Operation            Node Cost Actual Rows Actual Loops Startup Cost Total Cost Plan Rows Plan Width Strategy Sort Method Sort Space Used Sort Space Type Parent Relationship Relation Name Alias        Join Type Join Filter                      Sort Columns Hash Cond Filter                                 Index Name Index Condition
-------------------- --------- ----------- ------------ ------------ ---------- --------- ---------- -------- ----------- --------------- --------------- ------------------- ------------- ------------ --------- -------------------------------- ------------ --------- -------------------------------------- ---------- ---------------
ModifyTable          40,0 %    4           1            2.01         4.05       1         1301                                                                                message       message                                                                                                                                         
  Nested Loop        1,2 %     4           1            2.01         4.05       1         1301                                                            Member                                         Semi      (message.id = "ANY_subquery".id)                                                                                         
    Seq Scan         12,4 %    4           1            0.0          2.0        1         1268                                                            Outer               message       message                                                                                                                                         
    Subquery Scan 12,8 %    1           4            2.01         2.03       1         40                                                              Inner                             ANY_subquery                                                                                                                                     
      Limit               0,0 %     1           4            2.01         2.02       1         14                                                              Subquery                                                                                                                                                                           
        LockRows     17,6 %    1           4            2.01         2.02       1         14                                                              Outer                                                                                                                                                                             
          Sort           14,4 %    2           4            2.01         2.01       1         14                  quicksort   25              Memory          Outer                                                                                     m2.id                                                                                   
            Seq Scan  1,6 %     4           1            0.0          2.0        1         14                                                              Outer               message       m2                                                                             ((NOT is_dequeued) AND (queue_id = 5))


Si quelqu'un a une idée du problème, je suis vraiment preneur...

Hors ligne

#11 11/05/2022 22:53:13

gleu
Administrateur

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

Merci de fournir des plans d'exécution non réinterprétés (donc le réel résultat d'un EXPLAIN (ANALYZE) ). Déjà que comprendre un plan d'exécution n'est pas simple, mais si en plus, on doit interpréter un plan qui a été mouliné par un outil, on ne va pas s'en sortir smile

Ensuite, je suis étonné que le SELECT seul renvoie plus d'une ligne. On parle bien du SELECT ci-dessous ?

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

Avez-vous un exemple complet montrant le problème à fournir pour qu'on teste de notre côté ? (j'entends par là toutes les requêtes de création des objets, notamment la table, de remplissage de la table, etc, jusqu'à la requête montrant votre problème)


Guillaume.

Hors ligne

#12 16/05/2022 14:17:10

mimst
Membre

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

Après avoir réussi difficilement à reproduire le problème, j'ai les 2 plans d'exécution quand cela fonctionne (MAJ d'une seule ligne) et quand ça me mets à jour plus d'une ligne.

Voici les plans d'exécutions de la requête UPDATE:

Quand la requête met à jour une seule ligne (cas où ca marche),dans l'exemple cela met à jour 1 ligne sur les 5 existantes ce qui est demandé par la requête:

Update on message  (cost=0.13..9.07 rows=1 width=1273) (actual time=0.108..0.111 rows=1 loops=1)
  ->  Nested Loop Semi Join  (cost=0.13..9.07 rows=1 width=1273) (actual time=0.084..0.087 rows=1 loops=1)
        Join Filter: (message.id = "ANY_subquery".id)
        Rows Removed by Join Filter: 4
        ->  Seq Scan on message  (cost=0.00..5.05 rows=5 width=1240) (actual time=0.020..0.025 rows=5 loops=1)
        ->  Materialize  (cost=0.13..3.95 rows=1 width=40) (actual time=0.010..0.011 rows=1 loops=5)
              ->  Subquery Scan on "ANY_subquery"  (cost=0.13..3.95 rows=1 width=40) (actual time=0.044..0.045 rows=1 loops=1)
                    ->  Limit  (cost=0.13..3.94 rows=1 width=14) (actual time=0.038..0.038 rows=1 loops=1)
                          ->  LockRows  (cost=0.13..19.15 rows=5 width=14) (actual time=0.034..0.034 rows=1 loops=1)
                                ->  Index Scan using pk_message on message m2  (cost=0.13..19.10 rows=5 width=14) (actual time=0.006..0.006 rows=1 loops=1)
                                      Filter: ((NOT is_dequeued) AND (queue_id = 6))



Quand la requête met à jour plus d'une ligne (cas où ca ne marche pas ), dans l'exemple cela met à jour 4 lignes sur les 5 existantes :

Update on message  (cost=5.01..10.05 rows=1 width=1276) (actual time=0.202..0.265 rows=4 loops=1)
  ->  Nested Loop Semi Join  (cost=5.01..10.05 rows=1 width=1276) (actual time=0.115..0.126 rows=4 loops=1)
        Join Filter: (message.id = "ANY_subquery".id)
        Rows Removed by Join Filter: 1
        ->  Seq Scan on message  (cost=0.00..5.00 rows=1 width=1243) (actual time=0.020..0.024 rows=5 loops=1)
        ->  Subquery Scan on "ANY_subquery"  (cost=5.01..5.03 rows=1 width=40) (actual time=0.019..0.019 rows=1 loops=5)
              ->  Limit  (cost=5.01..5.02 rows=1 width=14) (actual time=0.015..0.015 rows=1 loops=5)
                    ->  LockRows  (cost=5.01..5.02 rows=1 width=14) (actual time=0.015..0.015 rows=1 loops=5)
                          ->  Sort  (cost=5.01..5.01 rows=1 width=14) (actual time=0.008..0.009 rows=2 loops=5)
                                Sort Key: m2.id
                                Sort Method: quicksort  Memory: 25kB
                                ->  Seq Scan on message m2  (cost=0.00..5.00 rows=1 width=14) (actual time=0.006..0.010 rows=5 loops=1)
                                      Filter: ((NOT is_dequeued) AND (queue_id = 6))


J'ai retesté uniquement le SELECT et il semble retourné une ligne même dans le cas ou cela ne fonctionne pas.. alors est ce l'UPDATE..returning * qui fait que parfois on retourne plus d'1 ligne.
Le problème se produit très très rarement, mais impossible de savoir qu'est ce qui fait qu'on a le soucis et pourquoi cela se résout au bout d'un moment.
Par exemple on va avoir 5 requêtes de suite où ca va planter, et ensuite on peut effectuer 60 requête d'affilée et ne pas avoir de problème. Il semble également que le problème se produit plus souvent lorsque la base est presque vide.


Sinon voici la structure de la table:
CREATE TABLE
    MESSAGE
    (
        id BIGSERIAL NOT NULL,
        date_creation TIMESTAMP(6) WITH TIME ZONE DEFAULT now() NOT NULL,
        identifier CHARACTER VARYING(110) NOT NULL,
        content_type CHARACTER VARYING(250),
        content_encoding CHARACTER VARYING(50),
        priority SMALLINT,
        routing_key CHARACTER VARYING(50),
        headers CHARACTER VARYING(2000),
        application_properties CHARACTER VARYING(2000),
        body_location CHARACTER VARYING(250),
        delivery_mode CHARACTER VARYING(50),
        body_size INTEGER,
        application_id CHARACTER VARYING(30),
        exchange_name CHARACTER VARYING(250),
        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)
    );


Le principe de la requête est de dépiler une seule ligne de la table MESSAGE à chaque appel, par contre si un appel est fait simultanément, il ne faut pas prendre la même ligne que la première requête d'où l'utilisation du select for update...skip locked.
Pourquoi par moment avons nous ce problème de MAJ de plus d'1 ligne ? est ce un problème de configuration PostGresSQL ?

Merci d'avance

Hors ligne

#13 16/05/2022 22:46:08

gleu
Administrateur

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

Clairement, aucun changement dans la configuration de PostgreSQL ne règlera ça. J'ai testé de mon côté et j'obtiens un résultat satisfaisant.

Normalement, ce type de requête se fait en deux temps, d'abord le SELECT FOR UPDATE, puis le UPDATE. Avez-vous essayé ça ?


Guillaume.

Hors ligne

#14 17/05/2022 12:00:15

dverite
Membre

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

Une recherche dans les mailing-lists trouve une réponse de Thomas Munro sur un cas comparable:

https://www.postgresql.org/message-id/f … utlook.com

Lorsque la table dont il faut verrouiller les lignes est référencée une deuxième fois dans la requête pour jointure, il dit que les deux côtés de la jointure n'ont pas le même niveau de visibilité, ce qui produit des résultats incohérents par rapport aux attentes.

Est-ce qu'il y a une solution avec la forme actuelle de la requête? Il dit:

You'd need to make sure that the row locking applies to the same relation(s) as the WHERE clause to avoid that.

et effectivement avec la requête mentionnée en #1

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 *

on n'est pas dans la bonne situation parce que le WHERE id... s'applique à la table message au niveau supérieur alors que le FOR UPDATE s'applique à la table message aliasée en "f2" dans la sous-requête.


Pour éviter le problème, on peut j'imagine soit couper la requête en deux comme suggère Guillaume en #13, soit utiliser un niveau d'isolation supérieur, REPEATABLE READ ou SERIALIZED. Dans ces niveaux d'isolation, ce genre d'anomalie liés aux accès concurrents devrait être remonté en erreur à l'utilisateur avec annulation de la transaction, au lieu de passer en force en mode "advienne que pourra" (en READ COMMITTED).

Hors ligne

Pied de page des forums