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

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


@ Lazáro Rubén García Martínez : the conditions happen when the
pgpool-regclass installed on database. the query just fine when we execute
it through psql to pgpool. it came to error when we execute that query from
an application using jdbc.
@ Tatsuo Ishii : i downloaded the pgpool twice, here is mine :
# md5sum pgpool2-7015e9f.tar.gz
9da084d7854817ae8b2126da2eef0487  pgpool2-7015e9f.tar.gz (this one is
installed and produce the latest error)
# md5sum pgpool2-7015e9f.tar.gz
890b09c21253a1d625777a31dade93a2  pgpool2-7015e9f.tar.gz (this is another
download, not yet implemented)


On Fri, Jul 6, 2012 at 12:22 PM, Tatsuo Ishii <ishii at postgresql.org> wrote:

> 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
>



-- 
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/dd79c918/attachment-0001.html>


More information about the pgpool-general mailing list