[pgpool-general: 3671] Re: Load balancing in explicit transaction block?

[Tatsuo Ishii]

> Hi, Tatsuo!
> Thank you for answer!
> It is good that function is not balanced - it writes to database. But the
> next statement in transaction (select after function) is balanced. Is it
> correct?

True.

> We do not want select to be balanced. And we expected it not to be balanced
> to slave, because it sits next to writing statement and in same explicit
> transaction.

You really should use black_function_list (or white_function_list).

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