[pgpool-general: 2822] COMMIT sent to read-only slave

Bruce McAlister bruce.mcalister at blueface.com
Tue May 13 20:59:12 JST 2014


Hi All,

I am fairly new to pgpool, so please bear with me :)

Sorry for the cross-post, I wasnt sure which list to send this question to.

We are currently testing pgpool-ii v3.3.3 in front of a 3 node 
postgresql cluster, the configuration is as follows:

PostgreSQL
     Pacemaker master/slave resource using pgsql resource in streaming 
replication mode
PGPool
     Pacemaker failover resource using pgpool-ii-ha v2.2

We are testing using a C# client that is attempting to insert a large 
object into postgresql via pgpool, what apears to be happening is that 
the npgsql driver sends a multistatement begin and set command via 
pgpool, which sends this over to dbnode 2 (write master). It then 
attempts to insert the lo and sends a finishing commit statement. The 
commit here is sent to the master, and the selected read slave, however, 
the read slave in this case is unaware of the transaction begin 
statement and fails. (This is my understanding of it as I read the log, 
please correct me if I am wrong).

I searched the mailing list archives to see if anyone else had come 
accross this particular issue, and I came accross 2 posts that I thought 
may be relevant to this:

Multi-statement queries:
http://www.pgpool.net/pipermail/pgpool-hackers/2014-February/000453.html

Commit sent to read-only slave:
http://www.pgpool.net/pipermail/pgpool-hackers/2012-July/000089.html

Is this a bug, or can our developer work around this issue in some way.

Has anyone come accross this particular behaviour before? Currently this 
is a show stopper for us for migrating onto pgpool, so any info that you 
may need please let me know I will attempt to provide it as needed, or, 
is there something we can do in pgpool that I may have missed?

The actual log of the failure is below:

pgpl-prod[12287]: connection received: host=hidden.com port=1401
pgpl-prod[12287]: DB node id: 2 backend pid: 28112 statement: SET 
extra_float_digits=3;SET ssl_renegotiation_limit=0;
pgpl-prod[12287]: DB node id: 2 backend pid: 28112 statement: BEGIN; SET 
TRANSACTION ISOLATION LEVEL READ COMMITTED;
pgpl-prod[12287]: DB node id: 1 backend pid: 3419 statement: BEGIN
pgpl-prod[12287]: DB node id: 1 backend pid: 3419 statement: SET 
statement_timeout = 60000
pgpl-prod[12287]: DB node id: 2 backend pid: 28112 statement: SET 
statement_timeout = 60000
pgpl-prod[12287]: DB node id: 1 backend pid: 3419 statement: COMMIT
pgpl-prod[12287]: DB node id: 0 backend pid: 29805 statement: SELECT 
count(*) FROM pg_class AS c, pg_namespace AS n WHERE c.relname = 
'pg_proc' AND c.relnamespace = n.oid AND n.nspname ~ '^pg_temp_'
pgpl-prod[12287]: DB node id: 0 backend pid: 29805 statement: SELECT 
count(*) FROM pg_class AS c, pg_namespace AS n WHERE c.relname = 
'pg_namespace' AND c.relnamespace = n.oid AND n.nspname ~ '^pg_temp_'
pgpl-prod[12287]: DB node id: 0 backend pid: 29805 statement: SELECT 
count(*) FROM pg_class AS c, pg_namespace AS n WHERE c.oid = 
pgpool_regclass('pg_proc') AND c.relnamespace = n.oid AND n.nspname = 
'pg_catalog'
pgpl-prod[12287]: DB node id: 0 backend pid: 29805 statement: SELECT 
count(*) FROM pg_class AS c, pg_namespace AS n WHERE c.oid = 
pgpool_regclass('pg_namespace') AND c.relnamespace = n.oid AND n.nspname 
= pg_catalog'
pgpl-prod[12287]: DB node id: 2 backend pid: 28112 statement: SELECT 
p.proname,p.oid  FROM pg_catalog.pg_proc p, pg_catalog.pg_namespace n  
WHERE p.pronamespace=n.oid AND n.nspname='pg_catalog' AND ( proname = 
'lo_open' or proname = 'lo_close' or proname = 'lo_creat' or proname = 
'lo_unlink' or proname = 'lo_lseek' or proname = 'lo_tell' or proname = 
'loread' or proname = 'lowrite')
pgpl-prod[12287]: DB node id: 2 backend pid: 28112 statement: COMMIT
pgpl-prod[12287]: DB node id: 1 backend pid: 3419 statement: COMMIT
pgpl-prod[12287]: pool_send_and_wait: Error or notice message from 
backend: : DB node id: 1 backend pid: 3419 statement: COMMIT message: 
there is no transaction in progress
pgpl-prod[12287]: read_kind_from_backend: 2 th kind C does not match 
with master or majority connection kind N
pgpl-prod[12287]: kind mismatch among backends. Possible last query was: 
"COMMIT" kind details are: 1[N: there is no transaction in progress] 2[C]
pgpl-prod[12287]: do_child: exits with status 1 due to error

Thank you for your time looking at this

Bruce


More information about the pgpool-general mailing list