# /opt/pgpoolII/bin/pgpool --help<br>pgpool-II version 3.1.3 (hatsuiboshi),<br> A generic connection pool/replication/load balance server for PostgreSQL<br><br>Usage:<br> pgpool [ -c] [ -f CONFIG_FILE ] [ -F PCP_CONFIG_FILE ] [ -a HBA_CONFIG_FILE ]<br>
[ -n ] [ -D ] [ -d ]<br> pgpool [ -f CONFIG_FILE ] [ -F PCP_CONFIG_FILE ] [ -a HBA_CONFIG_FILE ]<br> [ -m SHUTDOWN-MODE ] stop<br> pgpool [ -f CONFIG_FILE ] [ -F PCP_CONFIG_FILE ] [ -a HBA_CONFIG_FILE ] reload<br>
<br>Common options:<br> -a, --hba-file=HBA_CONFIG_FILE<br> Sets the path to the pool_hba.conf configuration file<br> (default: /opt/pgpoolII/etc/pool_hba.conf)<br> -f, --config-file=CONFIG_FILE<br>
Sets the path to the pgpool.conf configuration file<br> (default: /opt/pgpoolII/etc/pgpool.conf)<br> -F, --pcp-file=PCP_CONFIG_FILE<br> Sets the path to the pcp.conf configuration file<br>
(default: /opt/pgpoolII/etc/pcp.conf)<br> -h, --help Prints this help<br><br>Start options:<br> -c, --clear Clears query cache (enable_query_cache must be on)<br> -n, --dont-detach Don't run in daemon mode, does not detach control tty<br>
-D, --discard-status Discard pgpool_status file and do not restore previous status<br> -d, --debug Debug mode<br><br>Stop options:<br> -m, --mode=SHUTDOWN-MODE<br> Can be "smart", "fast", or "immediate"<br>
<br>Shutdown modes are:<br> smart quit after all clients have disconnected<br> fast quit directly, with proper shutdown<br> immediate the same mode as fast<br><br>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.<br>
<br>anyway, if the error appear again, i'll show you the debug log.<br><br>Thanks for the help.<br><br>Great work from Pgpool Global Development Group.<br><br><div class="gmail_quote">On Fri, Jul 6, 2012 at 2:20 PM, Tatsuo Ishii <span dir="ltr"><<a href="mailto:ishii@postgresql.org" target="_blank">ishii@postgresql.org</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">What does "pgpool --help" show?<br>
<br>
P.S. pgpool debug log please.<br>
<div><div>--<br>
Tatsuo Ishii<br>
SRA OSS, Inc. Japan<br>
English: <a href="http://www.sraoss.co.jp/index_en.php" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>
Japanese: <a href="http://www.sraoss.co.jp" target="_blank">http://www.sraoss.co.jp</a><br>
<br>
> @ Lazáro Rubén García Martínez : the conditions happen when the<br>
> pgpool-regclass installed on database. the query just fine when we execute<br>
> it through psql to pgpool. it came to error when we execute that query from<br>
> an application using jdbc.<br>
> @ Tatsuo Ishii : i downloaded the pgpool twice, here is mine :<br>
> # md5sum pgpool2-7015e9f.tar.gz<br>
> 9da084d7854817ae8b2126da2eef0487 pgpool2-7015e9f.tar.gz (this one is<br>
> installed and produce the latest error)<br>
> # md5sum pgpool2-7015e9f.tar.gz<br>
> 890b09c21253a1d625777a31dade93a2 pgpool2-7015e9f.tar.gz (this is another<br>
> download, not yet implemented)<br>
><br>
><br>
> On Fri, Jul 6, 2012 at 12:22 PM, Tatsuo Ishii <<a href="mailto:ishii@postgresql.org" target="_blank">ishii@postgresql.org</a>> wrote:<br>
><br>
>> Sounds like it's not 3.1-stable head. Can you verify md5sum?<br>
>> Here's mine:<br>
>> $ md5sum pgpool2-7015e9f.tar.gz<br>
>> a9d414f9f2654ba462f4cc41a3a156b3 pgpool2-7015e9f.tar.gz<br>
>> --<br>
>> Tatsuo Ishii<br>
>> SRA OSS, Inc. Japan<br>
>> English: <a href="http://www.sraoss.co.jp/index_en.php" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>
>> Japanese: <a href="http://www.sraoss.co.jp" target="_blank">http://www.sraoss.co.jp</a><br>
>><br>
>> > yes, i try<br>
>> ><br>
>> <a href="http://git.postgresql.org/gitweb/?p=pgpool2.git;a=snapshot;h=7015e9f16d34c4f1d28096753638ca78d3d6a7f6;sf=tgz" target="_blank">http://git.postgresql.org/gitweb/?p=pgpool2.git;a=snapshot;h=7015e9f16d34c4f1d28096753638ca78d3d6a7f6;sf=tgz</a><br>
>> ,<br>
>> > and i still get the error.<br>
>> > but now, the error only appear in this query : STATEMENT: SELECT 1<br>
>> > where the query comes from ? is it pgpool query?<br>
>> > when the error happens, the connection closed.<br>
>> > and now the error happens every second, and the application also<br>
>> disconnect<br>
>> > every time we want to access the database.<br>
>> ><br>
>> > On Fri, Jul 6, 2012 at 8:16 AM, Tatsuo Ishii <<a href="mailto:ishii@postgresql.org" target="_blank">ishii@postgresql.org</a>><br>
>> wrote:<br>
>> ><br>
>> >> You mean you tried this but you got the same error?<br>
>> >> >><br>
>> >><br>
>> <a href="http://git.postgresql.org/gitweb/?p=pgpool2.git;a=snapshot;h=7015e9f16d34c4f1d28096753638ca78d3d6a7f6;sf=tgz" target="_blank">http://git.postgresql.org/gitweb/?p=pgpool2.git;a=snapshot;h=7015e9f16d34c4f1d28096753638ca78d3d6a7f6;sf=tgz</a><br>
>> >><br>
>> >> If so, I need this.<br>
>> >> >> Also debug log of pgpool (start pgpool with -d) would be helpfull.<br>
>> >> --<br>
>> >> Tatsuo Ishii<br>
>> >> SRA OSS, Inc. Japan<br>
>> >> English: <a href="http://www.sraoss.co.jp/index_en.php" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>
>> >> Japanese: <a href="http://www.sraoss.co.jp" target="_blank">http://www.sraoss.co.jp</a><br>
>> >><br>
>> >> > we still have this error, only this, but it seems doesn't effect the<br>
>> >> > application's performance. is it normal ?<br>
>> >> ><br>
>> >> > Postgresql log :<br>
>> >> ><br>
>> >> > 2012-07-05 23:40:16 WIT user=postgres db=epayment_report pid=28114<br>
>> >> > command=PARSE 4ff5b343.6dd2 ERROR: current transaction is aborted,<br>
>> >> > commands ignored until end of transaction block<br>
>> >> > 2012-07-05 23:40:16 WIT user=postgres db=epayment_report pid=28114<br>
>> >> > command=PARSE 4ff5b343.6dd2 STATEMENT: SELECT 1<br>
>> >> ><br>
>> >> > On Thu, Jul 5, 2012 at 5:23 PM, Tatsuo Ishii <<a href="mailto:ishii@postgresql.org" target="_blank">ishii@postgresql.org</a>><br>
>> >> wrote:<br>
>> >> ><br>
>> >> >> Recently we have fixed problem with JDBC and unamed statement<br>
>> problem.<br>
>> >> >> Can you try out the lastest snapshot(to be released as 3.1.4) of<br>
>> >> >> pgpool-II 3.1 series? You can get it from:<br>
>> >> >><br>
>> >> >><br>
>> >><br>
>> <a href="http://git.postgresql.org/gitweb/?p=pgpool2.git;a=snapshot;h=7015e9f16d34c4f1d28096753638ca78d3d6a7f6;sf=tgz" target="_blank">http://git.postgresql.org/gitweb/?p=pgpool2.git;a=snapshot;h=7015e9f16d34c4f1d28096753638ca78d3d6a7f6;sf=tgz</a><br>
>> >> >><br>
>> >> >> Also debug log of pgpool (start pgpool with -d) would be helpfull.<br>
>> >> >> --<br>
>> >> >> Tatsuo Ishii<br>
>> >> >> SRA OSS, Inc. Japan<br>
>> >> >> English: <a href="http://www.sraoss.co.jp/index_en.php" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>
>> >> >> Japanese: <a href="http://www.sraoss.co.jp" target="_blank">http://www.sraoss.co.jp</a><br>
>> >> >><br>
>> >> >> > we have a problem in implementing pgpool in testing scenario with<br>
>> this<br>
>> >> >> > environment : (Centos 6 x86_64, 2 Postgresql 8.4 nodes,<br>
>> >> Pgpool-II-3.1.3<br>
>> >> >> in<br>
>> >> >> > replication mode, application with JDBC 4).<br>
>> >> >> ><br>
>> >> >> > Here is the LOG of Pgpool :<br>
>> >> >> ><br>
>> >> >> > 2012-07-01 00:00:27 LOG: pid 24113: DB node id: 0 backend pid:<br>
>> 29898<br>
>> >> >> > statement: Parse: BEGIN<br>
>> >> >> > 2012-07-01 00:00:27 LOG: pid 24113: DB node id: 0 backend pid:<br>
>> 29898<br>
>> >> >> > statement: B message<br>
>> >> >> > 2012-07-01 00:00:27 LOG: pid 24113: DB node id: 0 backend pid:<br>
>> 29898<br>
>> >> >> > statement: Execute: BEGIN<br>
>> >> >> > 2012-07-01 00:00:27 LOG: pid 24113: DB node id: 0 backend pid:<br>
>> 29898<br>
>> >> >> > statement: Parse: select username from client_user ,<br>
>> client_user_role<br>
>> >> >> where<br>
>> >> >> > client_user.username='payment1' and<br>
>> >> >> > client_user.password='5039b3c8ffbdb50a2102bc172ebbf755' and<br>
>> >> >> > (client_user_role.role_name='PAYMENT' or<br>
>> >> >> > client_user_role.role_name='SUPERUSER') and<br>
>> >> >> > client_user.role_id=client_user_role.role_id<br>
>> >> >> > 2012-07-01 00:00:27 LOG: pid 24113: DB node id: 0 backend pid:<br>
>> 29898<br>
>> >> >> > statement: B message<br>
>> >> >> > 2012-07-01 00:00:27 LOG: pid 24113: DB node id: 0 backend pid:<br>
>> 29898<br>
>> >> >> > statement: D message<br>
>> >> >> > 2012-07-01 00:00:27 LOG: pid 24113: DB node id: 0 backend pid:<br>
>> 29898<br>
>> >> >> > statement: Execute: select username from client_user ,<br>
>> >> client_user_role<br>
>> >> >> > where client_user.username='payment1' and<br>
>> >> >> > client_user.password='5039b3c8ffbdb50a2102bc172ebbf755' and<br>
>> >> >> > (client_user_role.role_name='PAYMENT' or<br>
>> >> >> > client_user_role.role_name='SUPERUSER') and<br>
>> >> >> > client_user.role_id=client_user_role.role_id<br>
>> >> >> > 2012-07-01 00:00:27 LOG: pid 24086: DB node id: 0 backend pid:<br>
>> 24972<br>
>> >> >> > statement: Parse: select airline , flightnum from flight_plan where<br>
>> >> true<br>
>> >> >> > order by airline, flightnum<br>
>> >> >> > 2012-07-01 00:00:27 LOG: pid 24086: pool_send_and_wait: Error or<br>
>> >> notice<br>
>> >> >> > message from backend: : DB node id: 0 backend pid: 24972 statement:<br>
>> >> >> select<br>
>> >> >> > airline , flightnum from flight_plan where true order by airline,<br>
>> >> >> flightnum<br>
>> >> >> > message: current transaction is aborted, commands ignored until<br>
>> end of<br>
>> >> >> > transaction block<br>
>> >> >> > 2012-07-01 00:00:27 LOG: pid 24086: Parse: Error or notice<br>
>> message<br>
>> >> from<br>
>> >> >> > backend: : DB node id: 0 backend pid: 24972 statement: select<br>
>> airline<br>
>> >> ,<br>
>> >> >> > flightnum from flight_plan where true order by airline, flightnum<br>
>> >> >> message:<br>
>> >> >> > current transaction is aborted, commands ignored until end of<br>
>> >> transaction<br>
>> >> >> > block<br>
>> >> >> ><br>
>> >> >> ><br>
>> >> >> > Here is Log of Postgresql :<br>
>> >> >> ><br>
>> >> >> > 2012-07-01 00:00:27 WIT user=postgres<br>
>> >> db=psc_sub_terminal_not_using_now<br>
>> >> >> > pid=29898 command=BEGIN 4feef1fc.74ca LOG: execute S_1: BEGIN<br>
>> >> >> > 2012-07-01 00:00:27 WIT user=postgres<br>
>> >> db=psc_sub_terminal_not_using_now<br>
>> >> >> > pid=29898 command=SELECT 4feef1fc.74ca LOG: execute <unnamed>:<br>
>> select<br>
>> >> >> > username from client_user,client_user_role where<br>
>> >> >> > client_user.username='payment1' and<br>
>> >> >> > client_user.password='5039b3c8ffbdb50a2102bc172ebbf755' and<br>
>> >> >> > (client_user_role.role_name='PAYMENT' or<br>
>> >> >> > client_user_role.role_name='SUPERUSER') and<br>
>> >> >> > client_user.role_id=client_user_role.role_id<br>
>> >> >> > 2012-07-01 00:00:27 WIT user=postgres<br>
>> >> db=psc_sub_terminal_not_using_now<br>
>> >> >> > pid=24972 command=PARSE 4fee8f60.618c ERROR: current transaction is<br>
>> >> >> > aborted, commands ignored until end of transaction block<br>
>> >> >> > 2012-07-01 00:00:27 WIT user=postgres<br>
>> >> db=psc_sub_terminal_not_using_now<br>
>> >> >> > pid=24972 command=PARSE 4fee8f60.618c STATEMENT: select airline ,<br>
>> >> >> flightnum<br>
>> >> >> > from flight_plan where true order by airline, flightnum<br>
>> >> >> > 2012-07-01 00:00:27 WIT user=postgres<br>
>> >> db=psc_sub_terminal_not_using_now<br>
>> >> >> > pid=24969 command=idle in transaction 4fee8f60.6189 LOG: statement:<br>
>> >> >> SELECT<br>
>> >> >> > count(*) FROM pg_catalog.pg_attrdef AS d, pg_catalog.pg_class AS c<br>
>> >> WHERE<br>
>> >> >> > d.adrelid = c.oid AND d.adsrc ~ 'nextval' AND c.oid =<br>
>> >> >> > pgpool_regclass('message_log')<br>
>> >> >> > 2012-07-01 00:00:27 WIT user=postgres<br>
>> >> db=psc_sub_terminal_not_using_now<br>
>> >> >> > pid=24969 command=INSERT 4fee8f60.6189 LOG: execute <unnamed>:<br>
>> insert<br>
>> >> >> into<br>
>> >> >> ><br>
>> >> >><br>
>> >><br>
>> message_log(session_id,message_type,thread_id,client_address,log_time,raw_message)<br>
>> >> >> > values ('7F478482136546E980B00C031AA79AC0','client<br>
>> message','Handler<br>
>> >> >> > 9','192.168.165.3','2012-07-01 00:00:27.000889','{"header":<br>
>> >> >> ><br>
>> >> >><br>
>> >><br>
>> "id":1,"address":"192.168.220.1","clientType":"MULTIPAY","messageType":"ECHO"},"body"<br>
>> >> >> > {"username":"payment1","password":"payment1"}}')<br>
>> >> >> > 2012-07-01 00:00:39 WIT user=postgres<br>
>> >> db=psc_sub_terminal_not_using_now<br>
>> >> >> > pid=613 command=SELECT 4fef1fa0.265 LOG: execute <unnamed>: select<br>
>> >> >> username<br>
>> >> >> > from<br>
>> >> >> > client_user , client_user_role where<br>
>> client_user.username='payment1'<br>
>> >> and<br>
>> >> >> > client_user.password='5039b3c8ffbdb50a2102bc172ebbf755' and<br>
>> >> >> > client_user_role.role_name='PAYMENT' or<br>
>> >> >> > client_user_role.role_name='SUPERUSER') and<br>
>> >> >> > client_user.role_id=client_user_role.role_id<br>
>> >> >> > 2012-07-01 00:00:39 WIT user=postgres<br>
>> >> db=psc_sub_terminal_not_using_now<br>
>> >> >> > pid=24123 command=BEGIN 4fee7fe7.5e3b LOG: execute S_1: BEGIN<br>
>> >> 2012-07-01<br>
>> >> >> > 00:00:39 WIT user=postgres db=psc_sub_terminal_not_using_now<br>
>> pid=24123<br>
>> >> >> > command=SELECT 4fee7fe7.5e3b LOG: execute <unnamed>: select<br>
>> airline ,<br>
>> >> >> > flightnum from flight_plan where true order by airline, flightnum<br>
>> >> >> > 2012-07-01 00:00:39 WIT user=postgres<br>
>> >> db=psc_sub_terminal_not_using_now<br>
>> >> >> > pid=24121 command=SELECT 4fee7fe5.5e39 LOG: execute <unnamed>:<br>
>> select<br>
>> >> >> > count(*) as jumlah from client_username_log where client_id =<br>
>> >> >> 'MULTIPAY-1-'<br>
>> >> >> > 2012-07-01 00:00:39 WIT user=postgres<br>
>> >> db=psc_sub_terminal_not_using_now<br>
>> >> >> > pid=656 command=PARSE 4fef1fc8.290 ERROR: current transaction is<br>
>> >> aborted,<br>
>> >> >> > commands ignored until end of transaction block<br>
>> >> >> > 2012-07-01 00:00:39 WIT user=postgres<br>
>> >> db=psc_sub_terminal_not_using_now<br>
>> >> >> > pid=656 command=PARSE 4fef1fc8.290 STATEMENT: update<br>
>> >> client_username_log<br>
>> >> >> > set client_username='payment1' where client_id<br>
>> >> >> ><br>
>> >> >> > the sql command often get this ERROR: current transaction is<br>
>> aborted,<br>
>> >> >> > commands ignored until end of transaction block<br>
>> >> >> > this error happens to any query.<br>
>> >> >> > I have tried some things to repair the situation like :<br>
>> >> >> > set insert_lock on/off (pgpool)<br>
>> >> >> > set parallel_mode on/off (pgpool)<br>
>> >> >> > /set on_error_rollback on/off (pgsql)<br>
>> >> >> > /set on_error_stop on/off (pgsql)<br>
>> >> >> > setAutoCommit true/false (java application), etc.<br>
>> >> >> > but nothing seems to have an effect.<br>
>> >> >> > if the jdbc goes directly to postgresql, there is no problem.<br>
>> >> >> > and if the query goes directly to pgpool, there is no problem.<br>
>> >> >> > looks like the problem occurs between JDBC and Pgpool.<br>
>> >> >> > any idea to solve this ?<br>
>> >> >> > thanks for the answer.<br>
>> >> >> ><br>
>> >> >> > --<br>
>> >> >> > Best Regards,<br>
>> >> >> ><br>
>> >> >> > Soni Maula Harriz<br>
>> >> >> > Database Administrator<br>
>> >> >> > PT. Data Aksara Sangkuriang<br>
>> >> >><br>
>> >> ><br>
>> >> ><br>
>> >> ><br>
>> >> > --<br>
>> >> > Best Regards,<br>
>> >> ><br>
>> >> > Soni Maula Harriz<br>
>> >> > Database Administrator<br>
>> >> > PT. Data Aksara Sangkuriang<br>
>> >><br>
>> ><br>
>> ><br>
>> ><br>
>> > --<br>
>> > Best Regards,<br>
>> ><br>
>> > Soni Maula Harriz<br>
>> > Database Administrator<br>
>> > PT. Data Aksara Sangkuriang<br>
>><br>
><br>
><br>
><br>
> --<br>
> Best Regards,<br>
><br>
> Soni Maula Harriz<br>
> Database Administrator<br>
> PT. Data Aksara Sangkuriang<br>
</div></div></blockquote></div><br><br clear="all"><br>-- <br>Best Regards,<br><br>Soni Maula Harriz<br>Database Administrator<br>PT. Data Aksara Sangkuriang<br><br>