Re: implementing query timeout

Here is my first draft of a query timeout SET variable. It works for a
simple test:

test=> set query_timeout to '2000';
SET
test=> select * from pg_class, pg_type;
ERROR: Query was cancelled.
test=>

I still need to polish it up and do more testing. Can people comment on
the proper placement of the disable_sig_alarm(true) calls? Also, the
handling of the alarm is tricky because the deadlock timer uses the
alarm as well and the query_timeout. (I have not gotten all the cases
correct yet.)

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

Bruce Momjian writes:

> Here is my first draft of a query timeout SET variable.

Unless it only cancels SELECT statements (which may or may not be a good
idea), please call it statement_timeout.

--
Peter Eisentraut peter_e(at)gmx(dot)net

Peter Eisentraut wrote:
> Bruce Momjian writes:
>
> > Here is my first draft of a query timeout SET variable.
>
> Unless it only cancels SELECT statements (which may or may not be a good
> idea), please call it statement_timeout.

Woh, you mean only SELECT is a query? Why is it SQL? Do we not use
'query' to mean any SQL command?

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

Peter Eisentraut wrote:
> Bruce Momjian writes:
>
> > Here is my first draft of a query timeout SET variable.
>
> Unless it only cancels SELECT statements (which may or may not be a good
> idea), please call it statement_timeout.

Do people prefer query_timeout or statement_timeout? Doesn't matter to
me.

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

On Wed, 2002-07-10 at 18:52, Bruce Momjian wrote:
> Peter Eisentraut wrote:
> > Bruce Momjian writes:
> >
> > > Here is my first draft of a query timeout SET variable.
> >
> > Unless it only cancels SELECT statements (which may or may not be a good
> > idea), please call it statement_timeout.
>
> Do people prefer query_timeout or statement_timeout? Doesn't matter to
> me.

There is no 'statement' in SQL, that said, 75% of SQL has nothing to do
with queries.

So... I think I vote 'statement'.

Bruce Momjian wrote:
>
> Peter Eisentraut wrote:
> > Bruce Momjian writes:
> >
> > > Here is my first draft of a query timeout SET variable.
> >
> > Unless it only cancels SELECT statements (which may or may not be a good
> > idea), please call it statement_timeout.
>
> Do people prefer query_timeout or statement_timeout? Doesn't matter to
> me.

Statements is everything. DDL- and DML-statements. Query is IMHO synonym
for DML-statement. So query_timeout is the right term.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

Jan Wieck wrote:
> Bruce Momjian wrote:
> >
> > Peter Eisentraut wrote:
> > > Bruce Momjian writes:
> > >
> > > > Here is my first draft of a query timeout SET variable.
> > >
> > > Unless it only cancels SELECT statements (which may or may not be a good
> > > idea), please call it statement_timeout.
> >
> > Do people prefer query_timeout or statement_timeout? Doesn't matter to
> > me.
>
> Statements is everything. DDL- and DML-statements. Query is IMHO synonym
> for DML-statement. So query_timeout is the right term.

But the timeout is for any statement, not just SELECT/UPDATE, etc, so it
sounds like you are voting for 'statement'.

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

The INFORMIX equivalent is SET LOCK MODE TO WAIT [timeout value in seconds];
If you don't specify any timeout value, you wait until all statements are
completed, If timeout value is set to 0 then you return immediatly if the
tables you query are already locked, ...

This statement is valid for a connection or until another identical
statement is sent.
----- Original Message -----
From: "Bruce Momjian" <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: "Jan Wieck" <JanWieck(at)Yahoo(dot)com>
Cc: "Peter Eisentraut" <peter_e(at)gmx(dot)net>; "Ed Loehr" <ed(at)LoehrTech(dot)com>;
<dave(at)fastcrypt(dot)com>; "Matthew Kennedy" <mkennedy(at)opushealthcare(dot)com>;
"PostgreSQL-patches" <pgsql-patches(at)postgresql(dot)org>
Sent: Thursday, July 11, 2002 11:34 AM
Subject: Re: [PATCHES] implementing query timeout

> Jan Wieck wrote:
> > Bruce Momjian wrote:
> > >
> > > Peter Eisentraut wrote:
> > > > Bruce Momjian writes:
> > > >
> > > > > Here is my first draft of a query timeout SET variable.
> > > >
> > > > Unless it only cancels SELECT statements (which may or may not be a
good
> > > > idea), please call it statement_timeout.
> > >
> > > Do people prefer query_timeout or statement_timeout? Doesn't matter
to
> > > me.
> >
> > Statements is everything. DDL- and DML-statements. Query is IMHO synonym
> > for DML-statement. So query_timeout is the right term.
>
> But the timeout is for any statement, not just SELECT/UPDATE, etc, so it
> sounds like you are voting for 'statement'.
>
> --
> Bruce Momjian | http://candle.pha.pa.us
> pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
> + If your life is a hard drive, | 830 Blythe Avenue
> + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>
>

Nicolas Bazin wrote:
> The INFORMIX equivalent is SET LOCK MODE TO WAIT [timeout value in seconds];
> If you don't specify any timeout value, you wait until all statements are
> completed, If timeout value is set to 0 then you return immediatly if the
> tables you query are already locked, ...
>
> This statement is valid for a connection or until another identical
> statement is sent.

Users want a more general timeout facitily, for example, queries taking
> 10 minutes.

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

Bruce Momjian wrote:
>
> Jan Wieck wrote:

> > Statements is everything. DDL- and DML-statements. Query is IMHO synonym
> > for DML-statement. So query_timeout is the right term.
>
> But the timeout is for any statement, not just SELECT/UPDATE, etc, so it
> sounds like you are voting for 'statement'.

No, I am voting for 'query'. I don't see the point in allowing a
timeout for utility statements. Why would someone want a timeout
on CREATE INDEX, COPY or VACUUM? Allowing that would IMHO be
calling for more trouble than necessary.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being
right. #
# Let's break this rule - forgive
me. #
#==================================================
JanWieck(at)Yahoo(dot)com #

Jan Wieck wrote:
> Bruce Momjian wrote:
> >
> > Jan Wieck wrote:
>
> > > Statements is everything. DDL- and DML-statements. Query is IMHO synonym
> > > for DML-statement. So query_timeout is the right term.
> >
> > But the timeout is for any statement, not just SELECT/UPDATE, etc, so it
> > sounds like you are voting for 'statement'.
>
> No, I am voting for 'query'. I don't see the point in allowing a
> timeout for utility statements. Why would someone want a timeout
> on CREATE INDEX, COPY or VACUUM? Allowing that would IMHO be
> calling for more trouble than necessary.

Seems pretty arbitrary to time just DML and not DLL. I can even imagine
this for VACUUM FULL where you don't want it running for a long time.
It is under their control and they can turn it off if they don't want it
for those statements.

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

Rod Taylor wrote:
> On Wed, 2002-07-10 at 18:52, Bruce Momjian wrote:
> > Peter Eisentraut wrote:
> > > Bruce Momjian writes:
> > >
> > > > Here is my first draft of a query timeout SET variable.
> > >
> > > Unless it only cancels SELECT statements (which may or may not be a good
> > > idea), please call it statement_timeout.
> >
> > Do people prefer query_timeout or statement_timeout? Doesn't matter to
> > me.
>
> There is no 'statement' in SQL, that said, 75% of SQL has nothing to do
> with queries.
>
> So... I think I vote 'statement'.

OK, changed to 'statement'. Patch completed, ready for testing. It
properly handles all the cases I tested, like UPDATE waiting on a lock
and SELECT queries.

I also tested setting statement_timeout to '1' and it allows you to
change it to a different value. It doesn't cancel the SET before it is
changed.

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