Thread: Improvements for statement cancellation
Hi.
I have a nasty issue which involves WebLogic, pgpool-II and Postgres JDBC.
WebLogic has a habit on calling Statement.cancel() on all cached (by itself) statements, when closing it's connection pools. It also calls Statement.cancel() in some other circumstances, as far as I can understand, and example is an abnormal transaction rollback.
Postgres JDBC, on Statement.cancel(), unconditionally calls Connection.cancelQuery().
pgpool-II has a special code that sleeps for one second before releasing cancel request (rather, between sending cancel requests to all backends involved in a connection). It has its reasons for doing that sleep.
This leaves me with a neraly-locked up system sometimes, as the cancellation process can take a very long time (these seconds do add up). Below is a sample stack trace of how it looks like, code path-wise.
I'm wondering if it's possible if Postgres JDBC could change the implementation of cancel() so that cancelQuery() is only sent if the statement being called is the actual statement that is being executed on the connection, and otherwise be a no-op.
at java.net.SocketInputStream.socketRead0(Native Method)
at java.net.SocketInputStream.read(SocketInputStream.java:129)
at org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:143)
at org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:112)
at org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:71)
at org.postgresql.core.PGStream.ReceiveEOF(PGStream.java:530)
at org.postgresql.core.v3.ProtocolConnectionImpl.sendQueryCancel(ProtocolConnectionImpl.java:98)
at org.postgresql.jdbc2.AbstractJdbc2Connection.cancelQuery(AbstractJdbc2Connection.java:1023)
at org.postgresql.jdbc2.AbstractJdbc2Statement.cancel(AbstractJdbc2Statement.java:2775)
at sun.reflect.GeneratedMethodAccessor123.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.postgresql.ds.jdbc23.AbstractJdbc23PooledConnection$StatementHandler.invoke(AbstractJdbc23PooledConnection.java:453)
at $Proxy50.cancel(Unknown Source)
at weblogic.jdbc.common.internal.ConnectionEnv.cancelStatements(ConnectionEnv.java:703)
On 4/10/2013 6:47 PM, Pawel Veselov wrote: > pgpool-II has a special code that sleeps for one second before > releasing cancel request (rather, between sending cancel requests to > all backends involved in a connection). It has its reasons for doing > that sleep. there may be reasons, but anything that relies on an arbitrary timeout like 1 second is, IMHO, poorly designed. > > This leaves me with a neraly-locked up system sometimes, as the > cancellation process can take a very long time (these seconds do add > up). Below is a sample stack trace of how it looks like, code path-wise. > > I'm wondering if it's possible if Postgres JDBC could change the > implementation of cancel() so that cancelQuery() is only sent if the > statement being called is the actual statement that is being executed > on the connection, and otherwise be a no-op. why use pgpool then? use weblogic's native java connection queuing. -- john r pierce 37N 122W somewhere on the middle of the left coast
Pawel Veselov <pawel.veselov@gmail.com> wrote: > I'm wondering if it's possible if Postgres JDBC could change the > implementation of cancel() so that cancelQuery() is only sent if > the statement being called is the actual statement that is being > executed on the connection, and otherwise be a no-op. I wondered the same thing back in 2005: http://www.postgresql.org/message-id/438F2DE5.EE98.0025.0@wicourts.gov I still think that something of the sort would be a good idea. -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Fri, Apr 12, 2013 at 4:32 AM, Kevin Grittner <kgrittn@ymail.com> wrote: > Pawel Veselov <pawel.veselov@gmail.com> wrote: >> I'm wondering if it's possible if Postgres JDBC could change the >> implementation of cancel() so that cancelQuery() is only sent if >> the statement being called is the actual statement that is being >> executed on the connection, and otherwise be a no-op. > > I wondered the same thing back in 2005: > http://www.postgresql.org/message-id/438F2DE5.EE98.0025.0@wicourts.gov > I still think that something of the sort would be a good idea. Agreed, the current Statement.cancel() behavior can only be described as buggy. Florent -- Florent Guillaume, Director of R&D, Nuxeo Open Source, Java EE based, Enterprise Content Management (ECM) http://www.nuxeo.com http://www.nuxeo.org +33 1 40 33 79 87