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

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

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20150421/6f604d29/attachment.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/20150421/6f604d29/attachment.py>