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

Soni Maula Harriz soni.harriz at sangkuriang.co.id
Fri Jul 6 12:33:37 JST 2012 

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20120706/c30e515c/attachment.html>

More information about the pgpool-general mailing list