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

[Lachezar Dobrev]

  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
>