[Pgpool-general] Replication Mode and Sequences

[Tatsuo Ishii]

> ----- "Christoph Handel" <christoph.handel at emporis.com> escribió:
> 
> > load_balance_mode -> Selects will be distributed to all nodes. So
> > first query going to node 1, next query to to node 2, ...
> > 
> > replicate_select -> if set to true all selects will be executed on
> > all
> > nodes? How does this work with load_balance_mode?
> 
> As far as I know, replicate_select allows you detect data mismatch. But I am not sure about this feature either, and would like Tatsuo to tell us the difference about it, too :)
> 
> > With:
> > 
> >    load_balance_mode = true
> >    replicate_select = false
> > 
> > Selects will be distributed? Right?
> 
> Yes.
> 
> > With
> > 
> >    load_balance_mode = false
> >    replicate_select = false
> > 
> > Selects go only to master.
> 
> Yes.
> 
> > And those two should be the same:
> >    load_balance_Mode = true
> >    replicate_select = true
> > 
> >    load_balance_mode = false
> >    replicate_select = true
> 
> They should, but let's wait for Tatsuo's confirmation.

Actually the decision to load balance is a little bit
complicated. Included is a flow chart which exlains hwo pgpool-II
decides. I'm going to include the diagram in the docs for the next
release.

> > How does pgpool decide if a query is a select (load-balance possible)
> > or an insert/update (must be executed on all nodes). Specially what
> > happens in a transaction? Is a transaction always executed on all
> > servers, even if someone is just using selects in the transaction?
> 
> pgpool-II won't do load balance when there are select and insert/update/delete statements in the same transaction. Instead, it will execute all queries against the master node and the insert/update/delete ones against the slave node only.

No. When a client connects to pgpool-II, pgpool-II will select
randomly one of the nodes for load balance. If the client start a
transaction, pgpool-II sends "BEGIN" to all nodes. and if the client
sends some DML, it will sends to all nodes as well. If the client then
sends "load-balance possible"(see the attached diagram) SELECT, it
will be sent to the node which pgpool-II chooses at the connection
time.

> > We rely on sequences to generate primary keys. If insert/update
> > queries are executed on multiple backends, they might be executed in
> > different orders, so generate different primary keys. 
> > 
> > How to avoid this?
> 
> Locking the tables.
> 
> > using insert_lock=true locks the table and then execute the insert.
> > No
> > Paralell execution, so no different execution order. What happens if
> > i
> > do a "select nextval(seq);" No locks. Might go out of sync?
> 
> Yes, it might.
> 
> > Any other way to avoid problems with sequences?
> 
> Not using them. Try relying on business logic to generate ids.
> 
> --
> Jaume Sabater
> http://linuxsilo.net/
> 
> "Ubi sapientas ibi libertas"
-------------- next part --------------
A non-text attachment was scrubbed...
Name: load_balance.odp
Type: application/octet-stream
Size: 14614 bytes
Desc: not available
Url : http://pgfoundry.org/pipermail/pgpool-general/attachments/20081121/f2c98b72/attachment-0001.obj