Re: Statement.setQueryTimeout() with autoCommit=false

I would suggest you ignore the "conn.commit()" call and instead create a separate statement "COMMIT;" and attach a statement timeout to that and then execute that statement. I would think that the only thing you lose is the ability to allow the driver to determine what the correct "commit;" command but this seems cross-database safe enough regardless.

I'll leave it to you to decide whether setting a fixed time-limit on the "COMMIT;" statement is wise in your situation.

David J.

> -----Original Message-----
> From: pgsql-jdbc-owner(at)postgresql(dot)org [mailto:pgsql-jdbc-
> owner(at)postgresql(dot)org] On Behalf Of Pavel Arnošt
> Sent: Thursday, December 06, 2012 5:58 PM
> To: pgsql-jdbc(at)postgresql(dot)org
> Subject: Re: [JDBC] Statement.setQueryTimeout() with autoCommit=false
>
> I think that this is the reason why it doesn't work:
>
> https://github.com/pgjdbc/pgjdbc/blob/master/org/postgresql/jdbc2/Abstr
> actJdbc2Statement.java#L561
>
> INSERT command is executed (immediately) and timer is canceled (also
> immediately), so there is no guard against query timeout when transaction is
> commited. That's unfortunate, for me at least.
>
> On Thursday, December 6, 2012 10:17:34 PM UTC+1, Pavel Arnošt wrote:
> > I added e.printStackTrace() to catch block, just to be sure, and no exception
> is thrown. I described result poorly, code with TimerTask does not
> *timeouts*, it *ends* after 5 seconds without error, just like my first
> example without explicit transaction and autoCommit set to true.
> >
> >
> >
> > According to javadoc, st.cancel() after st.execute() should be ok.
> >
> >
> >
> > Does someone use synchronous replication? Without query timeouts, it's
> not very usable IMO.
> >
> >
> >
> > On Thursday, December 6, 2012 10:03:17 PM UTC+1, "David Johnston"
> wrote:
> >
> > > 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(at)postgresql(dot)org [mailto:pgsql-jdbc-
> >
> > >
> >
> > > > owner(at)postgresql(dot)org] On Behalf Of Pavel Arno�t
> >
> > >
> >
> > > > Sent: Thursday, December 06, 2012 3:33 PM
> >
> > >
> >
> > > > To: pgsql-jdbc(at)postgresql(dot)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(at)postgresql(dot)org [mailto:pgsql-jdbc-
> >
> > >
> >
> > > > >
> >
> > >
> >
> > > > > > owner(at)postgresql(dot)org] On Behalf Of dmp
> >
> > >
> >
> > > > >
> >
> > >
> >
> > > > > > Sent: Thursday, December 06, 2012 1:15 PM
> >
> > >
> >
> > > > >
> >
> > >
> >
> > > > > > To: pgsql-jdbc(at)postgresql(dot)org
> >
> > >
> >
> > > > >
> >
> > >
> >
> > > > > > Cc: pavel(dot)arnost(at)loutka(dot)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(dot)arnost(at)loutka(dot)cz
> >
> > >
> >
> > > > >
> >
> > >
> >
> > > > > > > <mailto:pavel(dot)arnost(at)loutka(dot)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(at)postgresql(dot)org)
> > > > > > To make
> >
> > >
> >
> > > > >
> >
> > >
> >
> > > > > changes
> >
> > >
> >
> > > > >
> >
> > >
> >
> > > > > > to your subscription:
> >
> > >
> >
> > > > >
> >
> > >
> >
> > > > > > http://www.postgresql.org/mailpref/pgsql-jdbc
> >
> > >
> >
> > > > >
> >
> > >
> >
> > > > >
> >
> > >
> >
> > > > >
> >
> > >
> >
> > > > >
> >
> > >
> >
> > > > >
> >
> > >
> >
> > > > >
> >
> > >
> >
> > > > >
> >
> > >
> >
> > > > > --
> >
> > >
> >
> > > > >
> >
> > >
> >
> > > > > Sent via pgsql-jdbc mailing list (pgsql-jdbc(at)postgresql(dot)org)
> >
> > >
> >
> > > > >
> >
> > >
> >
> > > > > To make changes to your subscription:
> >
> > >
> >
> > > > >
> >
> > >
> >
> > > > > http://www.postgresql.org/mailpref/pgsql-jdbc
> >
> > >
> >
> > > >
> >
> > >
> >
> > > >
> >
> > >
> >
> > > >
> >
> > >
> >
> > > > --
> >
> > >
> >
> > > > Sent via pgsql-jdbc mailing list (pgsql-jdbc(at)postgresql(dot)org) To
> > > > make
> >
> > >
> >
> > > changes
> >
> > >
> >
> > > > to your subscription:
> >
> > >
> >
> > > > http://www.postgresql.org/mailpref/pgsql-jdbc
> >
> > >
> >
> > >
> >
> > >
> >
> > >
> >
> > >
> >
> > >
> >
> > >
> >
> > > --
> >
> > >
> >
> > > Sent via pgsql-jdbc mailing list (pgsql-jdbc(at)postgresql(dot)org)
> >
> > >
> >
> > > To make changes to your subscription:
> >
> > >
> >
> > > http://www.postgresql.org/mailpref/pgsql-jdbc
>
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc(at)postgresql(dot)org) To make changes
> to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc