BUG #11014: Postgres can be put into an error state by setting invalid timezone.

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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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.

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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 = -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 ...)

​As 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​ 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 &lt;

tgl@.pa

&gt; wrote:

David G Johnston &lt;

david.g.johnston@

&gt; 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 ...)

​As 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​ 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.

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.

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-tp5812271p5812301.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs