[Pgpool-general] Zimbra Detected SpamPgPool replication or Slony

[Tatsuo Ishii]

> Tatsuo Ishii wrote:
> >>> Can that situation occurs also with PgPool replication like:
> >>> PgPool sends update of A in M .
> >>> First request on A goes on M.
> >>> Second request on A goes to S.
> >>> PgPool sends update to S.
> >>>       
> >
> > This could happen too, though the window is very narrow comparing with
> > Slony-I. As of pgpool-II 2.0, any query is executed as an explicit
> > transaction. Let's say you have a master and two slaves and you send an
> > update query to pgpool-II.
> >
> >   
> Thanks, it was almost a theoretical question - but if it can happen, it 
> will happen in Production the day you are not expecting it ;)
> > Also note that in pgpool, if you send an update then a select to
> > retrieve the result in a *same session*, it's guaranteed that the
> > select returns the result of update since the update and select will
> > be executed on a same PostgreSQL node even if the load balanace option
> > is enabled.
> >   
> I had noticed that.
> 
> Does the lock mechanism change anything to my question ?
> 
> In fact I made the following tests with and without the lock:
> 
> * pgpool distributing the load on 3 machines A,B,C defined in that order 
> in the list.
> * I create a table with an id as primary key and a string column.
> *Through several psql connections via pgpool I update the DB and add 
> some rows.
> * Then I add manually in B server a line with id let's say 5. When then 
> I reconnect the psql client and try to add a row with id 5, it fails as 
> expected and detecting data inconsistency, it removes A.
> * When I do not set any lock, I get in fact A with the new row with 
> id=5, the second one with previous row and C with no row.
> * When I use the lock A and C remain consistent with no new row while B 
> has its previous row.
> 
> My idea is to keep the DB consistency among the three databases (a kind 
> of two-phase commit). And I just added that row to check the behavior on 
> the three database when an error might occur on a given DB.
> It is like with no-lock I can't guarantee the DB consistency and that 
> with lock I can.
> Am I right ?
> And with the lock, can the update be "accessed" on all the tables only 
> after all the DB have comited their update ?
> 
> Thanks for your answer and also thank you for PgPool which is a very 
> nice tool.

I think you could avoid the window by using a strong lock.

BEGIN;
LOCK t1;
UPDATE t1;
SELECT...
COMMIT;

With this, any transaction will not see the effect of UPDATE until all
servers commit the transaction.

Of course this will sacrifice concurrency though.

BTW I don't think 2PC helps you in this regard.
--
Tatsuo Ishii
SRA OSS, Inc. Japan