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

[Gary Fu]

Tatsuo,

You are right and thanks for the helpful information.

Gary
> It seems that was introduced in PostgreSQL 8.2:
>
>       * Support for automatically retrieving "SELECT" results in batches
>         using a cursor (Chris Mair)
>         This is enabled using "\set FETCH_COUNT n". This feature allows
>         large result sets to be retrieved in psql without attempting to
>         buffer the entire result set in memory.
>
> So as long as you don't use this feature, pgpool will be fine.
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese: http://www.sraoss.co.jp
>
>> Tatsuo,
>>
>> I'm not sure if it (to use cursor) is from the psql, but I saw it from
>> the pgpool log (see below).
>>
>> Again, my pgpool.conf file have the following setups:
>>
>> replication_mode = on
>> replicate_select = off
>> load_balance_mode = off
>> log_per_node_statement = on
>>
>> and when I did the 'select 1' query in psql
>>
>> 3:08pm 309 gfu at sd3dev1:~$ psql -h sd3dev1
>> psql (9.2.4)
>> Type "help" for help.
>>
>> [sd3dev1.dev1_admin].sd3dev1> select 1;
>>   ?column?
>> ----------
>>          1
>> (1 row)
>>
>> The pgpool has the following log and it seems to me that the select
>> sql is sent to both db servers.
>>
>> 3:08pm 1017 sd3dev1 at sd3dev1:/SD3dev1/pgpool$ pgpool -f
>> /SD3dev1/pgpool/pgpool.conf -D -n
>>
>> 2013-05-14 15:08:53 LOG: pid 26123: Backend status file
>> /SD3dev1/pgpool/log/pgpool_status discarded
>> 2013-05-14 15:08:53 LOG: pid 26123: pgpool-II successfully
>> started. version 3.2.3 (namameboshi)
>> 2013-05-14 15:09:02 LOG: pid 26183: connection received:
>> host=172.28.20.137 port=32862
>> 2013-05-14 15:09:12 LOG: pid 26183: DB node id: 0 backend pid: 28983
>> statement: BEGIN
>> 2013-05-14 15:09:12 LOG: pid 26183: DB node id: 1 backend pid: 6369
>> statement: BEGIN
>> 2013-05-14 15:09:12 LOG: pid 26183: DB node id: 0 backend pid: 28983
>> statement: DECLARE _psql_cursor NO SCROLL CURSOR FOR
>> select 1;
>> 2013-05-14 15:09:12 LOG: pid 26183: DB node id: 1 backend pid: 6369
>> statement: DECLARE _psql_cursor NO SCROLL CURSOR FOR
>> select 1;
>> 2013-05-14 15:09:12 LOG: pid 26183: DB node id: 0 backend pid: 28983
>> statement: FETCH FORWARD 1000 FROM _psql_cursor
>> 2013-05-14 15:09:12 LOG: pid 26183: DB node id: 1 backend pid: 6369
>> statement: FETCH FORWARD 1000 FROM _psql_cursor
>> 2013-05-14 15:09:12 LOG: pid 26183: DB node id: 0 backend pid: 28983
>> statement: CLOSE _psql_cursor
>> 2013-05-14 15:09:12 LOG: pid 26183: DB node id: 1 backend pid: 6369
>> statement: CLOSE _psql_cursor
>> 2013-05-14 15:09:12 LOG: pid 26183: DB node id: 1 backend pid: 6369
>> statement: COMMIT
>> 2013-05-14 15:09:12 LOG: pid 26183: DB node id: 0 backend pid: 28983
>> statement: COMMIT
>>
>>
>> By the way, when I run the following perl program:
>>
>> #----------------------------------------------------
>> #!/usr/local/bin/perl -w
>>
>> use strict;
>>
>> use MTVS::DB;
>>
>> my $instance = 'dev1_tmp';
>> die "NPPSD3_INSTANCE environment variable is not defined" unless
>> ($instance);
>>
>> my $dbh = new MTVS::DB($instance) or die "Failed to connect on
>> instance $instance ";
>>
>> my $sql =<<SQL;
>>          select 1
>> SQL
>>
>> my $items = $dbh->ScalarOfScalar($sql) or die "Failed SQL: $sql";
>>
>> exit;
>> #------------------------------------------------
>>
>> The following pgpool log seems to show that the sql (select 1),
>> without cursor, is only sent to the master db (db server 0)
>>
>> 2013-05-14 15:47:23 LOG: pid 26160: connection received:
>> host=172.28.20.137 port=32884
>> 2013-05-14 15:47:23 LOG: pid 26160: DB node id: 0 backend pid: 29884
>> statement: Parse: select 1
>>
>> 2013-05-14 15:47:23 LOG: pid 26160: DB node id: 0 backend pid: 29884
>> statement: B message
>> 2013-05-14 15:47:23 LOG: pid 26160: DB node id: 0 backend pid: 29884
>> statement: D message
>> 2013-05-14 15:47:23 LOG: pid 26160: DB node id: 0 backend pid: 29884
>> statement: Execute: select 1
>>
>> 2013-05-14 15:47:23 LOG: pid 26160: DB node id: 0 backend pid: 29884
>> statement: DEALLOCATE dbdpg_p26993_1
>> 2013-05-14 15:47:23 LOG: pid 26160: DB node id: 0 backend pid: 29884
>> statement: DISCARD ALL
>> 2013-05-14 15:47:23 LOG: pid 26160: DB node id: 1 backend pid: 7270
>> statement: DISCARD ALL
>>
>>
>>
>> Thanks,
>> Gary
>>
>>> 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
>>>>
>>>>
>