Re: Statement Timeout Message Incorrect

> >>From: Tom Lane <tgl ( at ) sss ( dot ) pgh ( dot ) pa ( dot ) us>
> >>To: elein <elein ( at ) varlena ( dot ) com>
> >>Subject: Re: Statement Timeout Message Incorrect
> >>Date: Wed, 23 Jan 2008 15:52:35 -0500
> >>elein <elein ( at ) varlena ( dot ) com> writes:
> >>> Running 8.3RC1
> >>> I have an sql script where one or more create index statements
> >>> raise a statement timeout message. The statement timeout is
> >>> set to 1d.
> >>
> >>Hmmm ... are you using integer timestamps by any chance?
> >>It looks to me like TimestampTzPlusMilliseconds() would overflow
> >>for such a large timeout.
> >>
> >>> The script runs in ~3 hours including the timeout messages.
> >>
> >>If I'm doing the math correctly, 1 day would overflow to about
> >>8 minutes in microseconds, so if you're seeing individual statements
> >>time out after more than that then there's something else going on.
> >>
> >> regards, tom lane
> >>
>
> Is this a bug or should there be documentation that tells us the max
> of statement timeout should be (what?)
>
>
> We did build with integer timestamps on a 64 bit machine with RC1.
>
> Sorry for the weird format. I'm on the digest and couldn't wait to
> reply.
>
> --elein
> elein(at)varlena(dot)com
>
On Thu, Jan 24, 2008 at 11:48:32AM -0800, elein wrote:

Elein to Daveg:
>Tom says it is a bug.
>It's a bug. You're supposed to get an error if you set the value too
>large:
>regression=# set statement_timeout TO '30d';
>ERROR: invalid value for parameter "statement_timeout": "30d" HINT:
>Value exceeds integer range.
>Patch is in CVS already, please check if it fixes it for you.
>regards, tom lane

>From daveg:
I agree it is a bug. But I don't agree with Tom about what the bug is.

The 8.2 and earlier behavior is that the specification is in milliseconds.
I use this all the time and it works as expected. The 8.3RC2 documentation
agrees saying:

"Abort any statement that takes over the specified number of milliseconds..."

A signed 32bit integer would support 2G milliseconds, or 2147483 seconds,
which is about 24 days, 20 hours. An unsiged integer would double this.

So a '1d' (86400 seconds) statement timeout should not be a problem as it
is not too large.

It sounds more like an error occurs converting from the '1d' format to the
integer milliseconds format. Possibly it goes through an intermediate
microseconds representation and loses there. In which case the usable range
might be around 35 minutes.

Please forward this to Tom since you are in contact.

I'd like us to run your test with a couple different settings of statement
timeout to get more information for the bug report.
[NB: 12h doesn't work either --em]

-dg