Re: Statement.setQueryTimeout() with autoCommit=false - Mailing list pgsql-jdbc

It may be that I am wrong and at the moment haven't tested your examples but
it seems odd that you can st.cancel() after st.execute() without getting
some kind of exception (which you are ignoring in your catch block).
Without some more detail I either need to do some testing on my own or leave
it to more qualified individuals to help.  I'll admit I am making untested
assumptions here about how the interaction should behave (but often my idea
of "should behave" differs from reality).

David J.


> -----Original Message-----
> From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-
> owner@postgresql.org] On Behalf Of Pavel Arnošt
> Sent: Thursday, December 06, 2012 3:33 PM
> To: pgsql-jdbc@postgresql.org
> Subject: Re: [JDBC] Statement.setQueryTimeout() with autoCommit=false
>
> If that's how it works, why this code timeouts after 5 seconds?:
>
> --
>         Connection conn = DriverManager.getConnection(url, props);
>         conn.setAutoCommit(false);
>         final PreparedStatement st = conn.prepareStatement("INSERT INTO
test
> VALUES('xxx')");
>         st.setQueryTimeout(5);
>         st.execute();
>
>         Timer timer = new Timer(true);
>
>         TimerTask cancelTimer = new TimerTask() {
>             public void run() {
>                 try {
>                     st.cancel();
>                 } catch (SQLException e) { }
>             }
>         };
>
>         timer.schedule(cancelTimer, 5000);
>
>         conn.commit();
> --
>
> Quick look at AbstractJdbc2Statement.execute() shows me that
> setQueryTimeout() works with Timer/TimerTask like this one.
>
> Thanks,
> Pavel
>
> On Thursday, December 6, 2012 7:36:53 PM UTC+1, "David Johnston" wrote:
> > > -----Original Message-----
> >
> > > From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-
> >
> > > owner@postgresql.org] On Behalf Of dmp
> >
> > > Sent: Thursday, December 06, 2012 1:15 PM
> >
> > > To: pgsql-jdbc@postgresql.org
> >
> > > Cc: pavel.arnost@loutka.cz
> >
> > > Subject: Re: [JDBC] Statement.setQueryTimeout() with
> > > autoCommit=false
> >
> > >
> >
> > > Exactly.
> >
> > >
> >
> > > According to the Java API:
> >
> > >
> >
> > > void setAutoCommit(boolean autoCommit) throws SQLException
> >
> > >
> >
> > >      Sets this connection's auto-commit mode to the given state. If
> > > a
> >
> > > connection is in auto-commit mode, then all its SQL statements will
> > > be
> >
> > > executed and committed as individual transactions. Otherwise, its
> > > SQL
> >
> > > statements are grouped into transactions that are terminated by a
> > > call to
> >
> > > either the method commit or the method rollback. By default, new
> >
> > > connections are in auto-commit mode.
> >
> > >
> >
> > > By setting setAutoCommit(false) you are deciding to tell the
> > > database your
> >
> > > code will determine when to commit the transaction. Thereby I would
> > > say
> >
> > > overiding setQueryTimeout().
> >
> > >
> >
> > > danap.
> >
> >
> >
> > Even within an manually administered transaction the setting of
> >
> > "setQueryTimeout()" will (should?) act on the individual statements
> > that
> >
> > make up the transaction.  If any individual statement within the
> > transaction
> >
> > exceeds the limit that statement will fail and thus put the
> > transaction into
> >
> > a "failed" state where a rollback (possibly to savepoint) is required.
> > The
> >
> > transaction itself does not constitute a statement and as such it can
> > run
> >
> > indefinitely long regardless of the presence of a statement timeout.
> >
> >
> >
> > David J.
> >
> >
> >
> > >
> >
> > > Dave Cramer wrote:
> >
> > > > Why would the query timeout at all ?
> >
> > > >
> >
> > > > The query timeout is for long running queries. If the query takes
> >
> > > > longer than n seconds it will timeout.
> >
> > > >
> >
> > > > Dave
> >
> > > >
> >
> > > > Dave Cramer
> >
> > > >
> >
> > > > dave.cramer(at)credativ(dot)ca
> >
> > > > http://www.credativ.ca
> >
> > > >
> >
> > > >
> >
> > > >
> >
> > > > On Thu, Dec 6, 2012 at 12:04 PM, <pavel.arnost@loutka.cz
> >
> > > > <mailto:pavel.arnost@loutka.cz>> wrote:
> >
> > > >
> >
> > > >     Hi,
> >
> > > >
> >
> > > >     does setQueryTimeout work with autoCommit=false? When
> > > > autoCommit
> >
> > > is
> >
> > > >     true, this code timeouts after 5 seconds (as expected):
> >
> > > >
> >
> > > > ~
> >
> > >  > ~
> >
> > >  > ~
> >
> > > >     --
> >
> > > >
> >
> > > >     But if I set autoCommit to false, this code timeouts after 30
> >
> > > >     seconds on read timeout:
> >
> > > >
> >
> > > >     --
> >
> > > > ~
> >
> > >  > ~
> >
> > >  > ~
> >
> > > >     --
> >
> > > >
> >
> > > >     I'm confused what's setQueryTimeout() method for, why it
> > > > doesn't
> >
> > > >     work with manual transactions?
> >
> > >
> >
> > >
> >
> > > --
> >
> > > Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org) To make
> >
> > changes
> >
> > > to your subscription:
> >
> > > http://www.postgresql.org/mailpref/pgsql-jdbc
> >
> >
> >
> >
> >
> >
> >
> > --
> >
> > Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
> >
> > To make changes to your subscription:
> >
> > http://www.postgresql.org/mailpref/pgsql-jdbc
>
>
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org) To make
changes
> to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc