[pgpool-general: 696] current transaction is aborted, commands ignored until end of transaction block

[Soni Maula Harriz]

we have a problem in implementing pgpool in testing scenario with this
environment : (Centos 6 x86_64, 2 Postgresql 8.4 nodes, Pgpool-II-3.1.3 in
replication mode, application with JDBC 4).

Here is the LOG of Pgpool :

2012-07-01 00:00:27 LOG:   pid 24113: DB node id: 0 backend pid: 29898
statement: Parse: BEGIN
2012-07-01 00:00:27 LOG:   pid 24113: DB node id: 0 backend pid: 29898
statement: B message
2012-07-01 00:00:27 LOG:   pid 24113: DB node id: 0 backend pid: 29898
statement: Execute: BEGIN
2012-07-01 00:00:27 LOG:   pid 24113: DB node id: 0 backend pid: 29898
statement: Parse: select username from client_user , client_user_role where
client_user.username='payment1' and
client_user.password='5039b3c8ffbdb50a2102bc172ebbf755' and
(client_user_role.role_name='PAYMENT' or
client_user_role.role_name='SUPERUSER') and
client_user.role_id=client_user_role.role_id
2012-07-01 00:00:27 LOG:   pid 24113: DB node id: 0 backend pid: 29898
statement: B message
2012-07-01 00:00:27 LOG:   pid 24113: DB node id: 0 backend pid: 29898
statement: D message
2012-07-01 00:00:27 LOG:   pid 24113: DB node id: 0 backend pid: 29898
statement: Execute: select username from client_user , client_user_role
where client_user.username='payment1' and
client_user.password='5039b3c8ffbdb50a2102bc172ebbf755' and
(client_user_role.role_name='PAYMENT' or
client_user_role.role_name='SUPERUSER') and
client_user.role_id=client_user_role.role_id
2012-07-01 00:00:27 LOG:   pid 24086: DB node id: 0 backend pid: 24972
statement: Parse: select airline , flightnum from flight_plan where true
order by airline, flightnum
2012-07-01 00:00:27 LOG:   pid 24086: pool_send_and_wait: Error or notice
message from backend: : DB node id: 0 backend pid: 24972 statement: select
airline , flightnum from flight_plan where true order by airline, flightnum
message: current transaction is aborted, commands ignored until end of
transaction block
2012-07-01 00:00:27 LOG:   pid 24086: Parse: Error or notice message from
backend: : DB node id: 0 backend pid: 24972 statement: select airline ,
flightnum from flight_plan where true order by airline, flightnum message:
current transaction is aborted, commands ignored until end of transaction
block


Here is Log of Postgresql :

2012-07-01 00:00:27 WIT user=postgres db=psc_sub_terminal_not_using_now
pid=29898 command=BEGIN 4feef1fc.74ca LOG: execute S_1: BEGIN
2012-07-01 00:00:27 WIT user=postgres db=psc_sub_terminal_not_using_now
pid=29898 command=SELECT 4feef1fc.74ca LOG: execute <unnamed>: select
username from client_user,client_user_role where
client_user.username='payment1' and
client_user.password='5039b3c8ffbdb50a2102bc172ebbf755' and
(client_user_role.role_name='PAYMENT' or
client_user_role.role_name='SUPERUSER') and
client_user.role_id=client_user_role.role_id
2012-07-01 00:00:27 WIT user=postgres db=psc_sub_terminal_not_using_now
pid=24972 command=PARSE 4fee8f60.618c ERROR: current transaction is
aborted, commands ignored until end of transaction block
2012-07-01 00:00:27 WIT user=postgres db=psc_sub_terminal_not_using_now
pid=24972 command=PARSE 4fee8f60.618c STATEMENT: select airline , flightnum
from flight_plan where true order by airline, flightnum
2012-07-01 00:00:27 WIT user=postgres db=psc_sub_terminal_not_using_now
pid=24969 command=idle in transaction 4fee8f60.6189 LOG: statement: SELECT
count(*) FROM pg_catalog.pg_attrdef AS d, pg_catalog.pg_class AS c WHERE
d.adrelid = c.oid AND d.adsrc ~ 'nextval' AND c.oid =
pgpool_regclass('message_log')
2012-07-01 00:00:27 WIT user=postgres db=psc_sub_terminal_not_using_now
pid=24969 command=INSERT 4fee8f60.6189 LOG: execute <unnamed>: insert into
message_log(session_id,message_type,thread_id,client_address,log_time,raw_message)
values ('7F478482136546E980B00C031AA79AC0','client message','Handler
9','192.168.165.3','2012-07-01 00:00:27.000889','{"header":
"id":1,"address":"192.168.220.1","clientType":"MULTIPAY","messageType":"ECHO"},"body"
{"username":"payment1","password":"payment1"}}')
2012-07-01 00:00:39 WIT user=postgres db=psc_sub_terminal_not_using_now
pid=613 command=SELECT 4fef1fa0.265 LOG: execute <unnamed>: select username
from
client_user , client_user_role where client_user.username='payment1' and
client_user.password='5039b3c8ffbdb50a2102bc172ebbf755' and
client_user_role.role_name='PAYMENT' or
client_user_role.role_name='SUPERUSER') and
client_user.role_id=client_user_role.role_id
2012-07-01 00:00:39 WIT user=postgres db=psc_sub_terminal_not_using_now
pid=24123 command=BEGIN 4fee7fe7.5e3b LOG: execute S_1: BEGIN 2012-07-01
00:00:39 WIT user=postgres db=psc_sub_terminal_not_using_now pid=24123
command=SELECT 4fee7fe7.5e3b LOG: execute <unnamed>: select airline ,
flightnum from flight_plan where true order by airline, flightnum
2012-07-01 00:00:39 WIT user=postgres db=psc_sub_terminal_not_using_now
pid=24121 command=SELECT 4fee7fe5.5e39 LOG: execute <unnamed>: select
count(*) as jumlah from client_username_log where client_id = 'MULTIPAY-1-'
2012-07-01 00:00:39 WIT user=postgres db=psc_sub_terminal_not_using_now
pid=656 command=PARSE 4fef1fc8.290 ERROR: current transaction is aborted,
commands ignored until end of transaction block
2012-07-01 00:00:39 WIT user=postgres db=psc_sub_terminal_not_using_now
pid=656 command=PARSE 4fef1fc8.290 STATEMENT: update client_username_log
set client_username='payment1' where client_id

the sql command often get this ERROR: current transaction is aborted,
commands ignored until end of transaction block
this error happens to any query.
I have tried some things to repair the situation like :
    set insert_lock on/off (pgpool)
    set parallel_mode on/off (pgpool)
    /set on_error_rollback on/off (pgsql)
    /set on_error_stop on/off (pgsql)
    setAutoCommit true/false (java application), etc.
but nothing seems to have an effect.
if the jdbc goes directly to postgresql, there is no problem.
and if the query goes directly to pgpool, there is no problem.
looks like the problem occurs between JDBC and Pgpool.
any idea to solve this ?
thanks for the answer.

-- 
Best Regards,

Soni Maula Harriz
Database Administrator
PT. Data Aksara Sangkuriang
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20120705/157f072c/attachment.html>