[pgpool-general-jp: 963] Re: SELECT クエリのレプリケートについて
Toshihiro Kitagawa
kitagawa @ sraoss.co.jp
2011年 8月 2日 (火) 17:03:52 JST
On Mon, 01 Aug 2011 20:25:41 +0900
"GOTO, Daisuke" <gotoh @ m-design.com> wrote:
> 後藤@MDCと申します。
>
> ご回答ありがとうございます。
>
>
> > > 結果、他のINSERTとタイミングがかぶると
> > > kind mismatch among backends
> > > が発生してしまう場合が稀にあります。
>
> 考慮から抜けていたのですが、
> クエリがレプリケートされていないのに
> kind mismatch among backends
> となることは無い、と考えてよろしいのでしょうか。
はい、レプリケートしなければ応答が一つのバックエンドからしか返って
こないため、そもそも不一致(mismatch)という考え自体がありません。
> > > replicate_select は falseなのに
> > > selectのクエリがレプリケートされてしまうのは何故でしょうか。
> > > selectのクエリでもレプリケートされてしまうような条件があるのでしょうか?
> >
> > マニュアルの「ロードバランスの条件について」にあるフローチャートによると、
> > 以下の場合は、SELECTでもレプリケートする仕様になっています。
> >
> > - SELECT INTO
> > - SELECT FOR UPDATE/SHARE
> > - nextval()かsetval()を含む
> >
> > これらには該当しますでしょうか?
> >
>
> いいえ。
> いずれにも該当しません。
>
> > もし該当しなければバグの可能性もありますので、再現調査のため、実際の
> > SELECT文(データ部分は適当な文字列に置き換えても構いません)とそれに
> > 関連するテーブル定義をお知らせ頂けると幸いです。
> >
>
> 文末にサンプルを載せました。
> 抜粋&置換をしたので実際とは異なるのですが。
> 解決の一助となれば幸いです。
サンプルをご提供頂きありがとうございます。
原因は、pgpool-II 2.2.4 の SQL パーサが問題のクエリを解析できないため
でした。
> SELECT
> DEM.code_a,
> DEM.code_b,
> ( case when sum(case when DEM.flag_a = '1' then 1 else 0 end) > 0 then 1 else 0 end +
> case when sum(case when DEM.flag_a = '2' then 1 else 0 end) > 0 then 2 else 0 end +
> case when sum(case when DEM.flag_a = '3' then 1 else 0 end) > 0 then 4 else 0 end
> ) flag_a,
^AS を省略しているのが原因
上記の AS の省略は PostgreSQL 8.4 から可能になっており、pgpool-II 2.2.4
の SQL パーサは PostgreSQL 8.3 のものを使用しているので、syntax error
となります。
syntax error の場合、それが新しい構文の更新クエリの可能性もあるので、
pgpool-II は、念のためそれをレプリケーションする仕様になっています。
したがって、問題のクエリに AS を一つ入れれば解決するはずです。
--
Toshihiro Kitagawa
SRA OSS, Inc. Japan
> > > また、/*insert lock*/のようにコメント等を指定することで、
> > > 明示的にクエリをレプリケートさせないようにする方法などはあるのでしょうか?
> >
> > ありません、逆(レプリケートさせる)はできるのですが…。
> >
>
> そうですか。了解しました。
> 小手先では解決しなさそうですね。
>
> 以下、サンプルの定義とデータ、クエリになります。
>
> --
> --■テーブル1
>
> create sequence dcmnt_exec_seq;
>
> create table dcmnt_exec (
> exec_id bigint not null default nextval('dcmnt_exec_seq')
> , exec_from integer
> , p_data text
> , pdf_binarydata character varying(255)
> , tiff_binarydata character varying(255)
> , exec_status smallint
> , error_code character varying(255)
> , exec_date timestamp without time zone default now()
> , fix_date timestamp without time zone default now()
> , trkbi timestamp without time zone default now()
> , endbi timestamp without time zone default now()
> , flag1 smallint
> , flag2 smallint
> , flag3 smallint
> , del_flag smallint
> , dcmntdir_id bigint
> , login_masterid bigint
> , p_udc text
> , dcmnt_date timestamp without time zone
> );
>
> create index dcmnt_exec_idx1 on dcmnt_exec(exec_id);
> create index dcmnt_exec_idx2 on dcmnt_exec(exec_from);
>
> insert into dcmnt_exec (exec_id,exec_from,p_data,exec_status,error_code,exec_date,fix_date,trkbi,endbi,dcmntdir_id,login_masterid,dcmnt_date) values
> (330960,1,'aaa',5 ,'ERR000000','2009-12-24 09:13:36','2009-12-24 09:16:38','2009-12-24 09:16:38','2009-12-24 09:16:38',1 ,161 ,'2009-12-24 09:16:38')
> ,(620589,1,'bbb',5 ,'ERR000000','2010-03-15 15:55:07','2010-03-15 16:11:28','2010-03-15 16:11:28','2010-03-15 16:11:28',1 ,144 ,'2010-03-15 16:11:28')
> ;
>
> --
> --■テーブル2
>
> create sequence dcmnt_exec_ms_seq;
>
> create table dcmnt_exec_ms (
> exec_ms_id bigint not null default nextval('dcmnt_exec_ms_seq'::regclass)
> ,exec_id bigint
> ,inquirycode character varying(255)
> ,userid character varying(255)
> ,code_a character varying(18)
> ,code_b text
> ,flag_a character varying(255)
> ,flag_b character varying(255)
> ,dcmnt_code character varying(255)
> ,code_p character varying(255)
> ,code_a_1 character varying(16)
> ,code_a_2 character varying(16)
> ,code_a_3 character varying(16)
> ,code_a_4 character varying(16)
> ,code_a_5 character varying(16)
> ,name_p character varying(50)
> ,tname1 character varying(255)
> ,tname2 character varying(255)
> ,tname3 character varying(255)
> ,tname4 character varying(255)
> ,tname5 character varying(255)
> ,data_recycle character varying(255)
> ,trkbi timestamp without time zone default now()
> ,endbi timestamp without time zone default now()
> ,flag1 smallint
> ,flag2 smallint
> ,flag3 smallint
> ,del_flag smallint
> ,flag_c character varying(255)
> ,flag_d character varying(255)
> );
>
> create index dcmnt_exec_ms_idx1 on dcmnt_exec_ms(exec_id);
> create index dcmnt_exec_ms_idx2 on dcmnt_exec_ms(exec_ms_id, exec_id);
> create index dcmnt_exec_ms_idx3 on dcmnt_exec_ms(inquirycode);
> create index dcmnt_exec_ms_idx4 on dcmnt_exec_ms(code_p, code_a_1, code_a_2, code_a_3, code_a_4, code_a_5);
>
> insert into dcmnt_exec_ms (exec_ms_id, exec_id, userid, code_a, code_b, flag_a, flag_b, dcmnt_code, code_p, code_a_1, code_a_2, code_a_3, data_recycle, trkbi, endbi, flag_c, flag_d) values
> (330576,330960 ,'AAAA1111','123456780120000000','123-4' , '1','2','1234','12','345','678','012','2','2009-12-24 09:13:36','2010-04-16 08:33:19','1','0')
> ,(620205,620589 ,'BBBB2222','123456780120000000','5-6-7', '1','1','1234','12','345','678','012','2','2010-03-15 15:55:07','2010-04-16 08:33:19','1','0')
> ;
>
> --
> --■問題のクエリ
>
> SELECT
> DEM.code_a,
> DEM.code_b,
> ( case when sum(case when DEM.flag_a = '1' then 1 else 0 end) > 0 then 1 else 0 end +
> case when sum(case when DEM.flag_a = '2' then 1 else 0 end) > 0 then 2 else 0 end +
> case when sum(case when DEM.flag_a = '3' then 1 else 0 end) > 0 then 4 else 0 end
> ) flag_a,
> DEM.flag_b,
> NULL AS flag_c,
> NULL AS flag_d
> FROM dcmnt_exec AS DE LEFT JOIN (
> SELECT exec_id, code_a, code_b, flag_a, flag_b, flag_c, flag_d
> FROM dcmnt_exec_ms
> WHERE code_a = '123456780120000000' AND data_recycle = '2' AND del_flag IS NULL
> ) DEM ON DE.exec_id = DEM.exec_id
> WHERE DEM.code_a = '123456780120000000' AND DE.exec_status = '5' AND del_flag IS NULL
> GROUP BY DEM.code_a, DEM.code_b, DEM.flag_b
> ORDER BY
> to_number(COALESCE(NULLIF(substring(translate(DEM.code_b, '0123456789', '0123456789') from '[0-9]*'), ''), '0'), '99999999999999999999'),
> to_number(COALESCE(NULLIF(substr(substring(translate(DEM.code_b, '0123456789', '0123456789') from '[0-9]+[^0-9]+[0-9]*'), length(substring(translate(DEM.code_b, '0123456789', '0123456789') from '[0-9]+[^0-9]+'))+1), ''), '0'), '99999999999999999999'),
> to_number(COALESCE(NULLIF(substr(substring(translate(DEM.code_b, '0123456789', '0123456789') from '[0-9]+[^0-9]+[0-9]+[^0-9]+[0-9]*'), length(substring(translate(DEM.code_b, '0123456789', '0123456789') from '[0-9]+[^0-9]+[0-9]+[^0-9]+'))+1), ''), '0'), '99999999999999999999'),
> DEM.flag_b
> ;
>
>
> --
> 後藤 大輔<gotoh @ m-design.com>
> 株式会社エム・ディー・シー
> 〒212-0012 川崎市幸区中幸町3丁目2
> Tel. 044-555-3185 Fax. 044-555-5700
> _______________________________________________
> pgpool-general-jp mailing list
> pgpool-general-jp @ sraoss.jp
> http://www.sraoss.jp/mailman/listinfo/pgpool-general-jp
>
pgpool-general-jp メーリングリストの案内