[Pgpool-general] inconsistency when using two pgpool instances in warm standby?

[Marcelo Martins]

Tatsuo,

I also have some online-recory scripts that I have been meaning to  
post on the list that perhaps some could use it as another  example  
together with the ones you currently provide


Marcelo
PostgreSQL DBA
Linux/Solaris System Administrator
http://www.zeroaccess.org

On Dec 13, 2008, at 6:58 AM, Tatsuo Ishii wrote:

> [snip]
>
>> I can prepare ./contrib/ branch of heartbeat configuration  
>> resources to the
>> 2.2 relese if you are interested in.
>
> Great. I'd like to include your contributions into 2.2 release. I
> will create ./contrib/heartbeat directory and will put files you
> provide under it. Please provide a tar ball along with README.
>
> I'm almost ready for 2.2. F.Y.I. here is the NEWS file of 2.2.
> ----------------------------------------------------------------------
> 2.2 (urukiboshi) 2008/12/?
> 	  * Version 2.2
>
> 	  This version enhances on line recovery and fixes important bugs
> 	  in handling serializable transactions which could cause data
> 	  inconsistency among DB nodes.
>
> 	  * New features
>
> 		- Add client_idle_limit_in_recovery directive. This will
> 		  prevent 2nd stage of on line recovery from not going forward
> 		  by idle clients sitting forever(Tatsuo)
>
> 		- Allow to load balance DECLARE, FETCH and CLOSE(Tatsuo)
>
> 	  * Bug fixes
>
> 		- Close all file descriptors when running in daemon mode.
> 		  Otherwise we inherit sockets from apache when it's started by
> 		  pgpoolAdmin. This results in that port 80 is occupied for
> 		  example.  Patch provided by Akio Ishida. Also add
> 		  chdir("/"). This is always good for daemon programs(Tatsuo)
>
> 	    - Allow MD5 authentication in raw mode as stated in
>          docs(Tatsuo)
>
> 		- Check transaction serialization failure error in
> 		  serializable mode and abort all nodes if so. Otherwise we
> 		  allow data inconsistency among DB nodes(Tatsuo).
>
> 		  See following scenario: (M:master, S:slave)
>
> 		  	M:S1:BEGIN;
> 			M:S2:BEGIN;
> 			S:S1:BEGIN;
> 			S:S2:BEGIN;
> 			M:S1:SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> 			M:S2:SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> 			S:S1:SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> 			S:S2:SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> 			M:S1:UPDATE t1 SET i = i + 1;
> 			S:S1:UPDATE t1 SET i = i + 1;
> 			M:S2:UPDATE t1 SET i = i + 1; <-- blocked
> 			S:S1:COMMIT;
> 			M:S1:COMMIT;
> 			M:S2:ERROR:  could not serialize access due to concurrent update
> 			S:S2:UPDATE t1 SET i = i + 1; <-- success in UPDATE and data  
> becomes inconsistent!
>
> 		- avoid kind mismatch error caused by "SET TRANSACTION
> 		  ISOLATION LEVEL must be called before any query"(Tatsuo).
>
> 		   This could happen in following scenario:
>
> 		    M:S1:BEGIN;
>            S:S1:BEGIN;
> 			M:S1:SELECT 1; <-- only sent to MASTER
> 			M:S1:SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> 			S:S1:SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> 			M: <-- error
> 			S: <-- ok since no previous SELECT is sent. kind mismatch error  
> occurs!
>
> 	  * Incompatible changes
>
> 		- Always fail over and restart all children. Before we do
> 		  restart only if master has not been changed. This is
> 		  wrong. If we have trouble with network cable or something,
> 		  TCP/IP stack keeps on retrying for long time and the only
> 		  way to prevent it is restarting process(Tatsuo)
> ----------------------------------------------------------------------
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> _______________________________________________
> Pgpool-general mailing list
> Pgpool-general at pgfoundry.org
> http://pgfoundry.org/mailman/listinfo/pgpool-general