<div dir="ltr">Hi!<br>Here is test case that fails on slow replication.<div><br><div class="gmail_quote">чт, 23 апр. 2015 г. в 17:16, 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">> 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 and<br>
black function lists are not specified.<br>
<br>
> transaction is balanced. Documentation, if i understand it correctly,<br>
> states that it should not be balanced. I think it should be 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 must be met:<br>
><br>
> - PostgreSQL version 7.4 or later<br>
> - *the query must not be in an explicitly declared transaction (i.e. 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. But 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 BEGIN are<br>
> sent to the primary node.Following SELECT and some other queries that can<br>
> be sent to both primary or standby are executed in the transaction or on<br>
> the standby node.Commands which cannot be executed on the standby such as<br>
> INSERT are sent to the primary. After one of these commands, even SELECTs<br>
> are sent to the primary node, This is because these SELECTs might want to<br>
> see the result of an INSERT immediately. This behavior continues until the<br>
> transaction closes or aborts.<br>
><br>
> This states that after any writing in transaction all statements should not<br>
> be balanced. This contradicts first part, which can be understood as 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 load<br>
>> balancing: all functions are treated as same. So "SELECT 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 add<br>
>> "/*NO LOAD BALANCE*/" to the SELECT statement. See the manual for 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 node<br>
>> > (slave) but it does not (by default stored functions are not balanced<br>
>> even<br>
>> > if they are not explicitly listed in black list). Problem is with next<br>
>> > statement in this transaction, which is select. And we want this 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 <<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 functions<br>
>> >> to inform the PgPool which functions are suitable for distributing, or<br>
>> >> which are not suitable. Documentation specifies that you can 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 (BEGIN;<br>
>> >> /* EVERYTHING; */ END;) would be sent to the master.<br>
>> >><br>
>> >> [1]<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>
>> <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 such as BEGIN<br>
>> >> are<br>
>> >> > sent to the primary node.Following SELECT and some other queries that<br>
>> >> can be<br>
>> >> > sent to both primary or standby are executed in the transaction or on<br>
>> the<br>
>> >> > standby node.Commands which cannot be executed on the standby such as<br>
>> >> INSERT<br>
>> >> > are sent to the primary. After one of these commands, even SELECTs are<br>
>> >> sent<br>
>> >> > to the primary node, This is because these SELECTs might want to see<br>
>> the<br>
>> >> > result of an INSERT immediately. This behavior continues until the<br>
>> >> > transaction closes or aborts.<br>
>> >> ><br>
>> >> > It looks like pgpool treats all stored functions as non writing in<br>
>> this<br>
>> >> > scenario.<br>
>> >> ><br>
>> >> > вт, 21 апр. 2015 г. в 12:05, Сергей Мелехин <<a href="mailto:cpro29a@gmail.com" target="_blank">cpro29a@gmail.com</a>>:<br>
>> >> ><br>
>> >> >> Hi!<br>
>> >> >> Our test server is relatively slow, and there are some lags in<br>
>> >> replication<br>
>> >> >> between master and slave sometimes. We are using pgpool 3.3.4 in load<br>
>> >> >> balancing mode to mimic our production environment.<br>
>> >> >> Some unit tests are making some changes in database (calling stored<br>
>> >> >> functions) and immediately check them issuing select 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 requirements must<br>
>> be<br>
>> >> >> met:<br>
>> >> >><br>
>> >> >> PostgreSQL version 7.4 or later<br>
>> >> >> the query must not be in an explicitly declared transaction (i.e. not<br>
>> >> in a<br>
>> >> >> BEGIN ~ END block)<br>
>> >> >><br>
>> >> >> we decided that putting whole test in transaction block will avoid<br>
>> load<br>
>> >> >> balancing for such test, but it looks like that no matter being in<br>
>> >> >> transaction, selects are replicated to slave and dont find desired<br>
>> data<br>
>> >> >> because of replication lags. This errors are present when we use<br>
>> stored<br>
>> >> >> functions, when we use explicit DML, selects inside transaction are<br>
>> not<br>
>> >> >> replicated. Function names are not included in white or black 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 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 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>
</blockquote></div></div></div>