[pgpool-general: 3908] Unexpected error occurred in rollback

Videanu Adrian videanuadrian at yahoo.com
Mon Aug 3 01:32:07 JST 2015

Hi All,
I have a pgpool-II version 3.3.4 (tokakiboshi) Master-Slave setup and two Postgres 9.3.3 servers configured also as Master-Slave with Streaming Replication.On a application side i have a JPA(Hibernate)  app running on a glassfish 4.1 server.
My nodes are defined as:
backend_hostname0 = ''
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/var/lib/postgresql/9.3/data'
backend_flag0= 'ALLOW_TO_FAILOVER'

backend_hostname1 = ''
backend_port1 = 5432
backend_weight1 = 0
backend_data_directory1 = '/var/lib/postgresql/9.3/data'
backend_flag1= 'ALLOW_TO_FAILOVER'

on each postgresql node I have : 

# - Memory -
shared_buffers = 2560MB                 # min 128kB

max_prepared_transactions = 512         # zero disables the feature
                                        # (change requires restart)
# Note:  Increasing max_prepared_transactions costs ~600 bytes of shared memory
# per transaction slot, plus lock space (see max_locks_per_transaction).
# It is not advisable to set max_prepared_transactions nonzero unless you
# actively intend to use prepared transactions.

The problem is that when Node 1 ( is the master (Node0 1.31 slave, after a failover) if something happens with a transaction and need to be rolledback I get : 

JTS5068: Unexpected error occurred in rollback
org.postgresql.xa.PGXAException: Error rolling back prepared transaction
        at org.postgresql.xa.PGXAConnection.rollback(PGXAConnection.java:420)
        at com.sun.gjc.spi.XAResourceImpl.rollback(XAResourceImpl.java:195)
        at com.sun.jts.jta.TransactionState._rollback(TransactionState.java:212)
        at com.sun.jts.jta.TransactionState.rollback(TransactionState.java:180)
        at com.sun.jts.jtsxa.OTSResourceImpl.rollback(OTSResourceImpl.java:333)
        at com.sun.jts.CosTransactions.RegisteredResources.distributeRollback(RegisteredResources.java:1040)
        at com.sun.jts.CosTransactions.TopCoordinator.rollback(TopCoordinator.java:2291)
        at com.sun.jts.CosTransactions.CoordinatorTerm.rollback(CoordinatorTerm.java:530)
        at com.sun.jts.CosTransactions.TerminatorImpl.rollback(TerminatorImpl.java:286)
        at com.sun.jts.CosTransactions.CurrentImpl.rollback(CurrentImpl.java:767)
        at com.sun.jts.jta.TransactionManagerImpl.rollback(TransactionManagerImpl.java:372)

And glassfish server needs to be restarted to kill all connections and open new ones.
But, when Node 0 is the master this issue is not present. I have check and postgresql conf seems to be identical, at least in what concerns XA transactions... 
Any idea what could be the issue here ?
Adrian Videanu

     From: Tatsuo Ishii <ishii at postgresql.org>
 To: liujinfei at xiangrikui.com 
Cc: pgpool-general at pgpool.net 
 Sent: Friday, July 31, 2015 6:40 AM
 Subject: [pgpool-general: 3906] Re: pgpool num_init_children with 10000 Concurrent connections
> Yesterday I have test pgpool with pgbench :
> pgbench -c 30 -T 20 -r pgbench -p9999 -h192.168.8.28
> Concurrent connections is 30, pgpool default num_init_children is 32.
> So, when I set -c 33 ,test will blocked unless I break out.
> My question is :
> If my concurrent connections online is 10000, should I set num_init_children=10000?
> It is terrible that num_init_children=10000 means pgpool start with 10000 process.
> Is there something wrong ?
> How can I config pgpool with 10000 concurrent connections?

That depends how your app behaves. For example if you want to start
10000 psql sessions, you need to set num_init_children=10000. On the
other hand, if your app is someting like web app, which connects to DB
and disconnects very frequently (and each session is very short), you
could set lower value of num_init_children.

Note that pgbench is psql type app.

Best regards,
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php

pgpool-general mailing list
pgpool-general at pgpool.net


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20150802/522beee6/attachment.html>

More information about the pgpool-general mailing list