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

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

Our stored function is not balanced. But next select in the same
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)*

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.

ср, 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/20150423/e8b3d96d/attachment.html>