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

[Gary Fu]

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