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

Bruce McAlister bruce.mcalister at blueface.com
Thu May 15 22:12:45 JST 2014


Hi All,

Are there any thoughts on the issue below, am I experiencing a known 
issue here, or do I need to get back to our developer to see if he can 
track it down on the npgsql side of things?

Thanks for your time in reading this message.

Bruce

On 13/05/2014 12:59, Bruce McAlister 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.
>
> 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