Vous n'êtes pas identifié(e).
Pages : 1
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
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
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
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/
Hors ligne
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
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/
Hors ligne
Pour la théorie de l'index corrompu, il y a un module pour tester ça:
@DanielVerite
http://blog-postgresql.verite.pro/
Hors ligne
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à
Hors ligne
- 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/
Hors ligne
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
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 
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 1Avez-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
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
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
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).
@DanielVerite
http://blog-postgresql.verite.pro/
Hors ligne
Pages : 1