[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 メーリングリストの案内