<div dir="ltr">><span style="font-size:13.1999998092651px;line-height:19.7999992370605px">So if you use the function</span><br style="font-size:13.1999998092651px;line-height:19.7999992370605px"><span style="font-size:13.1999998092651px;line-height:19.7999992370605px">by not using CURSOR it will be load balanced unless you register it in</span><br style="font-size:13.1999998092651px;line-height:19.7999992370605px"><span style="font-size:13.1999998092651px;line-height:19.7999992370605px">the black function list.</span><div><br></div><div>I thought that functions are not balanced by default:</div><div><br></div><div><span style="font-size:13.1999998092651px;line-height:19.7999992370605px"><a href="http://pgpool.projects.pgfoundry.org/pgpool-II/doc/pgpool-en.html">http://pgpool.projects.pgfoundry.org/pgpool-II/doc/pgpool-en.html</a></span><br></div><div><dt style="font-weight:bold;color:rgb(0,0,0);font-family:Verdana,Arial,Helvetica,sans-serif;font-size:12px;line-height:normal">white_function_list</dt><dd style="color:rgb(0,0,0);font-family:Verdana,Arial,Helvetica,sans-serif;font-size:12px;line-height:normal"><p style="padding-left:15px;padding-right:15px;margin-left:25px;margin-right:25px">Specify a comma separated list of function names that do not update the database. SELECTs using <b>functions not specified in this list</b> are neither load balanced, nor replicated if in replication mode. In master slave mode, such SELECTs <b>are sent to master (primary) only</b>.</p><p style="padding-left:15px;padding-right:15px;margin-left:25px;margin-right:25px"><br></p></dd></div></div><br><div class="gmail_quote">чт, 30 апр. 2015 г. в 11:28, Tatsuo Ishii <<a href="mailto:ishii@postgresql.org">ishii@postgresql.org</a>>:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">> Hi, Tatsuo!<br>
> Sorry, but I still dont understand. Stored function is not balanced, it<br>
> executes on master only. Why should we add it to black list?<br>
<br>
The reason why the SELECT (and FETCH) is not load balanced is, it uses<br>
cursor, not because of the stored function. So if you use the function<br>
by not using CURSOR it will be load balanced unless you register it in<br>
the black function list.<br>
<br>
> The problem is with next statement: plain select from table, no functions<br>
> used.<br>
> If we add function to black list, the next select will not be balanced too?<br>
<br>
I thought so, but was wrong. The decision is made by calling<br>
is_select_query(node, query)), which essentially regards a statement<br>
as SELECT if it looks like a SELECT. i.e. not checking if it calls<br>
writing functions. This seems a bug and I am going to discuss with<br>
other developers.<br>
<br>
Best regards,<br>
--<br>
Tatsuo Ishii<br>
SRA OSS, Inc. Japan<br>
English: <a href="http://www.sraoss.co.jp/index_en.php" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>
Japanese:<a href="http://www.sraoss.co.jp" target="_blank">http://www.sraoss.co.jp</a><br>
<br>
> чт, 30 апр. 2015, 9:10, Tatsuo Ishii <<a href="mailto:ishii@postgresql.org" target="_blank">ishii@postgresql.org</a>>:<br>
><br>
>> > Hi, Tatsuo!<br>
>> > Thank you for answer!<br>
>> > It is good that function is not balanced - it writes to database. But the<br>
>> > next statement in transaction (select after function) is balanced. Is it<br>
>> > correct?<br>
>><br>
>> True.<br>
>><br>
>> > We do not want select to be balanced. And we expected it not to be<br>
>> balanced<br>
>> > to slave, because it sits next to writing statement and in same explicit<br>
>> > transaction.<br>
>><br>
>> You really should use black_function_list (or white_function_list).<br>
>><br>
>> > With best regards, Sergey Melekhin<br>
>> ><br>
>> > вт, 28 апр. 2015 г. в 19:03, Tatsuo Ishii <<a href="mailto:ishii@postgresql.org" target="_blank">ishii@postgresql.org</a>>:<br>
>> ><br>
>> >> Thanks for the test case. The reason why your function is not load<br>
>> >> balanced is, you are using cursor statement. Currently pgpool-II does<br>
>> >> not load balance if cursor is used. This is because the cursor<br>
>> >> statement may use DML, which will raise problem if the cursor<br>
>> >> statement is sent to standby.<br>
>> >><br>
>> >> Best regards,<br>
>> >> --<br>
>> >> Tatsuo Ishii<br>
>> >> SRA OSS, Inc. Japan<br>
>> >> English: <a href="http://www.sraoss.co.jp/index_en.php" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>
>> >> Japanese:<a href="http://www.sraoss.co.jp" target="_blank">http://www.sraoss.co.jp</a><br>
>> >><br>
>> >> > Hi!<br>
>> >> > Here is test case that fails on slow replication.<br>
>> >> ><br>
>> >> > чт, 23 апр. 2015 г. в 17:16, Tatsuo Ishii <<a href="mailto:ishii@postgresql.org" target="_blank">ishii@postgresql.org</a>>:<br>
>> >> ><br>
>> >> >> > Our stored function is not balanced. But next select in the same<br>
>> >> >><br>
>> >> >> Could you show me a concrete example? I hardly believe that SELECT<br>
>> >> >> foo() or SELECT * FROM foo() is not load balanced if neither white<br>
>> and<br>
>> >> >> black function lists are not specified.<br>
>> >> >><br>
>> >> >> > transaction is balanced. Documentation, if i understand it<br>
>> correctly,<br>
>> >> >> > states that it should not be balanced. I think it should be<br>
>> clarified.<br>
>> >> >> ><br>
>> >> >> > <a href="http://www.pgpool.net/docs/latest/pgpool-en.html" target="_blank">http://www.pgpool.net/docs/latest/pgpool-en.html</a><br>
>> >> >> ><br>
>> >> >> > For a query to be load balanced, all the following requirements<br>
>> must<br>
>> >> be<br>
>> >> >> met:<br>
>> >> >> ><br>
>> >> >> > - PostgreSQL version 7.4 or later<br>
>> >> >> > - *the query must not be in an explicitly declared transaction<br>
>> >> (i.e.<br>
>> >> >> not<br>
>> >> >> > in a BEGIN ~ END block)*<br>
>> >> >><br>
>> >> >> This is simply wrong. Sorry for the outdated info. I will fix it.<br>
>> >> >><br>
>> >> >> > So query in explicitly declared transaction will not be balanced.<br>
>> But<br>
>> >> >> then<br>
>> >> >> > in the stame doc:<br>
>> >> >> ><br>
>> >> >> > <a href="http://www.pgpool.net/docs/latest/pgpool-en.html" target="_blank">http://www.pgpool.net/docs/latest/pgpool-en.html</a><br>
>> >> >> > In an explicit transaction:Transaction starting commands such as<br>
>> BEGIN<br>
>> >> >> are<br>
>> >> >> > sent to the primary node.Following SELECT and some other queries<br>
>> that<br>
>> >> can<br>
>> >> >> > be sent to both primary or standby are executed in the transaction<br>
>> or<br>
>> >> on<br>
>> >> >> > the standby node.Commands which cannot be executed on the standby<br>
>> >> such as<br>
>> >> >> > INSERT are sent to the primary. After one of these commands, even<br>
>> >> SELECTs<br>
>> >> >> > are sent to the primary node, This is because these SELECTs might<br>
>> >> want to<br>
>> >> >> > see the result of an INSERT immediately. This behavior continues<br>
>> until<br>
>> >> >> the<br>
>> >> >> > transaction closes or aborts.<br>
>> >> >> ><br>
>> >> >> > This states that after any writing in transaction all statements<br>
>> >> should<br>
>> >> >> not<br>
>> >> >> > be balanced. This contradicts first part, which can be understood<br>
>> as<br>
>> >> if<br>
>> >> >> > transactions are not balanced at all.<br>
>> >> >><br>
>> >> >> Correct.<br>
>> >> >><br>
>> >> >> > ср, 22 апр. 2015 г. в 18:24, Tatsuo Ishii <<a href="mailto:ishii@postgresql.org" target="_blank">ishii@postgresql.org</a>>:<br>
>> >> >> ><br>
>> >> >> >> pgpool-II has no idea which is a stored function or not regarding<br>
>> >> load<br>
>> >> >> >> balancing: all functions are treated as same. So "SELECT<br>
>> >> upper('foo')"<br>
>> >> >> >> is load balanced if white and black function list is empty.<br>
>> >> >> >><br>
>> >> >> >> BTW if you do not want to load balance particular SELECT, you can<br>
>> add<br>
>> >> >> >> "/*NO LOAD BALANCE*/" to the SELECT statement. See the manual for<br>
>> >> more<br>
>> >> >> >> details.<br>
>> >> >> >><br>
>> >> >> >> Best regards,<br>
>> >> >> >> --<br>
>> >> >> >> Tatsuo Ishii<br>
>> >> >> >> SRA OSS, Inc. Japan<br>
>> >> >> >> English: <a href="http://www.sraoss.co.jp/index_en.php" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>
>> >> >> >> Japanese:<a href="http://www.sraoss.co.jp" target="_blank">http://www.sraoss.co.jp</a><br>
>> >> >> >><br>
>> >> >> >> > Hi, Lachezar!<br>
>> >> >> >> > The function itself is not balanced, it would fail on read only<br>
>> >> node<br>
>> >> >> >> > (slave) but it does not (by default stored functions are not<br>
>> >> balanced<br>
>> >> >> >> even<br>
>> >> >> >> > if they are not explicitly listed in black list). Problem is<br>
>> with<br>
>> >> next<br>
>> >> >> >> > statement in this transaction, which is select. And we want this<br>
>> >> >> select<br>
>> >> >> >> to<br>
>> >> >> >> > be run on master.<br>
>> >> >> >> > And looking in documentation I think it should be run on master.<br>
>> >> >> >> ><br>
>> >> >> >> > вт, 21 апр. 2015 г. в 18:26, Lachezar Dobrev <<br>
>> <a href="mailto:l.dobrev@gmail.com" target="_blank">l.dobrev@gmail.com</a>>:<br>
>> >> >> >> ><br>
>> >> >> >> >> Non-authority response:<br>
>> >> >> >> >> You might want to use a white-list[1] or black-list[2] of<br>
>> >> functions<br>
>> >> >> >> >> to inform the PgPool which functions are suitable for<br>
>> >> distributing,<br>
>> >> >> or<br>
>> >> >> >> >> which are not suitable. Documentation specifies that you can<br>
>> use<br>
>> >> >> >> >> either, but not both. I suppose adding your functions to the<br>
>> >> >> >> >> black_function_list would be easier. Not sure if that will help<br>
>> >> >> >> >> though. My understanding was, that everything in a transaction<br>
>> >> >> (BEGIN;<br>
>> >> >> >> >> /* EVERYTHING; */ END;) would be sent to the master.<br>
>> >> >> >> >><br>
>> >> >> >> >> [1]<br>
>> >> >> >><br>
>> <a href="http://www.pgpool.net/docs/latest/pgpool-en.html#WHITE_FUNCTION_LIST" target="_blank">http://www.pgpool.net/docs/latest/pgpool-en.html#WHITE_FUNCTION_LIST</a><br>
>> >> >> >> >> [2]<br>
>> >> >> >><br>
>> <a href="http://www.pgpool.net/docs/latest/pgpool-en.html#BLACK_FUNCTION_LIST" target="_blank">http://www.pgpool.net/docs/latest/pgpool-en.html#BLACK_FUNCTION_LIST</a><br>
>> >> >> >> >><br>
>> >> >> >> >> 2015-04-21 5:55 GMT+03:00 Сергей Мелехин <<a href="mailto:cpro29a@gmail.com" target="_blank">cpro29a@gmail.com</a>>:<br>
>> >> >> >> >> > There is another part in docs:<br>
>> >> >> >> >> ><br>
>> >> >> >> >> > <a href="http://www.pgpool.net/docs/latest/pgpool-en.html" target="_blank">http://www.pgpool.net/docs/latest/pgpool-en.html</a><br>
>> >> >> >> >> > In an explicit transaction:Transaction starting commands<br>
>> such as<br>
>> >> >> BEGIN<br>
>> >> >> >> >> are<br>
>> >> >> >> >> > sent to the primary node.Following SELECT and some other<br>
>> queries<br>
>> >> >> that<br>
>> >> >> >> >> can be<br>
>> >> >> >> >> > sent to both primary or standby are executed in the<br>
>> transaction<br>
>> >> or<br>
>> >> >> on<br>
>> >> >> >> the<br>
>> >> >> >> >> > standby node.Commands which cannot be executed on the standby<br>
>> >> such<br>
>> >> >> as<br>
>> >> >> >> >> INSERT<br>
>> >> >> >> >> > are sent to the primary. After one of these commands, even<br>
>> >> SELECTs<br>
>> >> >> are<br>
>> >> >> >> >> sent<br>
>> >> >> >> >> > to the primary node, This is because these SELECTs might<br>
>> want to<br>
>> >> >> see<br>
>> >> >> >> the<br>
>> >> >> >> >> > result of an INSERT immediately. This behavior continues<br>
>> until<br>
>> >> the<br>
>> >> >> >> >> > transaction closes or aborts.<br>
>> >> >> >> >> ><br>
>> >> >> >> >> > It looks like pgpool treats all stored functions as non<br>
>> writing<br>
>> >> in<br>
>> >> >> >> this<br>
>> >> >> >> >> > scenario.<br>
>> >> >> >> >> ><br>
>> >> >> >> >> > вт, 21 апр. 2015 г. в 12:05, Сергей Мелехин <<br>
>> <a href="mailto:cpro29a@gmail.com" target="_blank">cpro29a@gmail.com</a><br>
>> >> >:<br>
>> >> >> >> >> ><br>
>> >> >> >> >> >> Hi!<br>
>> >> >> >> >> >> Our test server is relatively slow, and there are some lags<br>
>> in<br>
>> >> >> >> >> replication<br>
>> >> >> >> >> >> between master and slave sometimes. We are using pgpool<br>
>> 3.3.4<br>
>> >> in<br>
>> >> >> load<br>
>> >> >> >> >> >> balancing mode to mimic our production environment.<br>
>> >> >> >> >> >> Some unit tests are making some changes in database (calling<br>
>> >> >> stored<br>
>> >> >> >> >> >> functions) and immediately check them issuing select<br>
>> queries.<br>
>> >> >> >> Sometimes<br>
>> >> >> >> >> they<br>
>> >> >> >> >> >> fail not finding records they have just inserted.<br>
>> >> >> >> >> >> Judging by this verse in documentation:<br>
>> >> >> >> >> >><br>
>> >> >> >> >> >> For a query to be load balanced, all the following<br>
>> requirements<br>
>> >> >> must<br>
>> >> >> >> be<br>
>> >> >> >> >> >> met:<br>
>> >> >> >> >> >><br>
>> >> >> >> >> >> PostgreSQL version 7.4 or later<br>
>> >> >> >> >> >> the query must not be in an explicitly declared transaction<br>
>> >> (i.e.<br>
>> >> >> not<br>
>> >> >> >> >> in a<br>
>> >> >> >> >> >> BEGIN ~ END block)<br>
>> >> >> >> >> >><br>
>> >> >> >> >> >> we decided that putting whole test in transaction block will<br>
>> >> avoid<br>
>> >> >> >> load<br>
>> >> >> >> >> >> balancing for such test, but it looks like that no matter<br>
>> >> being in<br>
>> >> >> >> >> >> transaction, selects are replicated to slave and dont find<br>
>> >> desired<br>
>> >> >> >> data<br>
>> >> >> >> >> >> because of replication lags. This errors are present when we<br>
>> >> use<br>
>> >> >> >> stored<br>
>> >> >> >> >> >> functions, when we use explicit DML, selects inside<br>
>> transaction<br>
>> >> >> are<br>
>> >> >> >> not<br>
>> >> >> >> >> >> replicated. Function names are not included in white or<br>
>> black<br>
>> >> >> list in<br>
>> >> >> >> >> >> pgpool.conf.<br>
>> >> >> >> >> >><br>
>> >> >> >> >> >> Is it normal behaviour, or is it a bug?<br>
>> >> >> >> >> >><br>
>> >> >> >> >> >> I include simple test, it fails in our slow replication<br>
>> >> >> environment.<br>
>> >> >> >> >> >> You'll need python3 and psycopg2 to run it. And there is db<br>
>> >> >> >> connection<br>
>> >> >> >> >> >> string constant "DB" in the beginning of script you'll have<br>
>> to<br>
>> >> >> >> change.<br>
>> >> >> >> >> >><br>
>> >> >> >> >> >> Thank you for your work!<br>
>> >> >> >> >> >> Sergey Melekhin<br>
>> >> >> >> >> ><br>
>> >> >> >> >> ><br>
>> >> >> >> >> > _______________________________________________<br>
>> >> >> >> >> > pgpool-general mailing list<br>
>> >> >> >> >> > <a href="mailto:pgpool-general@pgpool.net" target="_blank">pgpool-general@pgpool.net</a><br>
>> >> >> >> >> > <a href="http://www.pgpool.net/mailman/listinfo/pgpool-general" target="_blank">http://www.pgpool.net/mailman/listinfo/pgpool-general</a><br>
>> >> >> >> >> ><br>
>> >> >> >> >><br>
>> >> >> >><br>
>> >> >><br>
>> >><br>
>><br>
</blockquote></div>