Pourquoi ce code échoue-t-il dans PostgreSQL et comment le corriger (contourner) ? Est-ce une faille du moteur Postgres SQL?

Question

J'ai travaillé sur une tâche d'analyse de texte quand j'ai trouvé un comportement étrange de Postgres. Mon code d'origine exposant une erreur étrange était écrit en Java avec une connectivité JDBC pour PostgreSQL (testé avec v8.3.3 et v8.4.2), voici mon post original : Est-ce une erreur du moteur SQL de PostgreSQL et comment éviter (contourner) cela ?. J'ai simplement porté mon code Java donné là en pur plpgsql et il donne les mêmes erreurs (même comportement que celui décrit dans le post original).

Le code simplifié n'a maintenant rien à voir avec l'analyse - il génère simplement des mots pseudo-aléatoires (mais reproductibles) et les insère après normalisation (la table spb_word contient des mots et des identifiants uniques, ils sont référencés par des identifiants dans la table finale spb_obj_word et la table spb_word4obj fonctionne comme un tampon d'entrée).

Voici mes tables (copiées depuis OP) :

create sequence spb_word_seq;

create table spb_word (
  id bigint not null primary key default nextval('spb_word_seq'),
  word varchar(410) not null unique
);

create sequence spb_obj_word_seq;

create table spb_obj_word (
  id int not null primary key default nextval('spb_obj_word_seq'),
  doc_id int not null,
  idx int not null,
  word_id bigint not null references spb_word (id),
  constraint spb_ak_obj_word unique (doc_id, word_id, idx)
);

create sequence spb_word4obj_seq;

create table spb_word4obj (
  id int not null primary key default nextval('spb_word4obj_seq'),
  doc_id int not null,
  idx int not null,
  word varchar(410) not null,
  word_id bigint null references spb_word (id),
  constraint spb_ak_word4obj unique (doc_id, word_id, idx),
  constraint spb_ak_word4obj2 unique (doc_id, word, idx)
);

et le code porté en plpgsql depuis le code Java d'origine :

create sequence spb_wordnum_seq;

create or replace function spb_getWord() returns text as $$
declare
  rn int;
  letters varchar(255) :=   'ąćęłńóśźżjklmnopqrstuvwxyz';
                          --'abcdefghijklmnopqrstuvwxyz';
  llen int := length(letters);
  res text := '';
  wordnum int;
begin
  select nextval('spb_wordnum_seq') into wordnum;

  rn := 3 * (wordnum + llen * llen * llen);
  rn := (rn + llen) / (rn % llen + 1);
  rn := rn % (rn / 2 + 10);

  loop
    res := res || substring(letters, rn % llen, 1);
    rn := floor(rn / llen);
    exit when rn = 0;
  end loop;

  --raise notice 'word for wordnum=% is %', wordnum, res;

  return res;
end;
$$ language plpgsql;

create or replace function spb_runme() returns void as $$
begin
  perform setval('spb_wordnum_seq', 1, false);
  truncate table spb_word4obj, spb_word, spb_obj_word;

  for j in 0 .. 50000-1 loop

    if j % 100 = 0 then raise notice 'j = %', j; end if;

    delete from spb_word4obj where doc_id = j;

    for i in 0 .. 20 - 1 loop
      insert into spb_word4obj (word, idx, doc_id) values (spb_getWord(), i, j);         
    end loop;

    update spb_word4obj set word_id = w.id from spb_word w 
    where w.word = spb_word4obj.word and doc_id = j;

    insert into spb_word (word) 
    select distinct word from spb_word4obj 
    where word_id is null and doc_id = j;

    update spb_word4obj set word_id = w.id 
    from spb_word w 
    where w.word = spb_word4obj.word and 
    word_id is null and doc_id = j;

    insert into spb_obj_word (word_id, idx, doc_id) 
    select word_id, idx, doc_id from spb_word4obj where doc_id = j;
  end loop;
end;
$$ language plpgsql;

Pour exécuter ceci, exécutez simplement select spb_runme() en tant que déclaration SQL.

Voici le premier exemple d'erreur :

NOTICE:  j = 8200
ERROR:  la valeur de clé dupliquée viole la contrainte unique "spb_word_word_key"
CONTEXT:  instruction SQL "insert into spb_word (word) select distinct word from spb_word4obj where word_id is null and doc_id =  $1 "
Fonction PL/pgSQL "spb_runme" ligne 18 dans l'instruction SQL

et le deuxième :

NOTICE:  j = 500
ERROR:  la valeur nulle dans la colonne "word_id" viole la contrainte de non NULL
CONTEXT:  instruction SQL "insert into spb_obj_word (word_id, idx, doc_id) select word_id, idx, doc_id from spb_word4obj where doc_id =  $1 "
Fonction PL/pgSQL "spb_runme" ligne 27 dans l'instruction SQL

Ces erreurs se produisent de manière imprévisible - à chaque fois à une itération différente (j) et avec des mots différents provoquant l'erreur.

Lorsque les caractères nationaux polonais (ąćęłńóśźż) sont éliminés des mots générés (la ligne letters varchar(255) := 'ąćęłńóśźżjklmnopqrstuvwxyz'; devient letters varchar(255) := 'abcdefghijklmnopqrstuvwxyz';), il n'y a pas d'erreur ! Ma base de données est créée avec un encodage UTF-8, donc il ne devrait pas y avoir de problème avec les caractères non-ascii, mais apparemment c'est très important !

Maintenant ma question : qu'est-ce qui ne va pas avec mon code ? Est-ce quelque chose de sérieusement incorrect avec PostgreSQL ? Comment contourner cette erreur ?

En passant : s'il s'agit d'une erreur dans le moteur PostgreSQL, comment cette base de données peut-elle être fiable ? Devrais-je passer à l'une des alternatives gratuites (par exemple MySQL) ?

---

MISE À JOUR : explications supplémentaires (principalement pour OMG Ponies)

Si je supprime le delete inutile, j'ai toujours les mêmes erreurs.

La fonction spb_getWord() doit générer des mots avec des duplicatas - elle simule l'analyse de texte et la division en mots - et certains mots se répètent - c'est normal et le reste de mon code gère les doublons. En raison des duplicatas possibles générés par spb_getWord(), j'insère des mots dans la table tampon spb_word4obj et puis je mets à jour word_id dans cette table pour les mots déjà traités de spb_word. Ainsi maintenant - si une ligne dans spb_word4obj a word_id non null - alors c'est un doublon, je n'insérerai donc pas ce mot dans spb_word. Mais - comme OMG Ponies l'a mentionné, j'obtiens l'erreur la valeur de clé dupliquée viole la contrainte unique ce qui signifie que mon code qui gère correctement les doublons échoue. C'est-à-dire que mon code échoue à cause d'une erreur interne de Postgres - le code correct est en quelque sorte mal exécuté par Postgres et échoue.

Après l'insertion de nouveaux mots (reconnus comme des doublons et marqués pour ne pas être insérés) dans spb_word, mon code insère enfin des mots normalisés dans spb_obj_word - remplaçant le corps du mot par une référence à une entrée non dupliquée dans spb_word, mais cela échoue parfois à cause d'une erreur interne de Postgres. Encore une fois, je pense que mon code est correct mais il échoue parce qu'il y a un problème dans le moteur SQL de Postgres lui-même.

Ajouter ou supprimer des lettres nationales polonaises des mots générés par spb_getWord m'assure seulement qu'il s'agit d'une erreur étrange de Postgres - toutes les considérations uniques/doublons restent les mêmes mais permettre/ne pas permettre certaines lettres dans les mots conduit à des erreurs ou les élimine. Donc ce n'est pas une erreur dans mon code - une mauvaise gestion des doublons.

Deuxième chose qui me assure que ce n'est pas une erreur dans mon code est le moment imprévisible des erreurs qui sont détectées. Chaque exécution de mon code fait la même séquence de mots donc il devrait toujours se casser au même endroit avec la même valeur provoquant l'erreur. Mais ce n'est pas le cas - c'est un moment assez aléatoire où cela échoue.

Answer 1

REMARQUE : j = 8200
ERREUR : la valeur clé en double viole la contrainte unique "spb_word_word_key"
CONTEXTE : déclaration SQL « insert into spb_word (word) select distinct word from spb_word4obj where word_id is null and doc_id = $1 »
Fonction PL/pgSQL "spb_runme" ligne 18 à la déclaration SQL

...vous indique que votre fonction spb_getWord() génère des valeurs qui existent déjà dans la table SPB_WORD. Vous devez mettre à jour la fonction pour vérifier si le mot existe déjà avant de quitter la fonction - si c'est le cas, régénérez jusqu'à ce que vous obteniez un mot qui n'existe pas encore.

Je pense que votre fonction spb_runme() doit ressembler à ceci :

create or replace function spb_runme() returns void as $$
DECLARE
  v_word VARCHAR(410);

begin
  perform setval('spb_wordnum_seq', 1, false);
  truncate table spb_word4obj, spb_word, spb_obj_word;

  for j in 0 .. 50000-1 loop

    if j % 100 = 0 then raise notice 'j = %', j; end if;

    for i in 0 .. 20 - 1 loop
      v_word := spb_getWord();
      INSERT INTO spb_word (word) VALUES (v_word);

      INSERT INTO spb_word4obj 
        (word, idx, doc_id, word_id)
        SELECT w.word, i, j, w.id
          FROM SPB_WORD w 
         WHERE w.word = v_word;

    end loop;

    INSERT INTO spb_obj_word (word_id, idx, doc_id) 
    SELECT w4o.word_id, w4o.idx, w4o.doc_id 
      FROM SPB_WORD4OBJ w4o 
     WHERE w40.doc_id = j;

  end loop;
end;

Cela vous permettrait de modifier le word_id pour ne pas prendre en charge les NULs. Lorsque vous travaillez avec des clés étrangères, remplissez d'abord la table référencée par la clé étrangère avant - commencez par le parent, puis passez à ses enfants.

L'autre changement que j'ai apporté était de stocker le résultat de spb_getWord() dans une variable (v_word), car appeler la fonction plusieurs fois signifie que vous obtiendriez une valeur différente à chaque fois.

Dernière chose - j'ai supprimé l'instruction de suppression. Vous avez déjà tronqué la table, il n'y a rien à supprimer. Certainement rien associé à une valeur de j.

Answer 2

J'ai réussi à simplifier le code de test - maintenant il utilise une seule table. Le problème simplifié a été posté sur la liste de diffusion pgsql-bugs : http://archives.postgresql.org/pgsql-bugs/2010-01/msg00182.php. Il est confirmé qu'il se produit sur d'autres machines (pas seulement la mienne).

Voici cette version simplifiée de la fonction de test principale (elle a besoin d'une table spb_word, de séquences spb_wordnum_seq et spb_word_seq et une fonction spb_getWord donnée dans ma question).

create or replace function spb_runmeSimple2(cnt int) returns void as $$
declare
  w varchar(410);
  wordId int;
begin
  perform setval('spb_wordnum_seq', 1, false);
  truncate table spb_word cascade;

  for i in 1 .. cnt loop

    if i % 100 = 0 then raise notice 'i = %', i; end if;

    select spb_getWord() into w;
    select id into wordId from spb_word where word = w;
    if wordId is null then 
      insert into spb_word (word) values (w);
    end if;

  end loop;
end;
$$ language plpgsql;

Maintenant une erreur se produit (mais de manière imprévisible) lors de l'exécution de select spb_runmeSimple2(10000000).

Voici la solution de contournement : changer la collation de la base de données du polonais à la norme 'C'. Avec la collation 'C', il n'y a pas d'erreur. Mais sans la collation polonaise, les mots polonais sont triés incorrectement (par rapport aux caractères nationaux polonais), donc le problème devrait être résolu dans Postgres lui-même.