Re: timeout implementation issues - Mailing list pgsql-hackers

> > > The current plan seems to be to make changes in the backend and the JDBC
> > > interface, the bulk of the implementation being in the backend.
> >
> > Yes, ODBC and JDBC need this, and I am sure psql folks will use it too,
> > not counting libpq and all the others.
>
> I wasn't able to follow this thread sorry.
> ODBC has QUERY_TIMEOUT and CONNECTION_TIMEOUT.
>
> > We just need a way to specify statement-level SET options inside a
> > transaction where the statement may fail and ignore the SET command that
> > resets the timeout.  We don't have any mechanism to reset the timeout
> > parameter at the end of a transaction automatically,
>
> Why should the timeout be reset automatically ?

It doesn't need to be reset automatically, but the problem is that if
you are doing a timeout for single statement in a transaction, and that
statement aborts the transaction, the SET command after it to reset the
timeout fails.

I am attaching the email that describes the issue.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
>From pgman Tue Apr  2 13:29:51 2002
Subject: Re: [HACKERS] timeout implementation issues
In-Reply-To: <Pine.LNX.4.21.0204021112230.11943-100000@atalanta.dynamicdiagrams.com>
To: Jessica Perry Hekman <jphekman@dynamicdiagrams.com>
Date: Tue, 2 Apr 2002 13:39:30 -0500 (EST)
cc: Tom Lane <tgl@sss.pgh.pa.us>, Jan Wieck <janwieck@yahoo.com>,
    pgsql-hackers@postgresql.org
X-Mailer: ELM [version 2.4ME+ PL97 (25)]
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset=US-ASCII
Content-Length:  1656
Status: OR

Jessica Perry Hekman wrote:
> On Mon, 1 Apr 2002, Tom Lane wrote:
>
> > On the other hand, we do not have anything in the backend now that
> > applies to just one statement and then automatically resets afterwards;
> > and I'm not eager to add a parameter with that behavior just for JDBC's
> > convenience.  It seems like it'd be a big wart.
>
> Does that leave us with implementing query timeouts in JDBC (timer in the
> driver; then the driver sends a cancel request to the backend)?

No, I think we have to find a way to do this in the backend; just not
sure how yet.

I see the problem Tom is pointing out, that SET is ignored if the
transaction has already aborted:

    test=> begin;
    BEGIN
    test=> lkjasdf;
    ERROR:  parser: parse error at or near "lkjasdf"
    test=> set server_min_messages = 'log';
    WARNING:  current transaction is aborted, queries ignored until end of
    transaction block
    *ABORT STATE*
    test=>

so if the transaction aborted, the reset of the statement_timeout would
not happen.  The only way the application could code this would be with
this:

    BEGIN WORK;
    query;
    SET statement_timeout = 4;
    query;
    SET statement_timeout = 0;
    query;
    COMMIT;
    SET statement_timeout = 0;

Basically, it does the reset twice, once assuming the transaction
doesn't abort, and another assuming it does abort.  Is this something
that the JDBC and ODBC drivers can do automatically?

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026