[Pgpool-general] Query regarding load balancing

[Tatsuo Ishii]

You need to tell pgpool that your function is writing to DB.
Please refer to black_function_list directive of pgpool.conf.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> But, when I write the insert statement in a function, pgpool sends it to the 
> slave. Why?
> 
> create table spl_tbl(j int);
> 
> create function plpgsql_testfunc(i int) returns int
> language plpgsql
> as
> $$
> 
> declare
>         col int;
> begin
>         --for i in 1..10 loop
>           insert into spl_tbl values (1);
>           select count(*) into col from spl_tbl;
>         --end loop;
>  return col;
> end;
> $$
> ;
> 
> select plpgsql_testfunc(1);
> --
> psql -p 9999 -c "\i sql/test_function.sql" postgres
> 
> ERROR:  cannot execute INSERT in a read-only transaction
> 
> Why is this?
> 
> 
> 
> 
> ________________________________
> From: Sandeep Thakkar <sandeeptt at yahoo.com>
> To: Sandeep Thakkar <sandeeptt at yahoo.com>; Guillaume Lelarge 
> <guillaume at lelarge.info>
> Cc: pgpool-general at pgfoundry.org
> Sent: Fri, January 14, 2011 4:36:52 PM
> Subject: Re: [Pgpool-general] Query regarding load balancing
> 
> 
> Sorry, the first query was:
> psql -p 9999 -c "CREATE TABLE streamtest5(x int)" postgres
> 
>  Sandeep.
> 
> 
> 
> 
> ________________________________
> From: Sandeep Thakkar <sandeeptt at yahoo.com>
> To: Sandeep Thakkar <sandeeptt at yahoo.com>; Guillaume Lelarge 
> <guillaume at lelarge.info>
> Cc: pgpool-general at pgfoundry.org
> Sent: Fri, January 14, 2011 3:54:02 PM
> Subject: Re: [Pgpool-general] Query regarding load balancing
> 
> 
> I enabled the query logs and I tried these statements:
> psql -p 9999 -c "select * from streamtest5" postgres
> 
> 
> psql -p 9999 -c "insert into streamtest5 values ('1')" postgres
> 
> psql -p 9999 -c "select * from streamtest5" postgres
> 
> I saw the server logs and found that the first two queries are executed by the 
> Master and the third one, which is a read only is executed by the Standby. That 
> means, it is working as expected and we can say that load is getting balanced, 
> even though we have not executed thousands of queries here. :)
> 
> 
> 
> 
> 
> ________________________________
> From: Sandeep Thakkar  <sandeeptt at yahoo.com>
> To: Guillaume Lelarge <guillaume at lelarge.info>
> Cc: pgpool-general at pgfoundry.org
> Sent: Fri, January 14, 2011 3:39:49 PM
> Subject: Re: [Pgpool-general] Query regarding load balancing
> 
> 
> Yes, I have set backend_weight0 = 0 and backend_weight1 = 1. So that all SELECT 
> queries go to Standby. and even though the  backend_weight0 (Master) is set to 
> 0, the INSERT/DELETE/UPDATE  queries will be handled by Master only.
> 
> I'll enable query log and confirm this. Thanks
> 
> 
> 
> 
> ________________________________
> From: Guillaume Lelarge <guillaume at lelarge.info>
> To: Sandeep Thakkar <sandeeptt at yahoo.com>
> Cc: pgpool-general at pgfoundry.org
> Sent: Fri, January 14, 2011 3:05:07 PM
> Subject: Re: [Pgpool-general] Query regarding load balancing
> 
> Le 14/01/2011 10:16, Sandeep Thakkar a écrit :
>> [...]
>> I'm using PG9.0 and pgpool-3.0.1 and I have setup a master and a standby 
>> servers. Standby server is configured for Hot Standby and Stream replication. 
>> I'm using pgpool in Master/Slave mode with Load balancing mode on. I would like 
>>
>> to execute few queries using pgpool port and I would like to see which server 
>> has served that query. I think, the read only queries should go to Standby and 
> 
>> Insert/Update and some others should go to the Master. But how do I confirm 
>> this? I checked the server logs, but I did not find anything. In pgpool log, I 
> 
>> could see information like "wait_for_query_response", but I did not understand. 
>>
>> 
> 
> The easier way is to enable queries log on both PostgreSQL nodes. BTW,
> read queries are executed by the master node or by the standby node, not
> only by the node unless you set the weight to  0 on the master node
> configuration.
> 
> 
> -- 
> Guillaume
> http://www.postgresql.fr
> http://dalibo.com
> 
> 
>