[Pgpool-general] Replication Mode and Sequences

[Jaume Sabater]

----- "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.

> 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.

> 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"