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

[Tatsuo Ishii]

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