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

[Tatsuo Ishii]

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
> 
> 
>