[pgpool-general: 2441] Re: Kind-mismatch on pg_locks table

[Tatsuo Ishii]

Sorry I meant was "Pgpool-II does *not* handle multi statment query very well"

> I'm not sure why it's logging errors for this though?

Problem is, the query went to both node 0 and node 1 at the same
time. pg_locks shows the current situation of locking which might be
different among database nodes. As a result, node 0 resturns more rows
than node 1 in this case and triggers the error.

> Most people these days don't have control over the queries that run on
> their database servers (within reason), with developers using database
> ORM's and standard application database frameworks that are used by web
> applications such as Drupal, Django etc...

No way, sorry. Handling multi statement query is one of long standing
TODOs. I believe technically there's some ways to solve it, but it
will need hard work.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> On 17 January 2014 09:58, Tatsuo Ishii <ishii at postgresql.org> wrote:
> 
>> Pgpool-II does handle multi statment query very well (stated in the
>> doc).  As a result your query goes to node 0 and node 1, which causes
>> the difference of the query result because of the nature of the
>> SELECT.
>>
>> Try to not use multi statement query.
>>
>> Best regards,
>> --
>> Tatsuo Ishii
>> SRA OSS, Inc. Japan
>> English: http://www.sraoss.co.jp/index_en.php
>> Japanese: http://www.sraoss.co.jp
>>
>> > Hello,
>> >
>> > I've setup a new PGPool cluster and I'm getting alerts for kind-mismatch
>> on
>> > the pg_locks table.
>> > It is my assumption that the pg_locks table will differ between backends
>> as
>> > pg_pool manages load balanced locking.
>> >
>> > - Do I need to whitelist pg_locks somehow or should I be worried?
>> >
>> >
>> > Jan 17 09:19:53 int-pgpool-01 pgpool[831]: connection received:
>> host=[local]
>> > Jan 17 09:19:53 int-pgpool-01 pgpool[32637]: read_kind_from_backend: 1 th
>> > kind C does not match with master or majority connection kind D
>> > Jan 17 09:19:53 int-pgpool-01 pgpool[32637]: kind mismatch among
>> backends.
>> > Possible last query was: "BEGIN;SET statement_timeout=30000;COMMIT;SELECT
>> > granted, mode, datname FROM pg_locks l RIGHT JOIN pg_database d ON
>> > (d.oid=l.database) WHERE d.datallowconn" kind details are: 0[D] 1[C]
>> > Jan 17 09:19:53 int-pgpool-01 pgpool[32637]: do_child: exits with status
>> 1
>> > due to error
>> > Jan 17 09:19:53 int-pgpool-01 pgpool[628]: connection received:
>> host=[local]
>> >
>> >
>> >    - PostgreSQL 9.3 (Two backends)
>> >    - PGPool-II 3.3.1
>> >    - Replication and load balancing enabled
>> >    - Debian 7, x64
>> >
>> >
>> > Thanks,
>> > Sam.
>>