[Pgpool-general] Replication Mode and Sequences

[Christoph Handel]

Hi List,

I'm thinking about using PGPool-II to spread our database load and get
some redundancy.

I have some troubles understanding the Replication Mode. 

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?

With:

   load_balance_mode = true
   replicate_select = false

Selects will be distributed? Right?

With

   load_balance_mode = false
   replicate_select = false

Selects go only to master.


And those two should be the same:
   load_balance_Mode = true
   replicate_select = true

   load_balance_mode = false
   replicate_select = true


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?




Next questions is about Sequences.

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?

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?

Any other way to avoid problems with sequences?


Greetings
   Christoph