Обсуждение: BUG #11014: Postgres can be put into an error state by setting invalid timezone.

The following bug has been logged on the website:

Bug reference:      11014
Logged by:          Duncan Gillis
Email address:      dgillis@dystillr.com
PostgreSQL version: 9.4beta1
Operating system:   Ubuntu 13.04; Ubuntu 14.04
Description:

psql (9.4beta1)
Type "help" for help.

postgres=#
postgres=#
postgres=# show timezone;
 TimeZone
----------
 UTC
(1 row)

postgres=#
postgres=#
postgres=# set timezone='-0400';
SET
postgres=#
postgres=# show timezone;
 TimeZone
----------
 unknown
(1 row)

postgres=# select now();
The connection to the server was lost. Attempting reset: Failed.
!>

dgillis@dystillr.com writes:
> postgres=# set timezone='-0400';
> SET
> postgres=#
> postgres=# show timezone;
>  TimeZone
> ----------
>  unknown
> (1 row)

> postgres=# select now();
> The connection to the server was lost. Attempting reset: Failed.

Oh, hm, looks like it didn't occur to us that pg_tzset could reject
out-of-range input :-(.

Previous versions of PG would allow this, interpreting the GMT offset
as 400 hours.  In 9.4, the data is fed to the zic library, which thinks
that the zone offset shouldn't exceed 1 week (168 hours); and we're
not defending against a null result.

While it's easy enough to add an error check, I wonder if there's an
actual use-case for such a large zone offset?  Or were you just
playing around?

            regards, tom lane

Tom Lane-2 wrote
> dgillis@

>  writes:
>> postgres=# set timezone='-0400';
>> SET
>> postgres=#
>> postgres=# show timezone;
>>  TimeZone
>> ----------
>>  unknown
>> (1 row)
>
>> postgres=# select now();
>> The connection to the server was lost. Attempting reset: Failed.
>
> Oh, hm, looks like it didn't occur to us that pg_tzset could reject
> out-of-range input :-(.
>
> Previous versions of PG would allow this, interpreting the GMT offset
> as 400 hours.  In 9.4, the data is fed to the zic library, which thinks
> that the zone offset shouldn't exceed 1 week (168 hours); and we're
> not defending against a null result.
>
> While it's easy enough to add an error check, I wonder if there's an
> actual use-case for such a large zone offset?  Or were you just
> playing around?

This does seem kind of a harsh for a mis-understanding that one cannot
specify a hours offset as a valid timezone identifier.  ISTM the OP was
trying to set the timezone to EDT by specifying a -04 hours and 00 minutes
offset (- 04 00 = -0400 )...a 4-digit input like this shouldn't be
considered a -400 hour GMT offset...



David J.








--
View this message in context:
http://postgresql.1045698.n5.nabble.com/BUG-11014-Postgres-can-be-put-into-an-error-state-by-setting-invalid-timezone-tp5812271p5812283.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.

Duncan Gillis <dgillis@dystillr.com> writes:
> I didn't intend on such an absurd timezone. The "-0400" came from a
> shell script of mine that set the timezone to an offset using using
> Unix's date command to print the local timezone offset (e.g., date +%z
> #=> -0400, if you're on the US east coast).

Ah so.

> The "+/-HHMM" timezone offset format -- without the colon between hours
> and minutes -- is quite common so it seems like this would be an easy
> error to make. Since a lot of developers interact with the database
> primarily through an ORM and as many (most?) ORMs apply their own
> localization conversion to a timestamp received from the database, a lot
> of people could have made a mistake similar to mine and never seen any
> ill effects. Then, all of a sudden 9.4 would appear to break their
> application.

Well, as I said, previous releases would have interpreted that as UTC
minus about two and a half weeks, so I'm not sure what you consider
an "ill effect" ;-).  Accepting a plausible-looking setting and then
interpreting it in a totally insane way isn't that helpful IMO.  But
I think we can content ourselves with throwing an error here, rather
than trying to restore the previous range of accepted values.  If we
make it complain specifically that the UTC offset is out of range,
it might help people spot the problem pretty easily.

            regards, tom lane

David G Johnston <david.g.johnston@gmail.com> writes:
> This does seem kind of a harsh for a mis-understanding that one cannot
> specify a hours offset as a valid timezone identifier.  ISTM the OP was
> trying to set the timezone to EDT by specifying a -04 hours and 00 minutes
> offset (- 04 00 = -0400 )...a 4-digit input like this shouldn't be
> considered a -400 hour GMT offset...

Perhaps so, but that's not the way it's been read by past PG releases.
Changing that would be in the nature of a feature addition, not a bug fix,
IMO.  (In other words, if someone wants to do it, send in a patch ...)

            regards, tom lane

On Mon, Jul 21, 2014 at 4:01 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> David G Johnston <david.g.johnston@gmail.com> writes:
> > This does seem kind of a harsh for a mis-understanding that one cannot
> > specify a hours offset as a valid timezone identifier.  ISTM the OP was
> > trying to set the timezone to EDT by specifying a -04 hours and 00
> minutes
> > offset (- 04 00 =3D -0400 )...a 4-digit input like this shouldn't be
> > considered a -400 hour GMT offset...
>
> Perhaps so, but that's not the way it's been read by past PG releases.
> Changing that would be in the nature of a feature addition, not a bug fix=
,
> IMO.  (In other words, if someone wants to do it, send in a patch ...)
>
>
=E2=80=8BAs you noted throwing an error is correct since an offset is not a
timezone - at least not in a way that is deterministic.

Since I don't have any example usages that would be convincing proof that
interpreting -0400=E2=80=8B as 400 hours is a bug the point becomes moot.  =
In this
one instance neither 4 nor 400 is a reasonable answer.  But given the
fundamental nature of a timezone anything that resolves to more than 12
absolute hours is incorrectly coded since values outside of +/- [0, 12]
have no meaning and either should throw an error or, in this case, be
treated in a more logical manner (-4:00)

There is a bug somewhere though you are welcome to argue whose
responsibility it is to correct - or at least accept that the aberrant
behavior is now intentional and being kept for backward compatibility
reasons even if it can result in absurd results - no matter how infrequent.

The code involved is already buggy since it crashes the server; this is
just another symptom that our current implementation is faulty.

David J.

David G Johnston wrote
> On Mon, Jul 21, 2014 at 4:01 PM, Tom Lane <

> tgl@.pa

> > wrote:
>=20
>> David G Johnston <

> david.g.johnston@

> > writes:
>> > This does seem kind of a harsh for a mis-understanding that one cannot
>> > specify a hours offset as a valid timezone identifier.  ISTM the OP wa=
s
>> > trying to set the timezone to EDT by specifying a -04 hours and 00
>> minutes
>> > offset (- 04 00 =3D -0400 )...a 4-digit input like this shouldn't be
>> > considered a -400 hour GMT offset...
>>
>> Perhaps so, but that's not the way it's been read by past PG releases.
>> Changing that would be in the nature of a feature addition, not a bug
>> fix,
>> IMO.  (In other words, if someone wants to do it, send in a patch ...)
>>
>>
> =E2=80=8BAs you noted throwing an error is correct since an offset is not=
 a
> timezone - at least not in a way that is deterministic.
>=20
> Since I don't have any example usages that would be convincing proof that
> interpreting -0400=E2=80=8B as 400 hours is a bug the point becomes moot.=
  In this
> one instance neither 4 nor 400 is a reasonable answer.  But given the
> fundamental nature of a timezone anything that resolves to more than 12
> absolute hours is incorrectly coded since values outside of +/- [0, 12]
> have no meaning and either should throw an error or, in this case, be
> treated in a more logical manner (-4:00)
>=20
> There is a bug somewhere though you are welcome to argue whose
> responsibility it is to correct - or at least accept that the aberrant
> behavior is now intentional and being kept for backward compatibility
> reasons even if it can result in absurd results - no matter how
> infrequent.
>=20
> The code involved is already buggy since it crashes the server; this is
> just another symptom that our current implementation is faulty.
>=20
> David J.

Though pondering this additionally the actual coding to make -0400 be
interpreted as "-4:00" would indeed be a feature enhancement - though
accepting a result of 400 I would argue is a bug even if it is one we are
going to choose to live with in back branches for compatibility reasons.  W=
e
seem to already agree that it should at least error out on a going-forward
basis.

David J.







--
View this message in context: http://postgresql.1045698.n5.nabble.com/BUG-1=
1014-Postgres-can-be-put-into-an-error-state-by-setting-invalid-timezone-tp=
5812271p5812301.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.

I wrote:
> dgillis@dystillr.com writes:
>> postgres=# set timezone='-0400';
>> SET
>> postgres=#
>> postgres=# show timezone;
>> TimeZone
>> ----------
>> unknown
>> (1 row)

>> postgres=# select now();
>> The connection to the server was lost. Attempting reset: Failed.

> Oh, hm, looks like it didn't occur to us that pg_tzset could reject
> out-of-range input :-(.

> Previous versions of PG would allow this, interpreting the GMT offset
> as 400 hours.  In 9.4, the data is fed to the zic library, which thinks
> that the zone offset shouldn't exceed 1 week (168 hours); and we're
> not defending against a null result.

Hmm ... I had checked that this example doesn't crash pre-9.4, but
on closer inspection it turns out that the bug is indeed there pre-9.4.
You just need to try specific operations that expect session_timezone
to be valid always, eg timeofday():

regression=# set timezone='-0400';
SET
regression=# select timeofday();
The connection to the server was lost. Attempting reset: Failed.

This was introduced in commit 631dc390f49909a5c8ebd6002cfb2bcee5415a9d,
which got back-patched, so it's in all the current releases.  Too bad
the report didn't come in a few hours earlier, we could've fixed it in
this week's releases :-(

            regards, tom lane

David G Johnston <david.g.johnston@gmail.com> writes:
> Though pondering this additionally the actual coding to make -0400 be
> interpreted as "-4:00" would indeed be a feature enhancement - though
> accepting a result of 400 I would argue is a bug even if it is one we are
> going to choose to live with in back branches for compatibility reasons.  We
> seem to already agree that it should at least error out on a going-forward
> basis.

The fact that it crashes is indisputably a bug (fixed as of a few minutes
ago).  However, whether SET TIMEZONE = '-0400' should be read as setting
the UTC offset to 400 hours, or 400 minutes, or 400 seconds, or 4 hours,
or who knows what else, is not a bug, it's a definitional disagreement.

As a comparison point, the syntax SET TIME ZONE INTERVAL '-0400' is
specified in the SQL standard, and unless I'm totally misreading it, the
standard requires that to be interpreted as 400 minutes.  (The standard's
examples of useful values look more like '-04:00'.)  Now that might look
silly on its face, but if the string is '-180' then reading it as UTC
minus 3 hours doesn't seem so silly.

Anyway, we could certainly have a discussion about changing the
interpretation, but it would not be a back-patchable bug fix IMO.
There's a non-negligible risk of breaking apps that worked before.

            regards, tom lane