[Pgpool-general] Problems with Pgpool-II replication mode

[Tatsuo Ishii]

> Hello,
> Thanks for all your help!!!
> 
> I' ve two new problems with the same environment. I 've two balancers
> nodes with Pgpool-II 3.0.3 instance and two SQL nodes with PostgreSQL
> 8.3.9 instance. I'm using Pgpool-II replication mode.
> Alfresco is one of the applications witch send requests to the cluster
> and return the follow error:
> 
> Cause: org.postgresql.util.PSQLException: ERROR: pgpool detected
> difference of the number of inserted, updated or deleted tuples.
> Possible last query was: "update             alf_lock         set
>        version = $1,             lock_token = $2,
> start_time = $3,             expiry_time
>  at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeUpdate(MappedStatement.java:107)
>  at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.update(SqlMapExecutorDelegate.java:457)
>  at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.update(SqlMapSessionImpl.java:90)
>  at org.springframework.orm.ibatis.SqlMapClientTemplate$9.doInSqlMapClient(SqlMapClientTemplate.java:380)
>  at org.springframework.orm.ibatis.SqlMapClientTemplate$9.doInSqlMapClient(SqlMapClientTemplate.java:1)
>  at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:200)
>  ... 15 more
> Caused by: org.postgresql.util.PSQLException: ERROR: pgpool detected
> difference of the number of inserted, updated or deleted tuples.
> Possible last query was: "update             alf_lock         set
>        version = $1,             lock_token = $2,
> start_time = $3,             expiry_time
>  at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
>  at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
>  at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
>  at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
>  at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388)
>  at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:381)
>  at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
>  at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
>  at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
>  at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeUpdate(SqlExecutor.java:100)
>  at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.sqlExecuteUpdate(MappedStatement.java:216)
>  at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeUpdate(MappedStatement.java:94)
> 
> The Alfresco send a requests group(INSERT, DELETE, UPDATE, SELECT) on
> the same connection but also on different transactions, I mean INSERT
> goes in one transaction, DELETE in the other, on the other the UPDATE,
> and so on. So, then returns that error for the application and the SQL
> node on witch Pgpool-II found the different information is detached.
> So I've many questions here:
> 
> How can I solve this problem?

Pgpool-II detected the number of rows affected by the UPDATE query.
First of all, you need to identify the exact query causing the
problem. Probably you want to take a look at PostgreSQL log with
log_statement enabled. Once you identify the query, you could ask help
from either your application engineer or this mailing list.

> How works pgpool-II replication mode internally?

By sending exact same query (except SELECT) to each PostgreSQL
nodes. Same query will create same databasae. Very simple idea:-)

> How pgPool-II does load balancing?

When client connects to pgpool, it decides which PostgreSQL node
should accept SELECT queries. If SELECT query comes, pgpool sends it
the node.

> How PGPool-ll difference INSERT, DELETE,  SELECT and UPDATE queries?

Pgpool-II parses query to know what kind of query(INSERT, DELETE,
SELECT and UPDATE) it is by using its own parser, which is almost same
as the one in PostgreSQL (except pgpool-II only does syntactic
anlysys, but which is enough for this purpose anyway).

> Please help me, I need some answer !!!
> Thanks
> 
> Maria