Thread: Improvements for statement cancellation

Improvements for statement cancellation

From
Pawel Veselov
Date:
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)

Re: Improvements for statement cancellation

From
John R Pierce
Date:
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



Re: Improvements for statement cancellation

From
Kevin Grittner
Date:
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


Re: Improvements for statement cancellation

From
Florent Guillaume
Date:
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