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

[Hoang Thanh Toan]

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.pgpool.net/pipermail/pgpool-general/attachments/20130411/10cb3aac/attachment.htm>