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