Vous n'êtes pas identifié(e).
Pages : 1
Bonjour
Nous avons observé un comportement très étrange de postgreSQL (v. 11.12) avec une table d’une de nos bases de données :
CREATE TABLE truites.sponsor (
sponsor varchar(30) NOT NULL,
sponsor_email varchar(100) NULL,
sponsor_address varchar(300) NULL,
sponsor_tel varchar(15) NULL,
sponsor_url varchar(100) NULL,
CONSTRAINT sponsor_pkey PRIMARY KEY (sponsor)
);Le problème porte sur un caractère '_' dans la clé primaire sponsor, dont nous ignorons comment il est stocké (encodé ?) par postgres.
NB : j'ai utilisé le champ sponsor_email pour bien différencier des enregistrements.
select sponsor, sponsor_email from truites.sponsor s where s.sponsor LIKE 'Univ.\_Antilles'
sponsor |sponsor_email |
--------------+-------------------+
Univ._Antilles|has real underscore|
select sponsor, sponsor_email from truites.sponsor s where s.sponsor LIKE 'Univ._Antilles'
sponsor |sponsor_email |
--------------+-------------------+
Univ._Antilles| |
Univ._Antilles|has real underscore|
select md5(sponsor), sponsor_email from truites.sponsor s where s.sponsor LIKE 'Univ._Antilles'
md5 |sponsor_email |
--------------------------------+-------------------+
671fa31dcf8193915bf3de0e039f428c| |
671fa31dcf8193915bf3de0e039f428c|has real underscore|La première requête montre qu'il existe une valeur de clé qui contient un caractère '_'
La deuxième requête montre qu'il existe une valeur de clé qui contient un caractère qui ressemble a un '_' mais qui n'en est pas un sinon on aurait un conflit de clé primaire.
La troisième requête montre que pourtant ces deux caractères sont identiques puisque le md5 est le même.
Nous avons exactement le même comportement avec une autre clé 'AAPPMA_Greoux'.
De plus nous pouvons observer ceci avec l'utilisation de la clause IN :
select sponsor, sponsor_email from truites.sponsor s where s.sponsor IN ('AAPPMA_Greoux')
sponsor |sponsor_email |
-------------+------------------------+
AAPPMA_Greoux|has real underscore|
select sponsor, sponsor_email from truites.sponsor s where s.sponsor IN ('Univ._Antilles')
sponsor |sponsor_email |
--------------+-------------------+
Univ._Antilles|has real underscore|
select sponsor, sponsor_email from truites.sponsor s where s.sponsor IN ('AAPPMA_Greoux', 'Univ._Antilles')
sponsor |sponsor_email |
--------------+-------------------+
AAPPMA_Greoux | |
Univ._Antilles| |
Univ._Antilles|has real underscore|
AAPPMA_Greoux |has real underscore|Comment se fait-il que les enregistrements ressortent quand on a deux valeurs dans la clause 'IN' d'autant qu'ils ne ressortent pas quand on n'a qu'une valeur ?
Nous ne savons pas du tout comment nous nous sommes retrouvés dans cette situation.
La valeur de ce champ est renseigné via une interface Web de type CRUD d'un site php.
Quelqu'un aurait-il une explication à ce comportement ? Il y a-t-il quelque chose qui nous échappe ?
Merci d'avance pour votre aide
Bien cordialement
Olivier
Hors ligne
Bonjour,
Cela ressemble à une corruption de votre index. Vous devriez regarder le plan d'exécution pour les requêtes qui renvoient des résultats différents et valider que dans un cas vous avez un parcours d'index et dans l'autre cas non. Un REINDEX de l'index en question devrait pointer le même problème, malheureusement en échouant pour cause de violation d'unicité. Il va vous falloir trouver et nettoyer les enregistrements problématiques à la main, mais surtout comprendre comment le problème a pu arriver. Avez-vous mis à jour postgres depuis d'autres versions (majeures ou mineures), si oui lesquelles et comment. Avez-vous eu des incidents sur l'instance? S'il s'agit d'un serveur GNU/Linux avez-vous un noyau suffisamment récent pour garantir la durabilité des données ?
Julien.
https://rjuju.github.io/
Hors ligne
Bonjour
Merci beaucoup pour ce retour. En effet il semble que l'index soit corrompu.
explain select sponsor, sponsor_email from truites.sponsor s where s.sponsor IN ('AAPPMA_Greoux', 'Univ._Antilles')
QUERY PLAN |
----------------------------------------------------------------------------+
Seq Scan on sponsor s (cost=0.00..10.88 rows=2 width=296) |
Filter: ((sponsor)::text = ANY ('{AAPPMA_Greoux,Univ._Antilles}'::text[]))|
explain select sponsor, sponsor_email from truites.sponsor s where s.sponsor IN ('Univ._Antilles')
QUERY PLAN |
------------------------------------------------------------------------------+
Index Scan using sponsor_pkey on sponsor s (cost=0.14..8.16 rows=1 width=296)|
Index Cond: ((sponsor)::text = 'Univ._Antilles'::text) |Cela explique la différence de comportement entre les deux requêtes...
Et REINDEX n'aboutit effectivement pas.
Nous avons effectivement fait une mise à jour pg 9 => pg 10 => pg 11 à l'occasion d'une mise à jour système (debian, maintenant en 10.9).
J'utilise la commande pg_upgradecluster pour faire la migration.
Mais difficile de savoir à quand remonte le problème et si cela a effectivement un lien avec les mises à jour.
Il n'y a a priori que ces deux clés qui posent problème. Le fait que le caractère '_' soit en question alors qu'il s'agit d'un caractère spécial du LIKE m'interpelle. Mais nous avons d'autres clés qui contiennent ce caractère qui elles ne posent pas problème. Donc...
En tout cas, supprimer ces enregistrements et faire un REINDEX devrait résoudre le problème. Au pire je peux tout régénérer. Je voulais surtout comprendre ce comportement, mettre une explication technique.
Merci beaucoup.
Olivier
Hors ligne
Vous pouvez vérifier la présence de doublon avec une requête du type
SELECT sponsor, count(*) FROM truites.sponsor GROUP BY sponsor HAVING count(*) > 1
(bien entendu pensez il faut s'assurer que l'index ne soit pas utilisé, vous pouvez désactiver les divers enable_indexcan et similaires pour cela).
Le problème n'a pas de lien avec les mises à jour, mais en cas de mise à jour avec pg_upgrade les corruptions sont conservées ce qui rend impossible de savoir si la corruption a eu lieu en v11 ou avant.
Cela dit, vu qu'il s'agit d'une colonne avec collation et que vous mentionnez une mise à jour de l'OS, cela est peut être tout simplement le résultat d'une mise à jour de la glibc, voir https://postgresql.verite.pro/blog/2018 … grade.html pour plus de détails. Si c'est votre cas, il vous faut réindexer tous les index potentiellement impactés.
Julien.
https://rjuju.github.io/
Hors ligne
Pour la migration vers Debian 10 le problème de changement glibc par rapport aux index avait été rapporté à Debian ce qui s'est traduit par cette entrée dans la doc
("Issues to be aware of for buster"): https://www.debian.org/releases/buster/ … ql-reindex
When upgrading from stretch to buster, the glibc locale data is upgraded. Specifically, this changes how PostgreSQL sorts data in text indexes. To avoid corruption, such indexes need to be REINDEXed immediately after upgrading the locales or locales-all packages, before putting the database back into production.
Suggested command:
sudo -u postgres reindexdb --all
Alternatively, upgrade the databases to PostgreSQL 11 using pg_upgradecluster. (This uses pg_dump by default which will rebuild all indexes. Using -m upgrade or pg_upgrade is not safe because it preserves the now-wrong index ordering.)
En #3 olobry dit avoir utilisé pg_upgradecluster donc a priori c'est OK sauf que ça dépend avec quelles options, et si ça a été fait vraiment dans le même temps que l'upgrade OS.
@DanielVerite
http://blog-postgresql.verite.pro/
Hors ligne
Bonjour
Merci pour l'information, je suis effectivement passé à côté de cette note lors de l'upgrade debian. Et je fais effectivement des pg_upgradecluster -m upgrade car nous avons des tables très volumineuses. Tout s'explique maintenant.
Merci beaucoup pour vos réponses.
Cordialement
Olivier
Hors ligne
Pages : 1