[pgpool-general-jp: 1119] Re: replication_mode+parallel_mode でエラー
Tatsuo Ishii
ishii ＠ sraoss.co.jp
2012年 11月 14日 (水) 17:26:31 JST
こんにちは。石井です。
お返事が遅くなり申し訳ありません。
パッチの有無に関わらず、現象が発生することを確認しました。
残念ながらパラレルクエリ周りはあまり見切れてなくて、今のところちょっと
わかりません。
# コードもコメントが少なくて難読状態:-)
解析を手伝ってくれる方がいたら歓迎します。
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp
> こんにちは、sho ともうします。
>
> replication_mode と parallel_mode の同時利用を試していた所、
> 次のようなエラーが出ます。
>
> testdb=> select * from rep_tbl left outer join para_tbl using(id);
> ERROR: pgpool2 sql restriction(notice from dblink)
> DETAIL: SELECT "pool_c$0" AS id,"pool_c$1" AS name,"pool_c$2" AS id,"pool_c$3" AS name FROM dblink('host=host01 dbname=testdb port=7999 user=test password=test','SELECT pool_loadbalance("SELECT rep_tbl.id, rep_tbl.name FROM rep_tbl")',false) AS rep
>
> 内部関数の pool_loadbalance が処理されず、そのまま node DB へSQLが投げられている
> ようです。
>
> 以上、よろしくお願いします。
> (あ、別件でパッチ当ててるので、最後に載せときます)
>
> -- 構成
> pgpool-II 3.2.1
> postgresql 9.1.6 : node 2 台
>
> -- pgpool.conf 抜粋
> replication_mode = on
> replicate_select = off
> load_balance_mode = off
> master_slave_mode = off
> parallel_mode = on
> memory_cache_enabled = on
>
> -- systemdb
> CREATE OR REPLACE FUNCTION pgpool_catalog.node2(anyelement)
> RETURNS integer AS $$
> SELECT $1 % 2;
> $$ LANGUAGE sql;
>
> INSERT INTO pgpool_catalog.dist_def VALUES (
> 'testdb','public',
> 'para_tbl',
> 'id',
> ARRAY['id','name'],
> ARRAY['int4','varchar'],
> 'pgpool_catalog.node2'
> );
>
> INSERT INTO pgpool_catalog.replicate_def VALUES (
> 'testdb','public',
> 'rep_tbl',
> ARRAY['id','name'],
> ARRAY['int4','varchar']
> );
>
> -- sample data
> testdb=> create table para_tbl (id int4, name text);
> testdb=> create table rep_tbl (id int4, name text);
> testdb=> insert into para_tbl (id,name) VALUES (1,'para 1');
> testdb=> insert into para_tbl (id,name) VALUES (2,'para 2');
> testdb=> insert into para_tbl (id,name) VALUES (10,'para 10');
> testdb=> insert into rep_tbl (id,name) VALUES (1,'rep 1');
> testdb=> insert into rep_tbl (id,name) VALUES (2,'rep 2');
> testdb=> insert into rep_tbl (id,name) VALUES (20,'rep 20');
>
> -- test SQL
> OK: select * from rep_tbl inner join para_tbl using(id);
> OK: select * from para_tbl inner join rep_tbl using(id);
> OK: select * from para_tbl left outer join rep_tbl using(id);
>
> NG: select * from rep_tbl left outer join para_tbl using(id);
>
> -- log
> 2012-10-18 13:36:07 DEBUG: pid 24424: OneNode_do_command: Query: SELECT "pool_c$0" AS id,"pool_c$1" AS name,"pool_c$2" AS id,"pool_c$3" AS name FROM dblink('host=host01 dbname=testdb port=7999 user=
> test password=test','SELECT pool_loadbalance("SELECT rep_tbl.id, rep_tbl.name FROM rep_tbl")',false) AS rep_tbl("pool_c$0" int4,"pool_c$1" varchar) LEFT OUTER JOIN dblink('host=host01 dbname=testdb por
> t=7999 user=test password=test','SELECT pool_parallel("SELECT para_tbl.id, para_tbl.name FROM para_tbl")',false) AS para_tbl("pool_c$2" int4,"pool_c$3" varchar) ON "pool_c$0" = "pool_c$2"
> (略)
> 2012-10-18 13:36:07 DEBUG: pid 24423: pool_set_query_in_progress: done
> 2012-10-18 13:36:07 DEBUG: pid 24423: can loadbalance_mode x SELECT rep_tbl.id, rep_tbl.name FROM rep_tbl
> 2012-10-18 13:36:07 DEBUG: pid 24423: pool_do_parallel_query: load balancing query: SELECT pool_loadbalance("SELECT rep_tbl.id, rep_tbl.name FROM rep_tbl")
> 2012-10-18 13:36:07 DEBUG: pid 24423: pool_has_insertinto_or_locking_clause: returns 0
> 2012-10-18 13:36:07 DEBUG: pid 24423: non_immutable_function_call_walker: function name: pool_loadbalance
> 2012-10-18 13:36:07 DEBUG: pid 24423: is_immutable_function: relcache created
> 2012-10-18 13:36:07 LOG: pid 24423: DB node id: 0 backend pid: 13452 statement: SELECT count(*) FROM pg_catalog.pg_proc AS p WHERE p.proname = 'pool_loadbalance' AND p.provolatile = 'i'
> 2012-10-18 13:36:07 DEBUG: pid 24423: do_query: extended:0 query:SELECT count(*) FROM pg_catalog.pg_proc AS p WHERE p.proname = 'pool_loadbalance' AND p.provolatile = 'i'
> 2012-10-18 13:36:07 DEBUG: pid 24423: do_query: kind: T
> 2012-10-18 13:36:07 DEBUG: pid 24423: do_query: row description received
> 2012-10-18 13:36:07 DEBUG: pid 24423: num_fileds: 1
> 2012-10-18 13:36:07 DEBUG: pid 24423: do_query: kind: D
> 2012-10-18 13:36:07 DEBUG: pid 24423: do_query: data row received
> 2012-10-18 13:36:07 DEBUG: pid 24423: do_query: kind: C
> 2012-10-18 13:36:07 DEBUG: pid 24423: do_query: Command complete received
> 2012-10-18 13:36:07 DEBUG: pid 24423: do_query: kind: Z
> 2012-10-18 13:36:07 DEBUG: pid 24423: do_query: Ready for query
> 2012-10-18 13:36:07 DEBUG: pid 24423: is_immutable_function: search result:0
> 2012-10-18 13:36:07 DEBUG: pid 24423: pool_has_non_immutable_function_call: 1
> 2012-10-18 13:36:07 DEBUG: pid 24423: function_call_walker: function name: pool_loadbalance
> 2012-10-18 13:36:07 DEBUG: pid 24423: pattern_compare: black_function_list (^nextval$) not matched: pool_loadbalance
> 2012-10-18 13:36:07 DEBUG: pid 24423: pattern_compare: black_function_list (^setval$) not matched: pool_loadbalance
> 2012-10-18 13:36:07 LOG: pid 24423: DB node id: 0 backend pid: 13452 statement: SELECT pool_loadbalance("SELECT rep_tbl.id, rep_tbl.name FROM rep_tbl")
> 2012-10-18 13:36:07 DEBUG: pid 24423: wait_for_query_response: waiting for backend 0 completing the query
> 2012-10-18 13:36:07 LOG: pid 24423: pool_send_and_wait: Error or notice message from backend: : DB node id: 0 backend pid: 13452 statement: SELECT pool_loadbalance("SELECT rep_tbl.id, rep_tbl.name FROM rep_tb
> l") message: column "SELECT rep_tbl.id, rep_tbl.name FROM rep_tbl" does not exist
>
> -- patch
> *** pool_rewrite_outfuncs.c.orig 2012-04-11 17:32:02.000000000 +0900
> --- pool_rewrite_outfuncs.c 2012-10-15 10:34:58.000000000 +0900
> ***************
> *** 2943,2948 ****
> --- 2943,2950 ----
> {
> //delay_string_append_char(message, str, analyze->virtual->table_list[i]);
> //delay_string_append_char(message, str, "ooo.");
> + delay_string_append_char(message, str, analyze->virtual->table_list[i]);
> + delay_string_append_char(message, str, ".");
> if(strcmp(col_name,"\"?column?\""))
> delay_string_append_char(message, str, col_name);
> else
>
>
> -- sho
> _______________________________________________
> pgpool-general-jp mailing list
> pgpool-general-jp ＠ sraoss.jp
> http://www.sraoss.jp/mailman/listinfo/pgpool-general-jp
pgpool-general-jp メーリングリストの案内