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

Yugo Nagata nagata at sraoss.co.jp
Tue May 27 19:53:24 JST 2014


Hi,

On Tue, 13 May 2014 12:59:12 +0100
Bruce McAlister <bruce.mcalister at blueface.com> wrote:

> 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.

pgpool-gerenal is sutable for bug report or general questions.

> 
> 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.

I can't reprode the error using the following queries; 

 BEGIN; SET TRANSACTINO ISOLATINO LEVEL READ COMMITTED;
 SET statement_timeout = 1000;
 SELECT p.proname, p.oid FROM pg_catalog.pg_proc p, pg_catalog.pg......
 COMMIT;

pgpool log with debug mode might be helpful for analysis. However, pgpool
doesn't support multi-statement query properly as a restriction, anyway.

> 
> 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
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general


-- 
Yugo Nagata <nagata at sraoss.co.jp>


More information about the pgpool-general mailing list