[pgpool-general: 7577] Re: jboss - jdbc - pgpool

Tue May 18 16:30:14 JST 2021

Hello Tatsuo,
Thank you for your answer. I checked pgpool log and i found such errors:
2021-05-18T09:13:15.974748+02:00 pgs00000205 pgpool[18000]: [18-1] 2021-05-18 09:13:15: pid 18000: FATAL:  connection was terminated due to conflict with recovery
2021-05-18T09:13:15.975235+02:00 pgs00000205 pgpool[18000]: [18-2] 2021-05-18 09:13:15: pid 18000: DETAIL:  User was holding a relation lock for too long.
2021-05-18T09:13:15.975436+02:00 pgs00000205 pgpool[18000]: [18-3] 2021-05-18 09:13:15: pid 18000: HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2021-05-18T09:13:15.978130+02:00 pgs00000205 pgpool[17201]: [19-1] 2021-05-18 09:13:15: pid 17201: LOG:  child process with pid: 18000 exits with status 256
2021-05-18T09:13:15.978502+02:00 pgs00000205 pgpool[17201]: [20-1] 2021-05-18 09:13:15: pid 17201: LOG:  fork a new child process with pid: 21211
2021-05-18T09:13:15.979342+02:00 pgs00000205 pgpool[17534]: [40-1] 2021-05-18 09:13:15: pid 17534: FATAL:  connection was terminated due to conflict with recovery
2021-05-18T09:13:15.979515+02:00 pgs00000205 pgpool[17534]: [40-2] 2021-05-18 09:13:15: pid 17534: DETAIL:  User was holding a relation lock for too long.
2021-05-18T09:13:15.979642+02:00 pgs00000205 pgpool[17534]: [40-3] 2021-05-18 09:13:15: pid 17534: HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2021-05-18T09:13:15.981441+02:00 pgs00000205 pgpool[17201]: [21-1] 2021-05-18 09:13:15: pid 17201: LOG:  child process with pid: 17534 exits with status 256
2021-05-18T09:13:15.982657+02:00 pgs00000205 pgpool[17201]: [22-1] 2021-05-18 09:13:15: pid 17201: LOG:  fork a new child process with pid: 21212
2021-05-18T09:13:15.984406+02:00 pgs00000205 pgpool[18310]: [18-1] 2021-05-18 09:13:15: pid 18310: FATAL:  connection was terminated due to conflict with recovery
database and repeat your command.
I have synchronous replication set on my two-node postgresql cluster (synchronous_commit parameter has a value of "remote_apply") - may it be a matter of synchronization problems between postgresql nodes? I haven't noticed any delays.
Best regards

Andrzej Rozmus
Starszy Technolog
Departament Systemów Administracji Rządowej
Dział Ministerstwo Finansów
Zespół Projektowy AIS/AES
Asseco Poland S.A.
ul. Branickiego 13
02-972 Warszawa
tel. kom. +48 502 270 059
andrzej.rozmus at asseco.pl

-----Original Message-----
From: Tatsuo Ishii <ishii at sraoss.co.jp> 
Sent: Tuesday, May 18, 2021 2:30 AM
To: Rozmus Andrzej <Andrzej.Rozmus at asseco.pl>
Cc: pgpool-general at pgpool.net; Wolanin Michał <michal.wolanin at asseco.pl>; Wierzbicki Sebastian <sebastian.wierzbicki at asseco.pl>; Mazurek Fryderyk <fryderyk.mazurek at asseco.pl>
Subject: Re: [pgpool-general: 7575] jboss - jdbc - pgpool

> Hello Tatsuo,
> I have encountered a problem in communication between Jboss and pgpool using jdbc drivers. This is Jboss output:
>     Caused by: org.postgresql.util.PSQLException: This connection has been closed.
>         at org.postgresql.jdbc.PgConnection.checkClosed(PgConnection.java:861)
>         at org.postgresql.jdbc.PgConnection.prepareStatement(PgConnection.java:1767)
>         at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.doPrepareStatement(BaseWrapperManagedConnection.java:732)
>         at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.prepareStatement(BaseWrapperManagedConnection.java:718)
>         at org.jboss.jca.adapters.jdbc.WrappedConnection.prepareStatement(WrappedConnection.java:404)
>         at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:161) [hibernate-core-4.2.5.Final.jar:4.2.5.Final]
>         at 
> org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPrep
> arationTemplate.prepareStatement(StatementPreparerImpl.java:182) [hibernate-core-4.2.5.Fina l.jar:4.2.5.Final] Version of pgpool in our environment is 4.0.5 and jdbc driver used in Jboss is 42.2.11.jre7. We plan to upgrade pgpool to at least 4.1.4, but i'm not sure if it will resolve problem. Do you have any idea if above error is a matter of improper configuration of pgpool or bug in jdbc driver/pgpool? Two yaers ago we had also problem connecting to pgpool via jdbc drivers and upgrade from 4.0.1 to 4.0.5 helped us because of bug fixed in 4.0.5.

It seems pgpool closed the connection from the client. There are number of reasons pgpool decides to close the connection to client. To clarify the reason, you'd better to share the pgpool log when pgpool closed the connection.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: https://eur02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.sraoss.co.jp%2Findex_en.php&data=04%7C01%7CAndrzej.Rozmus%40asseco.pl%7C88e2b6d5e3d842ded58108d9199406e0%7C88152bdecfa34a5cb981a785c624bb42%7C0%7C0%7C637568946048265578%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=G8MhEjjU%2B0lJ4YZ2u11aOUHEa%2BE7Y66tgAeeXCq1Xek%3D&reserved=0
Japanese:https://eur02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.sraoss.co.jp%2F&data=04%7C01%7CAndrzej.Rozmus%40asseco.pl%7C88e2b6d5e3d842ded58108d9199406e0%7C88152bdecfa34a5cb981a785c624bb42%7C0%7C0%7C637568946048265578%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=w667QuZLwm0DSRHLuP%2BZS5lXnjwIlefFt9ZbVRmxm28%3D&reserved=0