[Pgpool-hackers] Problems with PgPool 2.3.3 Prepare/Deallocation handling in Master/Slave mode

[Jan Kantert]

Hi,

we experienced problems with PgPool 2.3.3 and the Prepare Deallocation 
handling in Master/Slave mode:

statement: DEALLOCATE pdo_pgsql_stmt_00e01cf0
statement: BEGIN
statement: DEALLOCATE pdo_pgsql_stmt_00e01cf0
SimpleQuery: Error or notice message from backend: : DB node id: 1 
statement: DEALLOCATE pdo_pgsql_stmt_00e01cf0 message: prepared 
statement "pdo_pgsql_stmt_00e01cf0" does not exist
SimpleQuery: Error or notice message from backend: : DB node id: 2 
statement: DEALLOCATE pdo_pgsql_stmt_00e01cf0 message: prepared 
statement "pdo_pgsql_stmt_00e01cf0" does not exist
read_kind_from_backend: 0 th kind C does not match with master or 
majority connection kind E
kind mismatch among backends. Possible last query was: "DEALLOCATE 
pdo_pgsql_stmt_00e01cf0" kind details are: 0[C] 1[E: prepared statement 
"pdo_pgsql_stmt_00e01cf0" does not exist][SELECT NEXTVAL('xxx')] 2[E: 
prepared statement "pdo_pgsql_stmt_00e01cf0" does not exist][SELECT 
NEXTVAL('xxx')]
do_child: exits with status 1 due to error


Some notices: First we issue a prepared SELECT NEXTVAL in a transaction. 
This is only send to the master. Later we try to deallocate the prepared 
statement. This works on the master, but fails on the slaves. Seems to 
be a logic flaw.

I wrote a little patch to fix this:

--- pgpool-II-2.3.3/pool_process_query.c	2010-04-16 07:44:56.000000000 +0000
+++ pgpool-II-2.3.3-patched/pool_process_query.c	2010-05-28 
18:45:16.000000000 +0000
@@ -3522,6 +3522,18 @@
  				kind = 'E';
  #endif

+
+            // In some cases a PrepareStmt may only happen on master 
node in MASTER/SLAVE mode.
+            // DeallocateStmt will fail in that cases at slave nodes. 
We just ignore that here.
+			if ( master_slave_was_enabled && kind == 'E' && !IS_MASTER_NODE_ID(i) &&
+				pending_function && pending_prepared_portal &&
+				IsA(pending_prepared_portal->stmt, DeallocateStmt)) {
+
+    			pool_debug("Fixed DeallocateStmt error on backend:%d kind:%c 
pending_function:%x pending_prepared_portal:%x",
+			        i, kind, pending_function, pending_prepared_portal);
+				kind = 'C';
+            }
+
  			kind_list[i] = kind;

  			pool_debug("read_kind_from_backend: read kind from %d th backend %c 
NUM_BACKENDS: %d", i, kind_list[i], NUM_BACKENDS);


May be you can commit that to trunk.


Regards,
Jan