Re: why is max standby delay only 35 minutes?

I have a server where I wanted to do some reporting on a standby, and
wanted to set the max standby delay to 1 hour. upon doing that, i get
this in the logs:

2011-03-03 21:20:08 EST () [2656]: [2-1] user=,db=LOG: received
SIGHUP, reloading configuration files
2011-03-03 21:20:08 EST () [2656]: [3-1] user=,db=LOG: 3600000 is
outside the valid range for parameter "max_standby_archive_delay" (-1
.. 2147483)

The error is clear enough, but is there some reason that the parameter
is coded this way? istm people are much more likely to want to be able
to set the precision in hours than in microseconds.

OTOH, maybe it's a bug? The default resolution is in milliseconds, and
you can't set it to anything less than that (afaict). I asked on irc
and the consensus seemed to be that the internal representation is
off, are we missing something?

Robert Treat
play: xzilla.net
work: omniti.com
hiring: l42.org/Lg

On Fri, Mar 4, 2011 at 04:00, Robert Treat <rob(at)xzilla(dot)net> wrote:
> I have a server where I wanted to do some reporting on a standby, and
> wanted to set the max standby delay to 1 hour. upon doing that, i get
> this in the logs:
>
> 2011-03-03 21:20:08 EST () [2656]: [2-1] user=,db=LOG:  received
> SIGHUP, reloading configuration files
> 2011-03-03 21:20:08 EST () [2656]: [3-1] user=,db=LOG:  3600000 is
> outside the valid range for parameter "max_standby_archive_delay" (-1
> .. 2147483)
>
> The error is clear enough, but is there some reason that the parameter
> is coded this way? istm people are much more likely to want to be able
> to set the precision in hours than in microseconds.
>
> OTOH, maybe it's a bug? The default resolution is in milliseconds, and
> you can't set it to anything less than that (afaict). I asked on irc
> and the consensus seemed to be that the internal representation is
> off, are we missing something?

See this thread here:
http://archives.postgresql.org/pgsql-hackers/2010-12/msg01517.php

Summary: should be fixed, but it needs to be verified that it works
across all possible codepaths. It's not an issue with just
max_standby_delay.

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

On Fri, Mar 4, 2011 at 2:03 AM, Magnus Hagander <magnus(at)hagander(dot)net> wrote:
> On Fri, Mar 4, 2011 at 04:00, Robert Treat <rob(at)xzilla(dot)net> wrote:
>> I have a server where I wanted to do some reporting on a standby, and
>> wanted to set the max standby delay to 1 hour. upon doing that, i get
>> this in the logs:
>>
>> 2011-03-03 21:20:08 EST () [2656]: [2-1] user=,db=LOG:  received
>> SIGHUP, reloading configuration files
>> 2011-03-03 21:20:08 EST () [2656]: [3-1] user=,db=LOG:  3600000 is
>> outside the valid range for parameter "max_standby_archive_delay" (-1
>> .. 2147483)
>>
>> The error is clear enough, but is there some reason that the parameter
>> is coded this way? istm people are much more likely to want to be able
>> to set the precision in hours than in microseconds.
>>
>> OTOH, maybe it's a bug? The default resolution is in milliseconds, and
>> you can't set it to anything less than that (afaict). I asked on irc
>> and the consensus seemed to be that the internal representation is
>> off, are we missing something?
>
> See this thread here:
> http://archives.postgresql.org/pgsql-hackers/2010-12/msg01517.php
>
> Summary: should be fixed, but it needs to be verified that it works
> across all possible codepaths. It's not an issue with just
> max_standby_delay.
>

Thanks for the pointer! I guess the next question is if anyone is
working on that, and/or what would need to be done to know we've done
a satisfactory job of verifying nothing breaks across all codepaths
were someone to take on the job?

Robert Treat
play: xzilla.net
work: omniti.com
hiring: l42.org/Lg

On Fri, Mar 4, 2011 at 18:19, Robert Treat <rob(at)xzilla(dot)net> wrote:
> On Fri, Mar 4, 2011 at 2:03 AM, Magnus Hagander <magnus(at)hagander(dot)net> wrote:
>> On Fri, Mar 4, 2011 at 04:00, Robert Treat <rob(at)xzilla(dot)net> wrote:
>>> I have a server where I wanted to do some reporting on a standby, and
>>> wanted to set the max standby delay to 1 hour. upon doing that, i get
>>> this in the logs:
>>>
>>> 2011-03-03 21:20:08 EST () [2656]: [2-1] user=,db=LOG:  received
>>> SIGHUP, reloading configuration files
>>> 2011-03-03 21:20:08 EST () [2656]: [3-1] user=,db=LOG:  3600000 is
>>> outside the valid range for parameter "max_standby_archive_delay" (-1
>>> .. 2147483)
>>>
>>> The error is clear enough, but is there some reason that the parameter
>>> is coded this way? istm people are much more likely to want to be able
>>> to set the precision in hours than in microseconds.
>>>
>>> OTOH, maybe it's a bug? The default resolution is in milliseconds, and
>>> you can't set it to anything less than that (afaict). I asked on irc
>>> and the consensus seemed to be that the internal representation is
>>> off, are we missing something?
>>
>> See this thread here:
>> http://archives.postgresql.org/pgsql-hackers/2010-12/msg01517.php
>>
>> Summary: should be fixed, but it needs to be verified that it works
>> across all possible codepaths. It's not an issue with just
>> max_standby_delay.
>>
>
> Thanks for the pointer!  I guess the next question is if anyone is
> working on that, and/or what would need to be done to know we've done
> a satisfactory job of verifying nothing breaks across all codepaths
> were someone to take on the job?

I have it sitting on my list somewhere, but I haven't actually started
doing anything...

A good start is to just change the code (likely quite easy) and then
test all the different ways that you can set and reset and read the
values of a guc (set/show/pg_settings/anythingelseyoucanthinkof), that
it's passed properly across exec_backend etc - and testing tihs on
multiple platforms I guess, in particular both 32 and 64-bit...

At least that's my understanding of what needs to be done :-)

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

FYI, this is now on the TODO list:

Increase maximum values for max_standby_streaming_delay and
log_min_duration_statement

* http://archives.postgresql.org/pgsql-hackers/2010-12/msg01517.php

---------------------------------------------------------------------------

Magnus Hagander wrote:
> On Fri, Mar 4, 2011 at 18:19, Robert Treat <rob(at)xzilla(dot)net> wrote:
> > On Fri, Mar 4, 2011 at 2:03 AM, Magnus Hagander <magnus(at)hagander(dot)net> wrote:
> >> On Fri, Mar 4, 2011 at 04:00, Robert Treat <rob(at)xzilla(dot)net> wrote:
> >>> I have a server where I wanted to do some reporting on a standby, and
> >>> wanted to set the max standby delay to 1 hour. upon doing that, i get
> >>> this in the logs:
> >>>
> >>> 2011-03-03 21:20:08 EST () [2656]: [2-1] user=,db=LOG: ?received
> >>> SIGHUP, reloading configuration files
> >>> 2011-03-03 21:20:08 EST () [2656]: [3-1] user=,db=LOG: ?3600000 is
> >>> outside the valid range for parameter "max_standby_archive_delay" (-1
> >>> .. 2147483)
> >>>
> >>> The error is clear enough, but is there some reason that the parameter
> >>> is coded this way? istm people are much more likely to want to be able
> >>> to set the precision in hours than in microseconds.
> >>>
> >>> OTOH, maybe it's a bug? The default resolution is in milliseconds, and
> >>> you can't set it to anything less than that (afaict). I asked on irc
> >>> and the consensus seemed to be that the internal representation is
> >>> off, are we missing something?
> >>
> >> See this thread here:
> >> http://archives.postgresql.org/pgsql-hackers/2010-12/msg01517.php
> >>
> >> Summary: should be fixed, but it needs to be verified that it works
> >> across all possible codepaths. It's not an issue with just
> >> max_standby_delay.
> >>
> >
> > Thanks for the pointer! ?I guess the next question is if anyone is
> > working on that, and/or what would need to be done to know we've done
> > a satisfactory job of verifying nothing breaks across all codepaths
> > were someone to take on the job?
>
> I have it sitting on my list somewhere, but I haven't actually started
> doing anything...
>
> A good start is to just change the code (likely quite easy) and then
> test all the different ways that you can set and reset and read the
> values of a guc (set/show/pg_settings/anythingelseyoucanthinkof), that
> it's passed properly across exec_backend etc - and testing tihs on
> multiple platforms I guess, in particular both 32 and 64-bit...
>
> At least that's my understanding of what needs to be done :-)
>
> --
> ?Magnus Hagander
> ?Me: http://www.hagander.net/
> ?Work: http://www.redpill-linpro.com/
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

I looked through this, and it appears that we can just remove these
restrictions.

I considered the following parameters, which are measured in
milliseconds and currently have INT_MAX/1000 as maximum value.

deadlock_timeout
max_standby_archive_delay
max_standby_streaming_delay
log_min_duration_statement
log_autovacuum_min_duration

In addition there are

autovacuum_naptime
wal_receiver_status_interval

which are measured in seconds and have INT_MAX/1000 as maximum value.
This means they are not proposed to be changed, but they effectively use
the same arithmetic as some of the above parameters after multiplying
their value by 1000, showing that the arithmetic is expected to work up
to INT_MAX.

Also there is

statement_timeout

which is measured in milliseconds and already has INT_MAX as maximum,
and larger values are well exercised here.

Now,

The logic behind deadlock_timeout, max_standby_archive_delay,
max_standby_streaming_delay all depends on TimestampTzPlusMillisecond(),
which is also used by statement_timeout, so it works with values above
INT_MAX/1000. I tried this out with a patched server with
deadlock_timeout, and it works as expected.

log_min_duration_statement has custom code in postgres.c, which is
easily verified to work with values above INT_MAX/1000.

log_autovacuum_min_duration depends on TimestampDifferenceExceeds(),
which is also safe for large values, considering that it is part of the
core timestamp type support.

Consequently, I propose the attached patch. I didn't find any relevant
documentation references that would need updating.

On tis, 2011-03-15 at 20:44 +0200, Peter Eisentraut wrote:
> Consequently, I propose the attached patch. I didn't find any
> relevant documentation references that would need updating.

Applied.