[Pgpool-general] replication_mode safety

[Jeff Davis]

On Tue, 2007-05-29 at 17:31 +0900, Tatsuo Ishii wrote:
> Hi Jeff,
> 
> It was nice to see you in Ottawa.

Hi Tatsuo Ishii, it was very nice to meet you at PGCon.

> > Does this mean that all writes are serialized through pgpool, preventing
> > any concurrent writes at all, even from different clients?
> 
> No.
> 

In pgpool-II 1.1, with two clients connected through pgpool, I get these
results:

Client1=> insert into t2 select generate_series(1,5000000);
Client1=> insert into t1 select i from t2; -- statement1
Client2=> insert into t2 values(-1); -- statement2

After these statements finish, backend0 has 5000000 records in t1, but
backend1 has 5000001 records in t1.

I think that's happening because statement1 executes on backend0 before
statement2, getting a snapshot that doesn't include the tuple with value
-1. However, statement2 finishes long before statement1 on backend0, and
then statement2 is sent to backend1 and commits on backend1 before
statement1 begins on backend1. Therefore, statement1 on backend1 gets a
snapshot that includes the tuple with value -1 and then inserts it into
t1.

Is this a bug or expected behavior? The simplest way to work around it
is to serialize all writes, but that would sacrifice performance. There
may be other solutions, but they all seem complex.

Regards,
	Jeff Davis