[Pgpool-general] Zimbra Detected SpamPgPool replication or Slony

[Pascal Cohen]

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.

Pascal