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

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

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/20150424/3dc8d888/attachment-0001.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: transaction_load_balance_test.py
Type: text/x-python
Size: 2599 bytes
Desc: not available
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20150424/3dc8d888/attachment-0001.py>