(JP) VIEW間のJOIN操作を行うSQLでAssertionFailed

[taiki-k]

psql上からVIEW間でJOINを行うSQLを実行したところ、AssertionFailedとなりました。 Pentaho CEによる性能測定の準備のために、使用するテーブルのデータをtcacheに載せる目的での実行です。

• バックトレース

#0  0x0000003cdbc32925 in raise () from /lib64/libc.so.6
#1  0x0000003cdbc34105 in abort () from /lib64/libc.so.6
#2  0x0000000000937785 in ExceptionalCondition (conditionName=0xac91b0 "!(!bms_overlap(joinrel->relids, required_outer))",
    errorType=0xac8f7a "FailedAssertion", fileName=0xac8f70 "relnode.c", lineNumber=863) at assert.c:54
#3  0x0000000000745f02 in get_joinrel_parampathinfo (root=0x7f1d2a713060, joinrel=0x7f1d2a613018, outer_path=0x7f1d2a5c8058,
    inner_path=0x7f1d2a5c89f8, sjinfo=0x7fffef282980, required_outer=0x7f1d2a6142e0, restrict_clauses=0x7fffef282528) at relnode.c:863
#4  0x00007f2133f16106 in gpuhashjoin_add_path (root=0x7f1d2a713060, joinrel=0x7f1d2a613018, jointype=JOIN_INNER,
    core_workspace=0x7fffef2826d0, sjinfo=0x7fffef282980, semifactors=0x7fffef2828d0, outer_path=0x7f1d2a5c8058,
    inner_path=0x7f1d2a5c89f8, restrict_clauses=0x7f1d2a613588, required_outer=0x7f1d2a6142e0, hashclauses=0x7f1d2a614248)
    at gpuhashjoin.c:716
#5  0x0000000000706569 in try_hashjoin_path (root=0x7f1d2a713060, joinrel=0x7f1d2a613018, jointype=JOIN_INNER, sjinfo=0x7fffef282980,
    semifactors=0x7fffef2828d0, param_source_rels=0x7f1d2a6138b0, extra_lateral_rels=0x0, outer_path=0x7f1d2a5c8058,
    inner_path=0x7f1d2a5c89f8, restrict_clauses=0x7f1d2a613588, hashclauses=0x7f1d2a614248) at joinpath.c:509
#6  0x0000000000707976 in hash_inner_and_outer (root=0x7f1d2a713060, joinrel=0x7f1d2a613018, outerrel=0x7f1d2a6e6ab0,
    innerrel=0x7f1d2a6e7028, restrictlist=0x7f1d2a613588, jointype=JOIN_INNER, sjinfo=0x7fffef282980, semifactors=0x7fffef2828d0,
    param_source_rels=0x7f1d2a6138b0, extra_lateral_rels=0x0) at joinpath.c:1352
#7  0x0000000000705e21 in add_paths_to_joinrel (root=0x7f1d2a713060, joinrel=0x7f1d2a613018, outerrel=0x7f1d2a6e6ab0,
    innerrel=0x7f1d2a6e7028, jointype=JOIN_INNER, sjinfo=0x7fffef282980, restrictlist=0x7f1d2a613588) at joinpath.c:260
#8  0x00000000007089f7 in make_join_rel (root=0x7f1d2a713060, rel1=0x7f1d2a6e6ab0, rel2=0x7f1d2a6e7028) at joinrels.c:674
#9  0x00000000007080a1 in make_rels_by_clause_joins (root=0x7f1d2a713060, old_rel=0x7f1d2a6e6ab0, other_rels=0x7f1d2a613250)
    at joinrels.c:274
#10 0x0000000000707d40 in join_search_one_level (root=0x7f1d2a713060, level=2) at joinrels.c:96
#11 0x00000000006f2e43 in standard_join_search (root=0x7f1d2a713060, levels_needed=3, initial_rels=0x7f1d2a613218) at allpaths.c:1632
#12 0x00000000006f2d94 in make_rel_from_joinlist (root=0x7f1d2a713060, joinlist=0x7f1d2a6ec0f8) at allpaths.c:1563
#13 0x00000000006f2c9c in make_rel_from_joinlist (root=0x7f1d2a713060, joinlist=0x7f1d2a6ed770) at allpaths.c:1528
#14 0x00000000006f0a31 in make_one_rel (root=0x7f1d2a713060, joinlist=0x7f1d2a6ed770) at allpaths.c:159
#15 0x000000000071a51e in query_planner (root=0x7f1d2a713060, tlist=0x7f1d2a6de410, qp_callback=0x71e415 <standard_qp_callback>,
    qp_extra=0x7fffef282da0) at planmain.c:236
#16 0x000000000071c528 in grouping_planner (root=0x7f1d2a713060, tuple_fraction=0) at planner.c:1288
#17 0x000000000071b345 in subquery_planner (glob=0x1c57480, parse=0x1c57090, parent_root=0x0, hasRecursion=0 '\000',
    tuple_fraction=0, subroot=0x7fffef283098) at planner.c:573
#18 0x000000000071a844 in standard_planner (parse=0x1c57090, cursorOptions=0, boundParams=0x0) at planner.c:210
#19 0x00007f2133eff32e in pgstrom_grafter_entrypoint (parse=0x1c57090, cursorOptions=0, boundParams=0x0) at grafter.c:129
#20 0x000000000071a665 in planner (parse=0x1c57090, cursorOptions=0, boundParams=0x0) at planner.c:137
#21 0x00000000007e42d2 in pg_plan_query (querytree=0x1c57090, cursorOptions=0, boundParams=0x0) at postgres.c:750
#22 0x00000000007e43a8 in pg_plan_queries (querytrees=0x7f1d2a710040, cursorOptions=0, boundParams=0x0) at postgres.c:809
#23 0x00000000007e46ca in exec_simple_query (
    query_string=0x1c1ab38 "select\n  F.no,\n  F.time,\n  F.member,\n  C.name as member_name,\n  C.birthday,\n  C.gender,\n  C.city_no as c_city_no,\n  C.city_name as c_city_name,\n  C.pref_no as c_pref_no,\n  C.pref_name as c_pref_name,\n"...) at postgres.c:974
#24 0x00000000007e8fb2 in PostgresMain (argc=1, argv=0x1baa678, dbname=0x1baa4d8 "pentaho_demo", username=0x1baa4b8 "tkondo")
    at postgres.c:4010
#25 0x0000000000762df4 in BackendRun (port=0x1bd6230) at postmaster.c:4113
#26 0x00000000007624b5 in BackendStartup (port=0x1bd6230) at postmaster.c:3787
#27 0x000000000075e8f9 in ServerLoop () at postmaster.c:1566
#28 0x000000000075df57 in PostmasterMain (argc=1, argv=0x1ba9470) at postmaster.c:1219
#29 0x00000000006ae4ba in main (argc=1, argv=0x1ba9470) at main.c:219

• 実行したSQL

select
  F.no,
  F.time,
  F.member,
  C.name as member_name,
  C.birthday,
  C.gender,
  C.city_no as c_city_no,
  C.city_name as c_city_name,
  C.pref_no as c_pref_no,
  C.pref_name as c_pref_name,
  A.age as c_age,
  F.store,
  S.name as store_name,
  S.city_no as s_city_no,
  S.city_name as s_city_name,
  S.pref_no as s_pref_no,
  S.pref_name as s_pref_name,
  F.product,
  P.name as product_name,
  P.price,
  P.category_s,
  P.category_s_name,
  P.category_l,
  P.category_l_name,
  F.count,
  F.campaign,
  CP.name as cp_name,
  CP.discount
 FROM
  fact_sales F
   inner join dim_store S on F.store=S.id
   inner join dim_product P on F.product=P.id
   left outer join dim_customer C on F.member=C.id
   left outer join dim_customer_age A on (F.member=A.id AND F.time::date=A.date)
   left outer join campaign CP on F.campaign=CP.id;

• 対象のVIEW

CREATE VIEW fact_sales AS
 SELECT S.no, S.time, S.member, S.store, D.product, D.count, S.campaign
  FROM sales S INNER JOIN sales_details D ON S.no = D.no;

CREATE VIEW dim_store AS
 SELECT
  store.id,
  store.name,
  city.no AS city_no,
  city.name AS city_name,
  pref.no AS pref_no,
  pref.name AS pref_name
   FROM store INNER JOIN city ON store.city = city.no
    INNER JOIN pref ON city.pref = pref.no;

CREATE VIEW dim_customer AS
 SELECT
  member_customer.id,
  (member_customer.first_name||' '||member_customer.last_name)::text as name,
  birthday,
  gender,
  city.no AS city_no,
  city.name AS city_name,
  pref.no AS pref_no,
  pref.name AS pref_name
  FROM
   member_customer INNER JOIN city ON member_customer.city = city.no
    INNER JOIN pref ON city.pref = pref.no;

CREATE VIEW dim_customer_age AS
 SELECT
  DISTINCT ON (id, date)
   member_customer.id,
   sales.time::date as date,
   date_part('year', age(sales.time::date, member_customer.birthday))::int as age
 FROM
  sales INNER JOIN member_customer ON sales.member = member_customer.id;

CREATE VIEW dim_product AS
 SELECT
  P.id,
  P.name,
  p.price,
  CS.category AS category_s,
  CS.name AS category_s_name,
  CL.category AS category_l,
  CL.name AS category_l_name
   FROM
    product_master P INNER JOIN category_small CS ON P.category = CS.category
                     INNER JOIN category_large CL ON CS.super = CL.category;

• 対象のテーブル

pentaho_demo=# \d campaign
    Table "public.campaign"
  Column  |  Type   | Modifiers
----------+---------+-----------
 id       | integer | not null
 name     | text    | not null
 discount | integer | not null
Indexes:
    "campaign_pkey" PRIMARY KEY, btree (id)

pentaho_demo=# \d category_large
  Table "public.category_large"
  Column  |   Type   | Modifiers
----------+----------+-----------
 category | smallint | not null
 name     | text     | not null
Indexes:
    "category_large_pkey" PRIMARY KEY, btree (category)

pentaho_demo=# \d category_small
  Table "public.category_small"
  Column  |   Type   | Modifiers
----------+----------+-----------
 category | smallint | not null
 name     | text     | not null
 super    | smallint | not null
Indexes:
    "category_small_pkey" PRIMARY KEY, btree (category)

pentaho_demo=# \d product_master
  Table "public.product_master"
  Column  |   Type   | Modifiers
----------+----------+-----------
 id       | smallint | not null
 name     | text     | not null
 price    | bigint   | not null
 category | smallint | not null
Indexes:
    "product_master_pkey" PRIMARY KEY, btree (id)

pentaho_demo=# \d member_customer
  Table "public.member_customer"
   Column   |   Type   | Modifiers
------------+----------+-----------
 id         | smallint | not null
 last_name  | text     | not null
 first_name | text     | not null
 city       | smallint | not null
 birthday   | date     | not null
 gender     | "char"   | not null
Indexes:
    "member_customer_pkey" PRIMARY KEY, btree (id)

pentaho_demo=# \d store
     Table "public.store"
 Column |   Type   | Modifiers
--------+----------+-----------
 id     | smallint | not null
 name   | text     | not null
 city   | smallint | not null
Indexes:
    "store_pkey" PRIMARY KEY, btree (id)

pentaho_demo=# \d city
      Table "public.city"
 Column |   Type   | Modifiers
--------+----------+-----------
 no     | smallint | not null
 pref   | smallint | not null
 name   | text     | not null
Indexes:
    "city_pkey" PRIMARY KEY, btree (no)

pentaho_demo=# \d pref
      Table "public.pref"
 Column |   Type   | Modifiers
--------+----------+-----------
 no     | smallint | not null
 name   | text     | not null
Indexes:
    "pref_pkey" PRIMARY KEY, btree (no)

pentaho_demo=# \d sales
                Table "public.sales"
  Column  |            Type             | Modifiers
----------+-----------------------------+-----------
 no       | bigint                      | not null
 time     | timestamp without time zone | not null
 member   | smallint                    |
 store    | smallint                    | not null
 campaign | integer                     |
Indexes:
    "sales_pkey" PRIMARY KEY, btree (no)

pentaho_demo=# \d sales_details
  Table "public.sales_details"
 Column  |   Type   | Modifiers
---------+----------+-----------
 no      | bigint   | not null
 product | smallint | not null
 count   | integer  | not null
Indexes:
    "sales_details_pkey" PRIMARY KEY, btree (no, product)

[taiki-k]

この時のcoreファイルは、以下にbzip2圧縮して置いてありますので、必要であればご利用ください。

/home/tkondo/coding/postgres/pg_strom_test/core/core.502.2014-08-05_20-25.bz2

[kaigai]

本体側の get_joinrel_parampathinfo 内の Assert() で落ちていますが、 よくよくコードパスを眺めると、引数の required_outer オブジェクトが、 この関数の呼び出し前に解放されている（事がある）のが分かりました。

実際、required_outer にはメチャクチャな値が入っています。 (gdb) p *required_outer $2 = {nwords = 2139062143, words = {2139062143}}

PG-Strom側を呼び出すエントリポイントを少し動かしましたので、 PostgreSQL 本体側を最新版にリフレッシュして試してもらえますか？

[kaigai]

コードベースが非常に古いので、一旦クローズ扱いとします。