mardi 30 octobre 2012

Un coalesce en colonnes

Ça faisait longtemps que l'on avait pas fait de SQL, non? Alors, petit exercice. Soit une table contenant pour chaque date le nom d'un champ qui change de valeur, et sa nouvelle valeur. Comment transformer cette approche historique en approche par état, où l'on voit à chaque instant la valeur de chaque champ?

Tout d'abord, créons la table.

create table history(t serial, field text, value integer);
insert into history values(default, 'a', 1);
insert into history values(default, 'b', 12);
insert into history values(default, 'a', 3);
insert into history values(default, 'c', 101);
insert into history values(default, 'b', 12);
insert into history values(default, 'b', 14);
insert into history values(default, 'a', 3);
insert into history values(default, 'a', 1);
insert into history values(default, 'c', 102);
insert into history values(default, 'a', 2);
insert into history values(default, 'b', 13);

Pour visualiser la valeur de chaque champ, on donnera à chaque champ sa colonne, pour l'associer à l'ensemble des moments qui nous intéressent. Une jointure gauche s'impose donc:

select m.t, a.value as a, b.value as b, c.value as c
from history m
left outer join history a on m.t = a.t and a.field = 'a'
left outer join history b on m.t = b.t and b.field = 'b'
left outer join history c on m.t = c.t and c.field = 'c'
order by m.t

tabc
11
212
33
4101
512
614
73
81
9102
102
1113

C'est à peu près ça, sauf qu'il faut maintenant remplir les vides avec la dernière valeur disponible. Une sorte de coalesce, mais vertical.

Les sous-requêtes viennent rapidement à notre aide: pour chaque champ, l'on veut récupérer la dernière valeur en ordonnant par le temps, ce qui est facile grâce à limit. Voici donc:

select
 m.t,
 (select value from history h 
  where h.t <= m.t 
  and field = 'a' 
  and value is not null 
  order by h.t 
  desc limit 1) as a,
 (select value from history h 
  where h.t <= m.t 
  and field = 'b' 
  and value is not null 
  order by h.t desc 
  limit 1) as b,
 (select value from history h 
  where h.t <= m.t 
  and field = 'c' 
  and value is not null 
  order by h.t desc 
  limit 1) as c
from history m

tabc
11
2112
3312
4312101
5312101
6314101
7314101
8114101
9114102
10214102
11213102

Byzance! Mais arrêtons nous un instant: n'est-ce pas terriblement inefficace d'avoir des sous-requêtes dans le select? Peut-on trouver une autre solution, en utilisant par exemple des window functions? Ça vaut la peine d'essayer. Je ne crois pas qu'il existe de window function qui remplisse exactement ce rôle, mais une approche possible consiste à commencer par écrire la requête qui a un t donné donne le dernier t disponible pour le champ, en demandant à la window function d'aller chercher le max(t) pour un champ donné. La fenêtre par défaut allant jusqu'à la ligne courante, et les entrées nulles de notre jointure gauche étant par définition plus petites que le temps précédent, ça marche plutôt bien:

select 
 m.t, 
 max(a.t) over (order by m.t) as a,
 max(b.t) over (order by m.t) as b,
 max(c.t) over (order by m.t) as c
from history m
left outer join history a on m.t = a.t and a.field = 'a'
left outer join history b on m.t = b.t and b.field = 'b'
left outer join history c on m.t = c.t and c.field = 'c'
order by m.t

tabc
11
212
332
4324
5354
6364
7764
8864
9869
101069
1110119

Une fois que le temps attendu est disponible dans sa colonne, il est trivial d'obtenir la solution en joignant la table ainsi créée avec la table "history", pour chaque champ, afin d'en retrouver la valeur. La requête a pris du poids:

select h.t, a.value, b.value, c.value
from
 (select 
  m.t, 
  max(a.t) over (order by m.t) as a,
  max(b.t) over (order by m.t) as b,
  max(c.t) over (order by m.t) as c
 from history m
 left outer join history a on m.t = a.t and a.field = 'a'
 left outer join history b on m.t = b.t and b.field = 'b'
 left outer join history c on m.t = c.t and c.field = 'c'
 order by m.t) as h
left outer join history a on h.a = a.t and a.field = 'a'
left outer join history b on h.b = b.t and b.field = 'b'
left outer join history c on h.c = c.t and c.field = 'c'
order by t

Alors, est-ce plus rapide que l'approche par sous-requêtes dans le select? Malheureusement, non... Je m'attendais à une grosse différence, mais le planificateur de requête est loin d'être bête, et avec les bons index, les deux approches se valent, avec un léger avantage pour l'approche par sous-requête.

En cadeau bonus, cependant, le plan de notre dernière requête:

samedi 13 octobre 2012

Bug, partie 2

Je l'ai trouvé! Une saleté de race condition qui m'a bien pourri la vie pendant 4 jours. Nous avons en effet dans le code le concept de souscription via callbacks vers des objets, et mon code en regardait la valeur avant de souscrire: résultat, si l'état changeait entre le moment où l'on regarde la valeur, et le moment où l'on souscrit, boum, un trou, et l'on restait sur l'ancienne valeur. Il a suffi d'utiliser une autre primitive qui permettait de souscrire et de récupérer la valeur atomiquement, et tout est rentré dans l'ordre.

Cela me rappelle un autre bug qui m'avait également tenu en haleine pour un bon bout de temps: au final, c'était juste une fonction de hachage qui avait un buffer marqué par erreur comme "static", c'est à dire grosso modo global. Cela rendait la fonction non ré-entrante, et causait donc des erreurs de lookup dans les tables de hachage lors des pics de charge.

dimanche 7 octobre 2012

Bug

Je le traque depuis plusieurs jours. Au début, je n'avais aucune idée de l'animal que je poursuivais, je ne retrouvais que les dégâts causés bien après son passage, de sorte qu'il m'était impossible de l'attraper. Pendant près d'une journée, je me suis complètement fourvoyé sur des traces que je pensais être à lui, mais j'ai fini par me rendre compte que ces traces étaient normales et que c'était juste une fausse piste.

Alors j'ai posé des pièges. Je sait par où il passe, et je voulais tenter d'en apprendre plus. J'ai confectionné, puis tendu mes filets, hier vers 11h. Puis je suis parti rejoindre des collègues à Borough Market, et mangé d'excellentes pâtes au sanglier, suivies d'un bretzel (ohhh...) et d'un morceau de tarte au chocolat (ahhhh!). Je suis revenu, et j'ai regardé si j'avais été chanceux.

Je ne trouvais que quelques crottes insignifiantes, l'animal était bien passé par là mais n'avait rien laissé d'utile. Je vaquais donc à d'autres occupations, et vérifiais vers 16h. Bingo! Non seulement il était passé, mais il m'avais laissé une magnifique trace, que je m'empressais d'étudier.

Hier soir, j'en savais beaucoup plus sur l'animal. Pas suffisamment pour savoir exactement ce que c'était, et donc m'en débarrasser, mais assez pour sérieusement raffiner mes pièges. J'ai bon espoir de l'attraper Lundi.