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

Gary Fu gfu at sigmaspace.com
Tue May 14 05:39:00 JST 2013


Hi Tatsuo,

Sorry it takes a while to response on this.

The pgpool log included in the email (below) was from the psql session.  
It looks like to me that psql uses the cursor for the simple select 
query and passes to pgpool and causes the problem, so that's why I asked 
if it is because of the psql.

By the way, I also tried 'select 1' (under psql) and I got the same 
result (the select sql sent to both servers, not just the master).

Please advise.

Thanks,
Gary

> 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