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

[Tatsuo Ishii]

Thanks. I will look into this.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> 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
> _______________________________________________
> Pgpool-hackers mailing list
> Pgpool-hackers at pgfoundry.org
> http://pgfoundry.org/mailman/listinfo/pgpool-hackers