[Pgpool-general] pgpool-II 2.0.1: "pool_process_query: 1 th kind D does not match with master connection kind C" error

[Anthony MacKinnon]

Thanks for the reply.

We already have log_statement set to true in our configuration. Here is what
we see:


2007-11-29 02:00:18 LOG:   pid 11118: statement: SET SESSION AUTHORIZATION
some_account;select * from some_table where (some_column='value') AND
(other_column='43')

2007-11-29 02:00:19 LOG:   pid 11118: statement: SET SESSION AUTHORIZATION
some_account;

2007-11-29 02:00:19 LOG:   pid 11118: statement: SET SESSION AUTHORIZATION
some_account;SELECT * FROM some_table  where id in
(3186,2712,2713,...,4579,4580,4581) ORDER BY some_column ASC

2007-11-29 02:00:19 LOG:   pid 11118: statement: SET SESSION AUTHORIZATION
some_account;SELECT astext(transform(geo,4326)) ,* from
some_schema.some_table,other_table WHERE ( (gpstime >= timestamp '2007-11-29
00:37:00') AND (gpstime <= timestamp '2007-11-29 02:06:00')) AND (
some_schema.some_table.some_column=other_table.id) AND (other_table.id
in(3186,2712,2713,...,4579,4580,4581))

2007-11-29 02:00:19 ERROR: pid 11118: pool_process_query: 1 th kind D does
not match with master connection kind C

2007-11-29 02:00:19 LOG:   pid 11118: notice_backend_error: 1 fail over
request from pid 11118


In two of those SQL statements, I've removed hundreds of other IDs and
replaced them with "..." to make it readable. As you can see, we are not
using transactions in this particular case. As I wrote earlier, we are
running load balance mode with replication. SELECT statements therefore do
not get executed on all nodes, correct? How does pgpool detect a data
difference in this case?

Regards,
Anthony

-----Original Message-----
From: Tatsuo Ishii [mailto:ishii at sraoss.co.jp] 
Sent: Friday, December 21, 2007 5:35 AM
To: anthony.mackinnon at waypointinfo.com
Cc: ishii at sraoss.co.jp; pgpool-general at pgfoundry.org;
perry.casson at waypointinfo.com
Subject: Re: [Pgpool-general] pgpool-II 2.0.1: "pool_process_query: 1 th
kind D does not match with master connection kind C" error

Sorry for the delay.

We are doing a commercial support for pgpool. The pricing is 800,000
Japanese yen excluding TAX(about US $7k) per year including up to 4
PostgreSQL servers' support. If it's too high, I'd be happy to
continue the community support.

Any way...

Yes, this is a question too.

> [Anthony] We are running in load balance mode with replication, which
means
> that SELECT statements do not get executed on all nodes, correct? How does
> pgpool detect a data difference in this case?

One possibility is, the SELECT statement is inside a transaction.
Can you set log_statement = true so that you could see the SELECT
statement which causes the problem?
--
Tatsuo Ishii
SRA OSS, Inc. Japan

> Thanks for your reply.
> 
> I've added answers to yours questions below.
> 
> One thing we found in the latest documentation is that SERIAL columns
> require a LOCK TABLE. We are not currently doing that and it would be
> somewhat painful for us since we are using views in a lot of places.
Before
> we go ahead and try this, do you think that it could be causing the sync
> issues we are seeing?
> 
> In order to get things working as fast as possible, it would be great to
get
> on a phone call with you to discuss this further. We would be happy to pay
> for your time.
> 
> Regards,
> Anthony
> 
> 
> -----Original Message-----
> From: Tatsuo Ishii [mailto:ishii at sraoss.co.jp] 
> Sent: Monday, December 03, 2007 6:27 PM
> To: perry.casson at waypointinfo.com
> Cc: pgpool-general at pgfoundry.org
> Subject: Re: [Pgpool-general] pgpool-II 2.0.1: "pool_process_query: 1 th
> kind D does not match with
> master connection kind C" error
> 
> > We are testing pgpool-II-2.0.1 in replication mode and are trying to
> figure out why pgpool-II is
> > deprecating.  In our cases the system will usually run fine for 30-90
> minutes processing dozens of
> > transactions/second then we get the following error: pool_process_query:
1
> th kind D does not
> match
> > with master connection kind C and then pgpool drops all nodes but the
> master
> > 
> > In every case we have found it will always happen after a specific very
> large select statement
> (see
> > pid1118.log).  This statement is valid and similar statements have been
> ran 1000's of times before
> > the error occurs.
> 
> Can you be more specific? I mean If pgpool says "1 th kind D does not
> match with master connection kind C", there must be data difference
> among tables in both servers. Since you know the SELECT statement that
> causes the error, you could manualy issues the SELECT and see
> the data difference in tables. Can you veryfy it?
> 
> [Anthony] We are running in load balance mode with replication, which
means
> that SELECT statements do not get executed on all nodes, correct? How does
> pgpool detect a data difference in this case?
> 
> > I've started to look through the source code to try to figure out what's
> going on but that's going
> > to be a slow process just to understand what should be happening.
> > 
> > Keen to get this resolved and could arrange access to these servers if
> required or clues on how to
> > resolve this.
> 
> How do you INSERT/UPDATE
> some_customer.wp_point,wp_sentinelconfig.gpstime? If it's default to
> CURRENT_TIMESTAMP, it will not be safe for pgpool since it depends on
> the timing of query execution on db1 and db2, which will not be
> identical.
> 
> [Anthony] We do not use CURRENT_TIMESTAMP. All the timestamps we use are
> input data or generated in code before the insert.
> 
> 
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
>