[pgpool-general: 1644] Re: Problem with 'replicate_select'

Tatsuo Ishii ishii at postgresql.org
Sat Apr 20 10:25:59 JST 2013


Hi Gary,

> Hi Tatsuo,
> 
> I did the same simple query with a perl program without the problem
> (select query only sends to master db).

That's not what I expected. Can you show me pgpool log?

> Is this because the 'psql' ?

I don't think so.

> If so, do you know any solution (under psql) and/or other suggestions
> ?

No idea.

> Thanks,
> Gary
>> Hi Gary,
>>
>> pgpool-II does not think DECLARE-FETCH as read query because the query
>> *might* be with "FOR UPDATE/FOR SHARE". In the future maybe we could
>> look into the query a little bit deeply and determine if it actually
>> uses "FOR UPDATE/FOR SHARE". I will add to it TODO list.
>> --
>> Tatsuo Ishii
>> SRA OSS, Inc. Japan
>> English: http://www.sraoss.co.jp/index_en.php
>> Japanese: http://www.sraoss.co.jp
>>
>>> Hi,
>>>
>>> I'm running pgpool 3.2.3 with postgresql db server 9.2.4.
>>>
>>> In the /SD3dev1/pgpool/pgpool.conf configure file, I have the
>>> following setups:
>>>
>>> replication_mode = on
>>> replicate_select = off
>>> load_balance_mode = off
>>>
>>> log_per_node_statement = on
>>>
>>>  From the documentation, the select sql should only send to the master
>>> db, but the test below, I can see it still sends to both dbs.  Do I
>>> miss anything here ?
>>>
>>> Thanks,
>>> Gary
>>>
>>>
> --------------------------------------------------------------------
> psql with select sql
>>> 7:47pm 334 gfu at sd3dev1:~$ psql -h sd3dev1
>>> psql (9.2.4)
>>> Type "help" for help.
>>>
>>> [sd3dev1.dev1_admin].sd3dev1> select * from config where
>>> name='ControllerUser';
>>>        name      |  value
>>> ----------------+---------
>>>   ControllerUser | sd3dev1
>>> (1 row)
>>>
>>>
>>>
> -------------------------------------------------------------------------
>>> pgpool and log
>>> 7:47pm 1024 sd3dev1 at sd3dev1:~$ pgpool -f /SD3dev1/pgpool/pgpool.conf
>>> -D -n
>>> 2013-04-18 19:47:50 LOG: pid 14202: Backend status file
>>> /SD3dev1/pgpool/log/pgpool_status discarded
>>> 2013-04-18 19:47:50 LOG: pid 14202: pgpool-II successfully
>>> started. version 3.2.3 (namameboshi)
>>>
>>> 2013-04-18 19:47:55 LOG: pid 14262: connection received:
>>> host=172.28.20.137 port=50432
>>> 2013-04-18 19:48:04 LOG: pid 14262: DB node id: 0 backend pid: 6692
>>> statement: BEGIN
>>> 2013-04-18 19:48:04 LOG: pid 14262: DB node id: 1 backend pid: 6660
>>> statement: BEGIN
>>> 2013-04-18 19:48:04 LOG: pid 14262: DB node id: 0 backend pid: 6692
>>> statement: DECLARE _psql_cursor NO SCROLL CURSOR FOR
>>> select * from config where name='ControllerUser';
>>> 2013-04-18 19:48:04 LOG: pid 14262: DB node id: 1 backend pid: 6660
>>> statement: DECLARE _psql_cursor NO SCROLL CURSOR FOR
>>> select * from config where name='ControllerUser';
>>> 2013-04-18 19:48:04 LOG: pid 14262: DB node id: 0 backend pid: 6692
>>> statement: FETCH FORWARD 1000 FROM _psql_cursor
>>> 2013-04-18 19:48:04 LOG: pid 14262: DB node id: 1 backend pid: 6660
>>> statement: FETCH FORWARD 1000 FROM _psql_cursor
>>> 2013-04-18 19:48:04 LOG: pid 14262: DB node id: 0 backend pid: 6692
>>> statement: CLOSE _psql_cursor
>>> 2013-04-18 19:48:04 LOG: pid 14262: DB node id: 1 backend pid: 6660
>>> statement: CLOSE _psql_cursor
>>> 2013-04-18 19:48:04 LOG: pid 14262: DB node id: 1 backend pid: 6660
>>> statement: COMMIT
>>> 2013-04-18 19:48:04 LOG: pid 14262: DB node id: 0 backend pid: 6692
>>> statement: COMMIT
>>>
>>> _______________________________________________
>>> pgpool-general mailing list
>>> pgpool-general at pgpool.net
>>> http://www.pgpool.net/mailman/listinfo/pgpool-general
>>
> 


More information about the pgpool-general mailing list