PostgreSQL にバグレポートをした話

PostgreSQL

Posted at 2016-12-17

はじめに

みなさんは RDBMS を使っていますか？この記事を読んでいる方であれば使っていることが多いと思います。
RDBMS って、アプリケーション一式の中でも、一番堅牢で、最後の砦的なイメージがありますよね。つまり、最初から RDBMS のバグを疑うという事はあまりなく、だいたいは自分の書いたコードが間違っているという事が多いわけです。
この記事は、そんな RDBMS のバグを発見してしまって、じゃあどうする、と途方に暮れていたところ、コミュニティの協力のもと、バグレポートをしたという話です。以下は詳細です

どういうバグか

ある日、同僚から「なんか集計が合わないんじゃが......」という報告が上がってきました。再現条件つきだったので、試してみると以下のような状態になっていて、確かに集計が合ってない......

まず、適当なテーブルを作成して行を挿入する

temp_values は全部で 3,000,000 件

CREATE TEMP TABLE temp_values (
  id serial,
  target_id integer,
  date date
);
CREATE INDEX index_temp_values_target_id ON temp_values (target_id);

INSERT INTO temp_values (target_id, date)
SELECT id, '2016-04-01'::date + (random() * generate_series(1, 100))::integer
  FROM (SELECT generate_series(1, 30000)) AS sub(id);
-- INSERT 0 3000000

VACUUM ANALYZE temp_values;

CREATE TEMP TABLE temp_targets (
  target_id integer,
  label text
);
INSERT INTO temp_targets(target_id, label)
select t.id, row_number() OVER(PARTITION BY t.id)::text
from (values (14933), (14905), (14931), (14975), (14888), (14891), (14894), (14895), (14892), (14923), (14887), (14890), (14935), (18757), (14964), (14962), (24524), (14936), (17858), (14937), (23071), (14896), (17856), (14906), (14918), (14971), (17020), (14972), (17025), (17026), (14977), (14978), (14897), (14907), (14919), (20832), (14953), (14947), (14955), (14951), (18452), (14954), (21373), (14945), (14949), (20771), (24556), (24418), (26738), (22856), (23542), (24557), (24419), (26440), (22857), (17859), (23543), (14921), (14976), (14946), (14952), (14974), (14909), (14922), (14966), (14941), (14903), (2316), (14938), (2520), (14934), (14942), (14939), (14910), (14924), (14965), (14963), (26443), (26441), (26442), (18462), (18463), (18464), (18465), (18470), (18466), (18467), (18468), (18469), (18453), (18454), (18455), (18456), (18461), (18457), (18458), (18459), (18460), (14898), (14911), (14925), (14912), (14926), (14915), (14929), (21487), (17018), (27184), (23753), (14916), (21488), (17857), (27185), (23754), (14930), (14904), (14917), (20180), (14932), (14913), (14927), (14970), (14959), (14960), (14902), (22855), (14899), (14943), (14944), (14969), (14968), (18668), (14950), (17160), (14914), (14928), (14948), (14956), (14958), (18756), (14957), (14900), (14901), (14908), (14920), (14967), (14889), (14893), (2313), (14940), (25908), (25909), (25912), (18473), (25910), (25911), (16898), (25851), (16899), (16904), (16900), (16901), (16902), (16903), (20772), (17019), (17021), (16905), (16906), (16907), (16908), (16909), (16910), (16911), (17022), (16912), (16913), (18471), (18472), (16922), (21374), (16917), (16914), (17023), (16915), (16916), (21375), (16918), (16919), (16920), (16921), (20773), (17024), (25852), (25853), (25854), (25855), (25856), (26739), (25857), (25858), (25859), (25875), (26444), (25879), (25865), (25864), (25860), (25861), (25862), (25863), (25866), (25867), (25868), (25869), (25870), (25871), (25872), (25873), (25874), (25876), (25877), (25878), (25880), (25881), (25882), (25883), (25884), (26740), (25885), (25886), (25887), (25903), (26445), (25907), (25893), (25892), (25888), (25889), (25890), (25891), (25894), (25895), (25896), (25897), (25898), (25899), (25900), (25901), (25902), (25904), (25905), (25906), (14961), (14933), (14905), (14931), (14975), (14888), (14891), (14894), (14895), (14892), (14923), (14887), (14890), (14935), (18757), (14964), (14962), (24524), (14936), (17858), (14937), (23071), (14896), (17856), (14906), (14918), (14897), (14907), (14919), (20832), (14953), (14947), (14955), (14951), (18452), (14954), (21373), (14945), (14949), (20771), (24556), (24418), (26738), (22856), (23542), (24557), (24419), (26440), (22857), (17859), (23543), (14921), (14946), (14952), (14974), (14909), (14922), (14966), (14941), (14903), (2316), (14938), (2520), (14934), (14942), (14939), (14910), (14924), (14965), (14963), (26443), (26441), (26442), (18462), (18463), (18464), (18465), (18470), (18466), (18467), (18468), (18469), (18453), (18454), (18455), (18456), (18461), (18457), (18458), (18459), (18460), (14898), (14911), (14925), (14912), (14926), (14915), (14929), (21487), (17018), (27184), (23753), (14916), (21488), (17857), (27185), (23754), (14930), (14904), (14917), (20180), (14932), (14913), (14927), (14970), (14959), (14960), (14902), (22855), (14899), (14943), (14944), (14969), (14968), (18668), (14950), (17160), (14914), (14928), (14948), (14956), (14958), (18756), (14957), (14900), (14901), (14908), (14920), (14967), (14889), (14893), (2313), (14940), (14961)) AS t(id);

temp_targets は 254 件です

SELECT COUNT(DISTINCT target_id)
  FROM temp_targets ;
-- 254

254 種類の target_id で IN を使って絞り込みます

target_id ごとに 100 件入っているので、25,400 件あるはずです

SELECT COUNT(DISTINCT temp_values.id)
  FROM temp_values
 WHERE temp_values.target_id IN (14933, 14905, 14931, 14975, 14888, 14891, 14894, 14895, 14892, 14923, 14887, 14890, 14935, 18757, 14964, 14962, 24524, 14936, 17858, 14937, 23071, 14896, 17856, 14906, 14918, 14971, 17020, 14972, 17025, 17026, 14977, 14978, 14897, 14907, 14919, 20832, 14953, 14947, 14955, 14951, 18452, 14954, 21373, 14945, 14949, 20771, 24556, 24418, 26738, 22856, 23542, 24557, 24419, 26440, 22857, 17859, 23543, 14921, 14976, 14946, 14952, 14974, 14909, 14922, 14966, 14941, 14903, 2316, 14938, 2520, 14934, 14942, 14939, 14910, 14924, 14965, 14963, 26443, 26441, 26442, 18462, 18463, 18464, 18465, 18470, 18466, 18467, 18468, 18469, 18453, 18454, 18455, 18456, 18461, 18457, 18458, 18459, 18460, 14898, 14911, 14925, 14912, 14926, 14915, 14929, 21487, 17018, 27184, 23753, 14916, 21488, 17857, 27185, 23754, 14930, 14904, 14917, 20180, 14932, 14913, 14927, 14970, 14959, 14960, 14902, 22855, 14899, 14943, 14944, 14969, 14968, 18668, 14950, 17160, 14914, 14928, 14948, 14956, 14958, 18756, 14957, 14900, 14901, 14908, 14920, 14967, 14889, 14893, 2313, 14940, 25908, 25909, 25912, 18473, 25910, 25911, 16898, 25851, 16899, 16904, 16900, 16901, 16902, 16903, 20772, 17019, 17021, 16905, 16906, 16907, 16908, 16909, 16910, 16911, 17022, 16912, 16913, 18471, 18472, 16922, 21374, 16917, 16914, 17023, 16915, 16916, 21375, 16918, 16919, 16920, 16921, 20773, 17024, 25852, 25853, 25854, 25855, 25856, 26739, 25857, 25858, 25859, 25875, 26444, 25879, 25865, 25864, 25860, 25861, 25862, 25863, 25866, 25867, 25868, 25869, 25870, 25871, 25872, 25873, 25874, 25876, 25877, 25878, 25880, 25881, 25882, 25883, 25884, 26740, 25885, 25886, 25887, 25903, 26445, 25907, 25893, 25892, 25888, 25889, 25890, 25891, 25894, 25895, 25896, 25897, 25898, 25899, 25900, 25901, 25902, 25904, 25905, 25906, 14961, 14933, 14905, 14931, 14975, 14888, 14891, 14894, 14895, 14892, 14923, 14887, 14890, 14935, 18757, 14964, 14962, 24524, 14936, 17858, 14937, 23071, 14896, 17856, 14906, 14918, 14897, 14907, 14919, 20832, 14953, 14947, 14955, 14951, 18452, 14954, 21373, 14945, 14949, 20771, 24556, 24418, 26738, 22856, 23542, 24557, 24419, 26440, 22857, 17859, 23543, 14921, 14946, 14952, 14974, 14909, 14922, 14966, 14941, 14903, 2316, 14938, 2520, 14934, 14942, 14939, 14910, 14924, 14965, 14963, 26443, 26441, 26442, 18462, 18463, 18464, 18465, 18470, 18466, 18467, 18468, 18469, 18453, 18454, 18455, 18456, 18461, 18457, 18458, 18459, 18460, 14898, 14911, 14925, 14912, 14926, 14915, 14929, 21487, 17018, 27184, 23753, 14916, 21488, 17857, 27185, 23754, 14930, 14904, 14917, 20180, 14932, 14913, 14927, 14970, 14959, 14960, 14902, 22855, 14899, 14943, 14944, 14969, 14968, 18668, 14950, 17160, 14914, 14928, 14948, 14956, 14958, 18756, 14957, 14900, 14901, 14908, 14920, 14967, 14889, 14893, 2313, 14940, 14961);
-- 25400

254 種類の target_id が入ったテーブルを JOIN します

これも、やはり 25,400 件になるはずです

SELECT COUNT(DISTINCT temp_values.id)
  FROM temp_values
  JOIN temp_targets USING (target_id);
-- 25400

であれば、WHERE と JOIN を組み合わせても、同じ結果になるはず

SELECT COUNT(DISTINCT temp_values.id)
  FROM temp_values
  JOIN temp_targets USING (target_id)
 WHERE temp_values.target_id IN (14933, 14905, 14931, 14975, 14888, 14891, 14894, 14895, 14892, 14923, 14887, 14890, 14935, 18757, 14964, 14962, 24524, 14936, 17858, 14937, 23071, 14896, 17856, 14906, 14918, 14971, 17020, 14972, 17025, 17026, 14977, 14978, 14897, 14907, 14919, 20832, 14953, 14947, 14955, 14951, 18452, 14954, 21373, 14945, 14949, 20771, 24556, 24418, 26738, 22856, 23542, 24557, 24419, 26440, 22857, 17859, 23543, 14921, 14976, 14946, 14952, 14974, 14909, 14922, 14966, 14941, 14903, 2316, 14938, 2520, 14934, 14942, 14939, 14910, 14924, 14965, 14963, 26443, 26441, 26442, 18462, 18463, 18464, 18465, 18470, 18466, 18467, 18468, 18469, 18453, 18454, 18455, 18456, 18461, 18457, 18458, 18459, 18460, 14898, 14911, 14925, 14912, 14926, 14915, 14929, 21487, 17018, 27184, 23753, 14916, 21488, 17857, 27185, 23754, 14930, 14904, 14917, 20180, 14932, 14913, 14927, 14970, 14959, 14960, 14902, 22855, 14899, 14943, 14944, 14969, 14968, 18668, 14950, 17160, 14914, 14928, 14948, 14956, 14958, 18756, 14957, 14900, 14901, 14908, 14920, 14967, 14889, 14893, 2313, 14940, 25908, 25909, 25912, 18473, 25910, 25911, 16898, 25851, 16899, 16904, 16900, 16901, 16902, 16903, 20772, 17019, 17021, 16905, 16906, 16907, 16908, 16909, 16910, 16911, 17022, 16912, 16913, 18471, 18472, 16922, 21374, 16917, 16914, 17023, 16915, 16916, 21375, 16918, 16919, 16920, 16921, 20773, 17024, 25852, 25853, 25854, 25855, 25856, 26739, 25857, 25858, 25859, 25875, 26444, 25879, 25865, 25864, 25860, 25861, 25862, 25863, 25866, 25867, 25868, 25869, 25870, 25871, 25872, 25873, 25874, 25876, 25877, 25878, 25880, 25881, 25882, 25883, 25884, 26740, 25885, 25886, 25887, 25903, 26445, 25907, 25893, 25892, 25888, 25889, 25890, 25891, 25894, 25895, 25896, 25897, 25898, 25899, 25900, 25901, 25902, 25904, 25905, 25906, 14961, 14933, 14905, 14931, 14975, 14888, 14891, 14894, 14895, 14892, 14923, 14887, 14890, 14935, 18757, 14964, 14962, 24524, 14936, 17858, 14937, 23071, 14896, 17856, 14906, 14918, 14897, 14907, 14919, 20832, 14953, 14947, 14955, 14951, 18452, 14954, 21373, 14945, 14949, 20771, 24556, 24418, 26738, 22856, 23542, 24557, 24419, 26440, 22857, 17859, 23543, 14921, 14946, 14952, 14974, 14909, 14922, 14966, 14941, 14903, 2316, 14938, 2520, 14934, 14942, 14939, 14910, 14924, 14965, 14963, 26443, 26441, 26442, 18462, 18463, 18464, 18465, 18470, 18466, 18467, 18468, 18469, 18453, 18454, 18455, 18456, 18461, 18457, 18458, 18459, 18460, 14898, 14911, 14925, 14912, 14926, 14915, 14929, 21487, 17018, 27184, 23753, 14916, 21488, 17857, 27185, 23754, 14930, 14904, 14917, 20180, 14932, 14913, 14927, 14970, 14959, 14960, 14902, 22855, 14899, 14943, 14944, 14969, 14968, 18668, 14950, 17160, 14914, 14928, 14948, 14956, 14958, 18756, 14957, 14900, 14901, 14908, 14920, 14967, 14889, 14893, 2313, 14940, 14961);
-- EXPECT 25400, but RESULT 25300 on 9.5 and 9.6

が、ダメ！
なぜか、PostgreSQL 9.5.2, 9.5.3, 9.6.0 では 100 件減る！
そんなことってある？

とりあえず

まず、いろんな人から情報を聞きたいところですが、今起きている状況を簡単に試せるように、Docker で環境を作ってみました -> strange_postgresql_9_5
Docker を使えば、いろんなバージョンの PostgreSQL をサクサク試すことができて便利です

Twitter

【緩募】 https://t.co/h225mE1ZiP
PostgreSQL 9.4 と 9.5 で結果が変わってしまう件で死んでます… どなたか、原因がピンと来たら教えて下さい…
— yancya (@yancya) 2016年5月30日

Twitter で緩募したら、一時間くらいで急所を言い当てる人が現れて凄かったです

set enable_mergejoin to off;

SELECT COUNT(DISTINCT temp_values.id)
  FROM temp_values
  JOIN temp_targets USING (target_id)
 WHERE temp_values.target_id IN (14933, 14905, 14931, 14975, 14888, 14891, 14894, 14895, 14892, 14923, 14887, 14890, 14935, 18757, 14964, 14962, 24524, 14936, 17858, 14937, 23071, 14896, 17856, 14906, 14918, 14971, 17020, 14972, 17025, 17026, 14977, 14978, 14897, 14907, 14919, 20832, 14953, 14947, 14955, 14951, 18452, 14954, 21373, 14945, 14949, 20771, 24556, 24418, 26738, 22856, 23542, 24557, 24419, 26440, 22857, 17859, 23543, 14921, 14976, 14946, 14952, 14974, 14909, 14922, 14966, 14941, 14903, 2316, 14938, 2520, 14934, 14942, 14939, 14910, 14924, 14965, 14963, 26443, 26441, 26442, 18462, 18463, 18464, 18465, 18470, 18466, 18467, 18468, 18469, 18453, 18454, 18455, 18456, 18461, 18457, 18458, 18459, 18460, 14898, 14911, 14925, 14912, 14926, 14915, 14929, 21487, 17018, 27184, 23753, 14916, 21488, 17857, 27185, 23754, 14930, 14904, 14917, 20180, 14932, 14913, 14927, 14970, 14959, 14960, 14902, 22855, 14899, 14943, 14944, 14969, 14968, 18668, 14950, 17160, 14914, 14928, 14948, 14956, 14958, 18756, 14957, 14900, 14901, 14908, 14920, 14967, 14889, 14893, 2313, 14940, 25908, 25909, 25912, 18473, 25910, 25911, 16898, 25851, 16899, 16904, 16900, 16901, 16902, 16903, 20772, 17019, 17021, 16905, 16906, 16907, 16908, 16909, 16910, 16911, 17022, 16912, 16913, 18471, 18472, 16922, 21374, 16917, 16914, 17023, 16915, 16916, 21375, 16918, 16919, 16920, 16921, 20773, 17024, 25852, 25853, 25854, 25855, 25856, 26739, 25857, 25858, 25859, 25875, 26444, 25879, 25865, 25864, 25860, 25861, 25862, 25863, 25866, 25867, 25868, 25869, 25870, 25871, 25872, 25873, 25874, 25876, 25877, 25878, 25880, 25881, 25882, 25883, 25884, 26740, 25885, 25886, 25887, 25903, 26445, 25907, 25893, 25892, 25888, 25889, 25890, 25891, 25894, 25895, 25896, 25897, 25898, 25899, 25900, 25901, 25902, 25904, 25905, 25906, 14961, 14933, 14905, 14931, 14975, 14888, 14891, 14894, 14895, 14892, 14923, 14887, 14890, 14935, 18757, 14964, 14962, 24524, 14936, 17858, 14937, 23071, 14896, 17856, 14906, 14918, 14897, 14907, 14919, 20832, 14953, 14947, 14955, 14951, 18452, 14954, 21373, 14945, 14949, 20771, 24556, 24418, 26738, 22856, 23542, 24557, 24419, 26440, 22857, 17859, 23543, 14921, 14946, 14952, 14974, 14909, 14922, 14966, 14941, 14903, 2316, 14938, 2520, 14934, 14942, 14939, 14910, 14924, 14965, 14963, 26443, 26441, 26442, 18462, 18463, 18464, 18465, 18470, 18466, 18467, 18468, 18469, 18453, 18454, 18455, 18456, 18461, 18457, 18458, 18459, 18460, 14898, 14911, 14925, 14912, 14926, 14915, 14929, 21487, 17018, 27184, 23753, 14916, 21488, 17857, 27185, 23754, 14930, 14904, 14917, 20180, 14932, 14913, 14927, 14970, 14959, 14960, 14902, 22855, 14899, 14943, 14944, 14969, 14968, 18668, 14950, 17160, 14914, 14928, 14948, 14956, 14958, 18756, 14957, 14900, 14901, 14908, 14920, 14967, 14889, 14893, 2313, 14940, 14961);
-- 25400