[Pgpool-general] "BEGIN; LOCK TABLE" sends to slave in Master/Slave mode

[Tatsuo Ishii]

From: Успенский Игорь <admin at bestly.ru>
Subject: [Pgpool-general] "BEGIN; LOCK TABLE" sends to slave in Master/Slave mode
Date: Fri, 21 Oct 2011 09:48:48 +0400
Message-ID: <1319176128.17769.42.camel at admin.bestly.ru>

> Hello!
> 
> I using pgpool2 v3.1 and two PostgreSQL 9.0.4 (Master/Slave with stream
> replication)
> 
> Client send query "BEGIN;LOCK TABLE _1SCONNECT IN EXCLUSIVE MODE;" and
> pgpool sends this query master and slave, after slave print to log
> "cannot execute LOCK TABLE during recovery". Why pgpool sends this query
> to slave? How to change this behavior?

It's a know limitation of pgpool-II.

From:
http://pgpool.projects.postgresql.org/pgpool-II/doc/pgpool-en.html#restriction

> pgpool-II cannot process multi-statement queries. 

BEGIN;LOCK TABLE _1SCONNECT IN EXCLUSIVE MODE; is recognized as
"BEGIN;" statement by pgpool-II and it thinks that it needed to sent
to both master and slave.

The solution is to avoid multi-statement queries like BEGIN;LOCK TABLE_1SCONNECT IN EXCLUSIVE MODE;

Instead:

BEGIN;
LOCK TABLE _1SCONNECT IN EXCLUSIVE MODE;
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp