[Pgpool-general] Suggestion for load_balancing mode

[Tatsuo Ishii]

> Tatsuo,
> 
> > What I want to do by "session level load balancing" is something like
> > this:
> >
> > 1) a client which is willing to use session level load balancing
> >    explicitly declare it by issuing a special command or set global
> >    parameter in pgpool.conf
> >
> > 2) when the client connects to pgpool, pgpool selects one of the two
> >    servers to deal with the client.
> >
> > 3) the client sends SQL to pgpool and pgpool forwards it to the
> >    selected PostgreSQL server
> >
> > 4) repeat 3) until the client disconnects
> 
> Makes sense to me.   Actually, this is how a lot of other pooling mechanisms 
> (like Hibernate) work.   Except they don't have the option *not* to maintain 
> a session.

It's easy for them since they just do connection pooling. On the other
hand, for replication servers like pgpool there will be many problems
with this method. Pgpool should not load balance some SQLs:

1) DMLs for persistent objects must not be load balanced

2) Explicit transaction blocks must not be load balanced.

3) DDLs must not be load balanced.

For session local DDLs/DMLs (for example TEMPORARY TABLE), I'm not
sure if they could be load balanced. If not, probably there's no point
to introduce "session level load balancing". Think about this kind of
scenario:

a) CREATE TEMPORARY TABLE t1...
b) INSERT INTO t1...
c) INSERT INTO a_persistent_table SELECT * FROM t1...
:
:

The first problem is how pgpool know b) is a DDL to t1. pgpool need to
know the query plan. Second problem is c) will fail on a server if a)
is load balanced.
--
Tatsuo Ishii