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

I think that this is the reason why it doesn't work:

https://github.com/pgjdbc/pgjdbc/blob/master/org/postgresql/jdbc2/AbstractJdbc2Statement.java#L561

INSERT command is executed (immediately) and timer is canceled (also immediately), so there is no guard against query
timeoutwhen 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,
codewith TimerTask does not *timeouts*, it *ends* after 5 seconds without error, just like my first example without
explicittransaction 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@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
>
> >
>
> >
>
> >
>
> >
>
> >
>
> >
>
> >
>
> > --
>
> >
>
> > Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
>
> >
>
> > To make changes to your subscription:
>
> >
>
> > http://www.postgresql.org/mailpref/pgsql-jdbc