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

[Gary Fu]

Hi Tatsuo,

I did the same simple query with a perl program without the problem 
(select query only sends to master db).  Is this because the 'psql' ?  
If so, do you know any solution (under psql) and/or other suggestions ?

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
>