Thread: Statement.setQueryTimeout() with autoCommit=false

Hi,

does setQueryTimeout work with autoCommit=false? When autoCommit is true, this code timeouts after 5 seconds (as
expected):

--
        Class.forName("org.postgresql.Driver");
        String url = "jdbc:postgresql://172.30.100.6/test";
        Properties props = new Properties();
        props.setProperty("user", "postgres");
        props.setProperty("password", "");

        Connection conn = DriverManager.getConnection(url, props);
        PreparedStatement st = conn.prepareStatement("INSERT INTO test VALUES('xxx')");
        st.setQueryTimeout(5);
        st.execute();
--

But if I set autoCommit to false, this code timeouts after 30 seconds on read timeout:

--
        Class.forName("org.postgresql.Driver");
        String url = "jdbc:postgresql://172.30.100.6/test";
        Properties props = new Properties();
        props.setProperty("user", "postgres");
        props.setProperty("password", "");

        Connection conn = DriverManager.getConnection(url, props);
        conn.setAutoCommit(false);
        PreparedStatement st = conn.prepareStatement("INSERT INTO test VALUES('xxx')");
        st.setQueryTimeout(5);
        st.execute();
        conn.commit();
--

I'm confused what's setQueryTimeout() method for, why it doesn't work with manual transactions?

Thanks,
Regards
Pavel

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.

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?

> -----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

That insert was against PostgreSQL server with synchronous replication and dead slave.

On Thursday, December 6, 2012 9:18:48 PM UTC+1, Dave Cramer wrote:
> Well my point was that that insert statement shouldn't last long enough to time out ?
>
>
> Dave
>
> Dave Cramer
>
> dave.cramer(at)credativ(dot)ca
> http://www.credativ.ca
>
>
>
>
>
>
>
> On Thu, Dec 6, 2012 at 1:36 PM, David Johnston <pol...@yahoo.com> wrote:
>
>
>
> > -----Original Message-----
>
> > From: pgsql-jd...@postgresql.org [mailto:pgsql-jdbc-
>
> > ow...@postgresql.org] On Behalf Of dmp
>
> > Sent: Thursday, December 06, 2012 1:15 PM
>
> > To: pgsql...@postgresql.org
>
> > Cc: pavel....@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....@loutka.cz
>
> > > <mailto:pavel....@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...@postgresql.org) To make
>
> changes
>
> > to your subscription:
>
> > http://www.postgresql.org/mailpref/pgsql-jdbc
>
>
>
>
>
>
>
> --
>
> Sent via pgsql-jdbc mailing list (pgsql...@postgresql.org)
>
> To make changes to your subscription:
>
> http://www.postgresql.org/mailpref/pgsql-jdbc

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

The statement timeout that you provided is attached to the INSERT statement
- which executed just fine even though it wasn't comitted.  The exception
you are seeing in the "autoCommit(false)" scenario occurs at "conn.commit()"
which implicitly executes "COMMIT;" on the backend and fails due to the
replication situation.

In "autocommit" mode the commit is executed as part of the same statement as
the INSERT (it is a compound statement I guess) and as such the timeout is
still in effect at that time.

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:26 PM
> To: pgsql-jdbc@postgresql.org
> Subject: Re: [JDBC] Statement.setQueryTimeout() with autoCommit=false
>
> That insert was against PostgreSQL server with synchronous replication and
> dead slave.
>
> On Thursday, December 6, 2012 9:18:48 PM UTC+1, Dave Cramer wrote:
> > Well my point was that that insert statement shouldn't last long enough
to
> time out ?
> >
> >
> > Dave
> >
> > Dave Cramer
> >
> > dave.cramer(at)credativ(dot)ca
> > http://www.credativ.ca
> >
> >
> >
> >
> >
> >
> >
> > On Thu, Dec 6, 2012 at 1:36 PM, David Johnston <pol...@yahoo.com>
> wrote:
> >
> >
> >
> > > -----Original Message-----
> >
> > > From: pgsql-jd...@postgresql.org [mailto:pgsql-jdbc-
> >
> > > ow...@postgresql.org] On Behalf Of dmp
> >
> > > Sent: Thursday, December 06, 2012 1:15 PM
> >
> > > To: pgsql...@postgresql.org
> >
> > > Cc: pavel....@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....@loutka.cz
> >
> > > > <mailto:pavel....@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...@postgresql.org) To make
> >
> > changes
> >
> > > to your subscription:
> >
> > > http://www.postgresql.org/mailpref/pgsql-jdbc
> >
> >
> >
> >
> >
> >
> >
> > --
> >
> > Sent via pgsql-jdbc mailing list (pgsql...@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

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

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

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

I would suggest you ignore the "conn.commit()" call and instead create a separate statement "COMMIT;" and attach a
statementtimeout to that and then execute that statement.  I would think that the only thing you lose is the ability to
allowthe 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@postgresql.org [mailto:pgsql-jdbc-
> owner@postgresql.org] On Behalf Of Pavel Arnošt
> Sent: Thursday, December 06, 2012 5:58 PM
> To: pgsql-jdbc@postgresql.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@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
>
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org) To make changes
> to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc