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 <[email protected]> > To: Sandeep Thakkar <[email protected]>; Guillaume Lelarge > <[email protected]> > Cc: [email protected] > 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 <[email protected]> > To: Sandeep Thakkar <[email protected]>; Guillaume Lelarge > <[email protected]> > Cc: [email protected] > 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 <[email protected]> > To: Guillaume Lelarge <[email protected]> > Cc: [email protected] > 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 <[email protected]> > To: Sandeep Thakkar <[email protected]> > Cc: [email protected] > 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 > > > _______________________________________________ Pgpool-general mailing list [email protected] http://pgfoundry.org/mailman/listinfo/pgpool-general
