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

[Tatsuo Ishii]

> I thought that functions are not balanced by default:
> 
> http://pgpool.projects.pgfoundry.org/pgpool-II/doc/pgpool-en.html
> white_function_list
> 
> Specify a comma separated list of function names that do not update the
> database. SELECTs using *functions not specified in this list* are neither
> load balanced, nor replicated if in replication mode. In master slave mode,
> such SELECTs *are sent to master (primary) only*.

I think the description is plain wrong. This should be:

In master slave mode, such SELECTs *are load balanced*.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

> чт, 30 апр. 2015 г. в 11:28, Tatsuo Ishii <ishii at postgresql.org>:
> 
>> > Hi, Tatsuo!
>> > Sorry, but I still dont understand. Stored function is not balanced, it
>> > executes on master only. Why should we add it to black list?
>>
>> The reason why the SELECT (and FETCH) is not load balanced is, it uses
>> cursor, not because of the stored function. So if you use the function
>> by not using CURSOR it will be load balanced unless you register it in
>> the black function list.
>>
>> > The problem is with next statement: plain select from table,  no
>> functions
>> > used.
>> > If we add function to black list, the next select will not be balanced
>> too?
>>
>> I thought so, but was wrong. The decision is made by calling
>> is_select_query(node, query)), which essentially regards a statement
>> as SELECT if it looks like a SELECT. i.e. not checking if it calls
>> writing functions. This seems a bug and I am going to discuss with
>> other developers.
>>
>> Best regards,
>> --
>> Tatsuo Ishii
>> SRA OSS, Inc. Japan
>> English: http://www.sraoss.co.jp/index_en.php
>> Japanese:http://www.sraoss.co.jp
>>
>> > чт, 30 апр. 2015, 9:10, Tatsuo Ishii <ishii at postgresql.org>:
>> >
>> >> > 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
>> >> >> >> >> >> >
>> >> >> >> >> >>
>> >> >> >> >>
>> >> >> >>
>> >> >>
>> >>
>>