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

[Tatsuo Ishii]

Sounds like it's not 3.1-stable head. Can you verify md5sum?
Here's mine:
$ md5sum pgpool2-7015e9f.tar.gz
a9d414f9f2654ba462f4cc41a3a156b3  pgpool2-7015e9f.tar.gz
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> yes, i try
> http://git.postgresql.org/gitweb/?p=pgpool2.git;a=snapshot;h=7015e9f16d34c4f1d28096753638ca78d3d6a7f6;sf=tgz,
> and i still get the error.
> but now, the error only appear in this query : STATEMENT:  SELECT 1
> where the query comes from ? is it pgpool query?
> when the error happens, the connection closed.
> and now the error happens every second, and the application also disconnect
> every time we want to access the database.
> 
> On Fri, Jul 6, 2012 at 8:16 AM, Tatsuo Ishii <ishii at postgresql.org> wrote:
> 
>> You mean you tried this but you got the same error?
>> >>
>> http://git.postgresql.org/gitweb/?p=pgpool2.git;a=snapshot;h=7015e9f16d34c4f1d28096753638ca78d3d6a7f6;sf=tgz
>>
>> If so, I need this.
>> >> Also debug log of pgpool (start pgpool with -d) would be helpfull.
>> --
>> Tatsuo Ishii
>> SRA OSS, Inc. Japan
>> English: http://www.sraoss.co.jp/index_en.php
>> Japanese: http://www.sraoss.co.jp
>>
>> > we still have this error, only this, but it seems doesn't effect the
>> > application's performance. is it normal ?
>> >
>> > Postgresql log :
>> >
>> > 2012-07-05 23:40:16 WIT user=postgres db=epayment_report pid=28114
>> > command=PARSE 4ff5b343.6dd2 ERROR:  current transaction is aborted,
>> > commands ignored until end of transaction block
>> > 2012-07-05 23:40:16 WIT user=postgres db=epayment_report pid=28114
>> > command=PARSE 4ff5b343.6dd2 STATEMENT:  SELECT 1
>> >
>> > On Thu, Jul 5, 2012 at 5:23 PM, Tatsuo Ishii <ishii at postgresql.org>
>> wrote:
>> >
>> >> Recently we have fixed problem with JDBC and unamed statement problem.
>> >> Can you try out the lastest snapshot(to be released as 3.1.4) of
>> >> pgpool-II 3.1 series? You can get it from:
>> >>
>> >>
>> http://git.postgresql.org/gitweb/?p=pgpool2.git;a=snapshot;h=7015e9f16d34c4f1d28096753638ca78d3d6a7f6;sf=tgz
>> >>
>> >> Also debug log of pgpool (start pgpool with -d) would be helpfull.
>> >> --
>> >> Tatsuo Ishii
>> >> SRA OSS, Inc. Japan
>> >> English: http://www.sraoss.co.jp/index_en.php
>> >> Japanese: http://www.sraoss.co.jp
>> >>
>> >> > 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
>> >>
>> >
>> >
>> >
>> > --
>> > Best Regards,
>> >
>> > Soni Maula Harriz
>> > Database Administrator
>> > PT. Data Aksara Sangkuriang
>>
> 
> 
> 
> -- 
> Best Regards,
> 
> Soni Maula Harriz
> Database Administrator
> PT. Data Aksara Sangkuriang