[pgpool-general: 712] Re: current transaction is aborted, commands ignored until end of transaction block
Soni Maula Harriz
soni.harriz at sangkuriang.co.id
Mon Jul 9 13:37:01 JST 2012
# /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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20120709/c2b0944a/attachment-0001.html>
More information about the pgpool-general
mailing list