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

[Tatsuo Ishii]

Hi Gary,

How did you know psql uses the cursor?
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> 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
>