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

[Tatsuo Ishii]

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