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

Lazáro Rubén García Martínez lgarciam at vnz.uci.cu
Fri Jul 6 14:39:30 JST 2012 

There is something strange because today I have installed the same version (3.1_STABLE), and it is working properly, and In my pgpool log file, there is not the query "SELECT 1", i think this is not a pgpool query.

I am running the same query through pgpool-II and there is not error.

Is the pgpool-regclass function installed in your database?

Regards.
________________________________________
From: pgpool-general-bounces at pgpool.net [pgpool-general-bounces at pgpool.net] On Behalf Of Soni Maula Harriz [soni.harriz at sangkuriang.co.id]
Sent: Thursday, July 05, 2012 11:03 PM
To: Tatsuo Ishii; pgpool-general at pgpool.net
Subject: [pgpool-general: 704] Re: current transaction is aborted, commands ignored until end of transaction block

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


________________________________
Fin a la injusticia, LIBERTAD AHORA A NUESTROS CINCO COMPATRIOTAS QUE SE ENCUENTRAN INJUSTAMENTE EN PRISIONES DE LOS EEUU!
http://www.antiterroristas.cu
http://justiciaparaloscinco.wordpress.com

Fin a la injusticia, LIBERTAD AHORA A NUESTROS CINCO COMPATRIOTAS QUE SE ENCUENTRAN INJUSTAMENTE EN PRISIONES DE LOS EEUU!
http://www.antiterroristas.cu
http://justiciaparaloscinco.wordpress.com

More information about the pgpool-general mailing list