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

[Tatsuo Ishii]

Yes.

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

> Is the link you specified in the end of your comment is the 3.7.2 version
> without the bug ?
> 
> 2018-03-02 1:10 GMT+02:00 Tatsuo Ishii <ishii at sraoss.co.jp>:
> 
>> 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
>> >>
>>