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

Fri Aug 3 22:30:21 JST 2012

> dear Tatsuo Ishii,
> 
> is the new release version (pgpool-II 3.2.0) has included change you stated
> in this link
> http://git.postgresql.org/gitweb/?p=pgpool2.git;a=snapshot;h=7015e9f16d34c4f1d28096753638ca78d3d6a7f6;sf=tgz?

Yes, I think so.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> because you said that the link is update for pgpool-II 3.1.4.
> 
> is pgpool-II 3.2.0 has overcomed the issue with jdbc ?
> because we would like to test pgpool-II 3.2.0 with jdbc application.
> 
> thanks for the answer.
> 
> 
> On Mon, Jul 9, 2012 at 11:37 AM, Soni Maula Harriz <
> soni.harriz at sangkuriang.co.id> wrote:
> 
>> # /opt/pgpoolII/bin/pgpool --help
>> pgpool-II version 3.1.3 (hatsuiboshi),
>>   A generic connection pool/replication/load balance server for PostgreSQL
>>
>> Usage:
>>   pgpool [ -c] [ -f CONFIG_FILE ] [ -F PCP_CONFIG_FILE ] [ -a
>> HBA_CONFIG_FILE ]
>>          [ -n ] [ -D ] [ -d ]
>>   pgpool [ -f CONFIG_FILE ] [ -F PCP_CONFIG_FILE ] [ -a HBA_CONFIG_FILE ]
>>          [ -m SHUTDOWN-MODE ] stop
>>   pgpool [ -f CONFIG_FILE ] [ -F PCP_CONFIG_FILE ] [ -a HBA_CONFIG_FILE ]
>> reload
>>
>> Common options:
>>   -a, --hba-file=HBA_CONFIG_FILE
>>                       Sets the path to the pool_hba.conf configuration file
>>                       (default: /opt/pgpoolII/etc/pool_hba.conf)
>>   -f, --config-file=CONFIG_FILE
>>                       Sets the path to the pgpool.conf configuration file
>>                       (default: /opt/pgpoolII/etc/pgpool.conf)
>>   -F, --pcp-file=PCP_CONFIG_FILE
>>                       Sets the path to the pcp.conf configuration file
>>                       (default: /opt/pgpoolII/etc/pcp.conf)
>>   -h, --help          Prints this help
>>
>> Start options:
>>   -c, --clear         Clears query cache (enable_query_cache must be on)
>>   -n, --dont-detach   Don't run in daemon mode, does not detach control tty
>>   -D, --discard-status Discard pgpool_status file and do not restore
>> previous status
>>   -d, --debug         Debug mode
>>
>> Stop options:
>>   -m, --mode=SHUTDOWN-MODE
>>                       Can be "smart", "fast", or "immediate"
>>
>> Shutdown modes are:
>>   smart       quit after all clients have disconnected
>>   fast        quit directly, with proper shutdown
>>   immediate   the same mode as fast
>>
>> after doing more testing on the next day (about 1000 to 2000
>> transactions), the error doesn't appear anymore, and i can't imagine how
>> could this happen. i didn't do any change since the error.
>>
>> anyway, if the error appear again, i'll show you the debug log.
>>
>> Thanks for the help.
>>
>> Great work from Pgpool Global Development Group.
>>
>>
>> On Fri, Jul 6, 2012 at 2:20 PM, Tatsuo Ishii <ishii at postgresql.org> wrote:
>>
>>> What does "pgpool --help" show?
>>>
>>> P.S. pgpool debug log please.
>>> --
>>> Tatsuo Ishii
>>> SRA OSS, Inc. Japan
>>> English: http://www.sraoss.co.jp/index_en.php
>>> Japanese: http://www.sraoss.co.jp
>>>
>>> > @ 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
>>>
>>
>>
>>
>> --
>> Best Regards,
>>
>> Soni Maula Harriz
>> Database Administrator
>> PT. Data Aksara Sangkuriang
>>
>>
> 
> 
> -- 
> Best Regards,
> 
> Soni Maula Harriz
> Database Administrator
> PT. Data Aksara Sangkuriang