Improvements for statement cancellation

[Pawel Veselov]

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)

[John R Pierce]

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



--
Sent via pgsql-jdbc mailing list (pgsql...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc