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

[Tatsuo Ishii]

[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