[Pgcluster-general] SELECT is executed on all nodes if withingtransaction

[Alexey Kamenchuk]

I just tried to make same test connecting to one of DB nodes (rather than to
the load balancer) and yes, indeed, SELECT was executed on than DB only -
replicator didn't pass it over to another DB.

 

Trouble is the load is not spread across servers in this case. I do need the
load to be balanced

 

Please can someone help me?

 

Thanks

Alexey

 

  _____  

From: pgcluster-general-bounces at pgfoundry.org
[mailto:pgcluster-general-bounces at pgfoundry.org] On Behalf Of Alexey
Kamenchuk
Sent: Monday, February 19, 2007 1:40 PM
To: pgcluster-general at pgfoundry.org
Subject: Re: [Pgcluster-general] SELECT is executed on all nodes if
withingtransaction

 

Hi Christian

 

Thanks for coming back to me

 

For some reason the trick doesn't work for me :-(

 

The way I'm testing it is:

I started replicator, 2 DB nodes and load balancer

I connected to the load balancer using psql tool and issued following
commands:

 

postgres=# begin;

BEGIN

postgres=# select * from test;

 a

---

 1

(1 row)

 

postgres=# commit;

COMMIT

postgres=#

 

Then I checked logs of both postgres servers - first one contains:

 

2007-02-19 13:22:50 MSK 81893 LOG:  statement: begin

2007-02-19 13:22:54 MSK 81893 LOG:  statement: SELECT
PGR_SYSTEM_COMMAND_FUNCTION(8,13,0,1)

2007-02-19 13:22:54 MSK 81893 LOG:  statement: select * from test

2007-02-19 13:22:56 MSK 81893 LOG:  statement: SELECT
PGR_SYSTEM_COMMAND_FUNCTION(8,14,0,1)

2007-02-19 13:22:56 MSK 81893 LOG:  statement: commit

 

And second one contains:

 

2007-02-19 13:23:30 MSK [local] 81878LOG:  statement: begin;

2007-02-19 13:23:33 MSK [local] 81878LOG:  statement: select * from test;

2007-02-19 13:23:35 MSK [local] 81878LOG:  statement: commit;

 

 

Any ideas?

 

Thanks a lot for help

Alexey

 

  _____  

From: pgcluster-general-bounces at pgfoundry.org
[mailto:pgcluster-general-bounces at pgfoundry.org] On Behalf Of Christian
Dannemann
Sent: Monday, February 19, 2007 12:07 PM
To: pgcluster-general at pgfoundry.org
Subject: Re: [Pgcluster-general] SELECT is executed on all nodes if withing
transaction

 

Hi Alexey,

put

not_replicate_prepared_select = true

in postgresql.conf on all cluster servers and the problem is resolved.

Cheers,

Christian


Alexey Kamenchuk wrote: 

Hi All

 

I mentioned that load balancer executes SELECT on all nodes if it is issued
within transaction. I expected the behavior when SELECT is sent to only DB
node. Is it something wrong with my configuration or this is how LB always
works?

 

Thanks

 

Best regards

Alexey Kamenchuk

 

 
 
 
 
 





  _____  



 
 
 
 
 
_______________________________________________
Pgcluster-general mailing list
 <mailto:Pgcluster-general at pgfoundry.org> Pgcluster-general at pgfoundry.org
 <http://pgfoundry.org/mailman/listinfo/pgcluster-general>
http://pgfoundry.org/mailman/listinfo/pgcluster-general
  
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://pgfoundry.org/pipermail/pgcluster-general/attachments/20070219/870c4f10/attachment.html