Обсуждение: Statement Timeout Message Incorrect

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.

The script runs in ~3 hours including the timeout messages.

The script does this:

BEGIN;
create table temp.xxx ...
insert into temp.xxx ...
COMMIT;
create index one on temp.xxx(col1);
create index one on temp.xxx(col2);
create index one on temp.xxx(col3);
create index one on temp.xxx(col4);
BEGIN;
drop table public.xxx;
alter table temp.xxx set schema public;
COMMIT;

I have sprinkled the script with show statement_timeout
and it always returns 1D.

I suspect either a problem with counting statement time
or another error is using the statement timeout message.

Ideas?

--elein
elein@varlena.com

elein <elein@varlena.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

On Wed, Jan 23, 2008 at 11:22:06AM -0800, elein wrote:
> 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.
>
> The script runs in ~3 hours including the timeout messages.
>
> The script does this:
>
> BEGIN;
> create table temp.xxx ...
> insert into temp.xxx ...
> COMMIT;
> create index one on temp.xxx(col1);
> create index one on temp.xxx(col2);
> create index one on temp.xxx(col3);
> create index one on temp.xxx(col4);
> BEGIN;
> drop table public.xxx;
> alter table temp.xxx set schema public;
> COMMIT;
>
> I have sprinkled the script with show statement_timeout
> and it always returns 1D.
>
> I suspect either a problem with counting statement time
> or another error is using the statement timeout message.
>
> Ideas?
>
> --elein
> elein@varlena.com
>

>>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@varlena.com

elein <elein@varlena.com> writes:
> From: Tom Lane <tgl ( at ) sss ( dot ) pgh ( dot ) pa ( dot ) us>
>> Hmmm ... are you using integer timestamps by any chance?
>> It looks to me like TimestampTzPlusMilliseconds() would overflow
>> for such a large timeout.

> Is this a bug or should there be documentation that tells us the max
> of statement timeout should be (what?)

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: 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@varlena.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

elein <elein@varlena.com> writes:
> So a '1d' (86400 seconds) statement timeout should not be a problem as it
> is not too large.

Indeed.  It's a bug that this fails.  What's your point?

            regards, tom lane