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).<br><br>Here is the LOG of Pgpool :<br>
<br>2012-07-01 00:00:27 LOG: pid 24113: DB node id: 0 backend pid: 29898 statement: Parse: BEGIN<br>2012-07-01 00:00:27 LOG: pid 24113: DB node id: 0 backend pid: 29898 statement: B message<br>2012-07-01 00:00:27 LOG: pid 24113: DB node id: 0 backend pid: 29898 statement: Execute: BEGIN<br>
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<br>
2012-07-01 00:00:27 LOG: pid 24113: DB node id: 0 backend pid: 29898 statement: B message<br>2012-07-01 00:00:27 LOG: pid 24113: DB node id: 0 backend pid: 29898 statement: D message<br>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<br>
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<br>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<br>
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<br>
<br><br>Here is Log of Postgresql :<br><br>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 <br>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 <br>
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 <br>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 <br>
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') <br>
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"}}')<br>
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<br>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 <br>
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 <br>
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<br>count(*) as jumlah from client_username_log where client_id = 'MULTIPAY-1-' <br>
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 <br>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<br>
<br>the sql command often get this ERROR: current transaction is aborted, commands ignored until end of transaction block<br>this error happens to any query.<br>I have tried some things to repair the situation like :<br> set insert_lock on/off (pgpool)<br>
set parallel_mode on/off (pgpool)<br> /set on_error_rollback on/off (pgsql)<br> /set on_error_stop on/off (pgsql)<br> setAutoCommit true/false (java application), etc.<br>but nothing seems to have an effect.<br>
if the jdbc goes directly to postgresql, there is no problem.<br>and if the query goes directly to pgpool, there is no problem.<br>looks like the problem occurs between JDBC and Pgpool.<br>any idea to solve this ?<br>thanks for the answer.<br clear="all">
<br>-- <br>Best Regards,<br><br>Soni Maula Harriz<br>Database Administrator<br>PT. Data Aksara Sangkuriang<br><br>