[Pgpool-general] replication_mode safety

[Tatsuo Ishii]

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

I think you will get random results even if you do not use pgpool-II
in the example you have shown above. It depends on the timing and the
process scheduling whether t1 results in 5000000 or 5000001 records.

To get a consistent result, you can use serializable transaction:

Client1=> begin;
Client1=> set transactioin isolation level serializable;
Client1=> insert into t2 select generate_series(1,5000000);
Client1=> insert into t1 select i from t2; -- statement1
Client1=> end;
Client2=> insert into t2 values(-1); -- statement2

Or serializing access to t2:

Client1=> begin;
Client1=> lock table t2;
Client1=> insert into t2 select generate_series(1,5000000);
Client1=> insert into t1 select i from t2; -- statement1
Client1=> end;
Client2=> begin;
Client2=> lock table t2;
Client2=> insert into t2 values(-1); -- statement2
Client2=> end;
--
Tatsuo Ishii
SRA OSS, Inc. Japan