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

[Сергей Мелехин]

>So if you use the function
by not using CURSOR it will be load balanced unless you register it in
the black function list.

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*.



чт, 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
> >> >> >> >> >> >
> >> >> >> >> >>
> >> >> >> >>
> >> >> >>
> >> >>
> >>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20150430/bc005a22/attachment-0001.html>