[pgpool-general: 1634] Re: Master/Slave Mode: Temp table used by view.

[Hoang Thanh Toan]

Dear Tatsuo Ishii,Thanks for your reply, If I have 4 statements in a transaction the following :------------begin-----------------	Function 1	Query 2	Function 3	Query 4-------------- commit-------------------------Pgpool have to make sure that all 4 statements in this transaction to be executed at a node, right?means that either all statements in this transaction be only executed in master or slave. Yes?Please help me to confirm this.Thanks a lot.
> Date: Thu, 11 Apr 2013 18:19:41 +0900
> To: hoangthanhtoan at hotmail.com
> CC: pgpool-general at pgpool.net
> Subject: Re: [pgpool-general: 1597] Master/Slave Mode: Temp table used by view.
> From: ishii at postgresql.org
> 
> There are a few optioins:
> 
> 1) use /*NO LOAD BALANCE*/ comment. Example:
>    /*NO LOAD BALANCE*/SELECT * FROM hrvw_emp_info;
> 
> 2) create a SQL function which invoke hrvw_emp_info.
>    CREATE FUNCTION foo AS ... SELECT * FROM hrvw_emp_info....
>    then mark foo as "volatile". Then SELECT the function instead of hrvw_emp_info.
>    i.e. SELECT * FROM foo();
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese: http://www.sraoss.co.jp
> 
> > Dear Tatsuo,
> > 
> > Thanks for your reply, 
> > 
> > Metadata structure of my db like this:
> > 
> >   hrvw_emp_info (view) --call-->hrvw_employee_trigger
> > (view) --call-->smfn_get_param (function)--->select * from
> > temporary_table.
> > 
> >  
> > 
> > CREATE OR REPLACE VIEW hrvw_emp_info AS 
> > 
> >  SELECT *
> > 
> >    FROM hrvw_employee_trigger t;
> > 
> >  
> > 
> > CREATE OR REPLACE VIEW hrvw_employee_trigger AS 
> > 
> >  SELECT *
> > 
> >    FROM ( SELECT *
> > 
> >            FROM
> > hrtb_employee_infomation
> > 
> >           WHERE
> > smfn_get_param('hrvw_employee_infomation.company_id'::character
> > varying)::numeric = hrtb_employee_infomation.company_id) a;
> > 
> >    
> > 
> >    
> > 
> > CREATE OR REPLACE FUNCTION smfn_get_param(pm_param_name character
> > varying)
> > 
> >   RETURNS character varying AS
> > 
> > $BODY$
> > 
> >   var_param_value varchar2(4000);
> > 
> > begin
> > 
> >                   select
> > param_value into var_param_value
> > 
> >                   from
> > smtb_session_param
> > 
> >                   where
> > param_name = pm_param_name;
> > 
> >                   return
> > var_param_value;
> > 
> >                   exception
> > 
> >                   when
> > others then
> > 
> >                   return
> > null;
> > 
> > end$BODY$
> > 
> >    
> > 
> >    
> > 
> >    CREATE OR REPLACE FUNCTION smpr_set_param(pm_param_name character
> > varying, pm_param_value character varying)
> > 
> >    ..................
> > 
> >                                 CREATE
> > TEMPORARY TABLE smtb_session_param
> > 
> >                                 (
> > 
> >                                   param_name
> > character varying(500) NOT NULL,
> > 
> >                                   param_value
> > character varying(4000),
> > 
> >                                   CONSTRAINT
> >  smtb_session_param_pk PRIMARY KEY (param_name )
> > 
> >                                 )
> > ON COMMIT DELETE ROWS;
> > 
> > .............
> > 
> >    end$BODY$
> > 
> >  
> > 
> >    I have added smpr_set_param,
> > smfn_get_param to black_function_list parameter in pgpool.conf file:
> > 
> >    black_function_list=
> > smpr_set_param,smfn_get_param
> > 
> >    
> > 
> >    But the queries on views
> > hrvw_emp_info, hrvw_employee_trigger still forward to the slave.
> > 
> >  
> > 
> >> Date: Thu, 11 Apr 2013 17:28:42 +0900
> > 
> >> To: hoangthanhtoan at hotmail.com
> > 
> >> CC: pgpool-general at pgpool.net
> > 
> >> Subject: Re: [pgpool-general: 1597] Master/Slave Mode: Temp table used by
> > view.
> > 
> >> From: ishii at postgresql.org
> > 
> >> 
> > 
> >> > Dear all,Please help me.I have a temporary table is used as a literal
> > in View objects.in Master/Slave Mode (Pgpool): how to detect all query on this
> > view, and then transfer these queries to master.Please give me some
> > advices.Thanks a lot. 
> > 
> >> Query examples please.
> > 
> >> --
> > 
> >> Tatsuo Ishii
> > 
> >> SRA OSS, Inc. Japan
> > 
> >> English: http://www.sraoss.co.jp/index_en.php
> > 
> >> Japanese: http://www.sraoss.co.jp
> > 
> > 
> >  		 	   		  
 		 	   		  
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20130418/1d6f36fe/attachment-0001.html>