[pgpool-general: 5966] Re: pgpool load balancing problem

[Tatsuo Ishii]

So you are surely bitten by the bug in 3.7.2.

In other message you failed to incomporate the patch. Maybe executing
"make clean" before compiling helps you.

If you are not familiar with patching, you could obtain whole source
code from:
https://git.postgresql.org/gitweb/?p=pgpool2.git;a=shortlog;h=refs/heads/V3_7_STABLE

In this window the top most row is the latest entry for 3.7.x. You hit
"snapshot" link to get the source code tar ball (.tgz).

Current direct link to the tar ball is:
https://git.postgresql.org/gitweb/?p=pgpool2.git;a=snapshot;h=8593af680d9c575c23a823489ecfa693749811f3;sf=tgz

Another workaround is, swapping the entry for node 0 and node 1 in
your pgpool.conf.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

> pool_version
> 
> ----------------------
> 
> 3.7.2 (amefuriboshi)
> 
> (1 row)
> 
> 
> 
> 
> 
> psql -d postgres -U postgres -h 127.0.0.1  -p 9999 -c "show pool_nodes";
> 
> 
> 
> node_id |    hostname     | port | status | lb_weight |  role   |
> select_cnt | load_balance_node | replication_delay
> 
> ---------+-----------------+------+--------+-----------+---------+------------+-------------------+-------------------
> 
> 0       | pg1 | 5432 | up     | 0.333333  | standby | 22565      |
> true              | 0
> 
> 1       | pg2 | 5432 | up     | 0.333333  | primary | 10416      |
> false             | 0
> 
> 2       | pg3 | 5432 | up     | 0.333333  | standby | 7686       |
> false             | 0
> 
> (3 rows)
> 
> 
> 2018-03-01 6:23 GMT+02:00 Tatsuo Ishii <ishii at sraoss.co.jp>:
> 
>> > I found a problem that had a big impact on our servers. I'm using pgpool
>> as
>> > a load balancer and pool connections manager. When a user tries to run a
>> > select query it redirects the query to the slave. If the user tries after
>> > that to run dml or ddl command he get error :
>> >
>> > ERROR: cannot execute CREATE TABLE in a read-only transaction
>> > SQL state: 25006
>> >
>> > Is there any way to solve this issue ? It means that once the pool
>> > redirected your session to the slaves you cant run any ddl/dml actions ..
>>
>> Normaly Pgpool-II redirects any ddl/dml query to primary.  Can you
>> show me following query results? I would like to confirm your problem.
>>
>> show pool_version;
>> show pool_nodes;
>>
>> Best regards,
>> --
>> Tatsuo Ishii
>> SRA OSS, Inc. Japan
>> English: http://www.sraoss.co.jp/index_en.php
>> Japanese:http://www.sraoss.co.jp
>>