Thread: pg_dump/pg_restore with time zone displacement out of range

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

I have the following strange effect, maybe someone has an idea on wether this is 'normal' or a bug:

My platform: Ubuntu 12.04, PostgreSQL 9.2beta1, tried it with 9.1 with same result.
postgresql.conf is stock, except for timezone, which is set to Asia/Manila.

$ createdb -U postgres tztest
$ psql tztest postgres
tztest=# create table foo (bar int, baz timestamptz);
CREATE TABLE
tztest=# insert into foo values (1,'2012-05-01T14:00:00');
INSERT 0 1
tztest=# insert into foo values (2,'2012-05-01T14:00:00 bc');
INSERT 0 1
tztest=# select * from foo;
 bar |             baz
- -----+------------------------------
   1 | 2012-05-01 14:00:00+08
   2 | 2012-05-01 14:00:00-15:56 BC

Now that's strange already. Take a look upon this funny timezone offset. Let's backup this now.

$ pg_dump -Fp -U postgres -x -O -a -f "tztest@pht.sql" tztest
$ grep -e '^[12]' tztest@pht.sql
1    2012-05-01 14:00:00+08
2    2012-05-01 14:00:00-15:56 BC

Now that's expected, it's the same as the output before. Let's restore it.

$ psql tztest postgres
tztest=# delete from foo;
DELETE
tztest=# \i tztest@pht.sql
[...]
psql:tztest@pht.sql:20: ERROR:  time zone displacement out of range: "2012-05-01 14:00:00-15:56 BC"
KONTEXT:  COPY foo, line 2, column baz: "2012-05-01 14:00:00-15:56 BC"
tztest=#

It can't import because -15:56 is out of range for a timezone offset. But it's in the backup, so
it should restore it, though, right?
Doing this with any other format, using pg_restore, gives the same error.

The only workaround I found was (without sacrificing functionality of date queries, that is) was
to manually set the time zone to GMT+8 *prior* to running pg_dump.

My question upon you is now: Is it my platform, which causes this, or is the constraint within the
restore which is bad, or, which is my best bet, should pg_dump leave it's fingers off the time
zone calculation and backup the data as if time zone 'UTC' was given within postgresql.conf?

- --
cu, Patric
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: GnuPT 2.5.2

iEYEARECAAYFAk/GjSMACgkQfGgGu8y7ypD6WwCgj0EPBp7RpQENNtZA4vvefooP
7DcAoNkIl3EkUnk1rYFigoMta4fm/6Q0
=TMAu
-----END PGP SIGNATURE-----

Patric Bechtel <patric.bechtel@gmail.com> writes:
> My platform: Ubuntu 12.04, PostgreSQL 9.2beta1, tried it with 9.1 with same result.
> postgresql.conf is stock, except for timezone, which is set to Asia/Manila.

> $ createdb -U postgres tztest
> $ psql tztest postgres
> tztest=# create table foo (bar int, baz timestamptz);
> CREATE TABLE
> tztest=# insert into foo values (1,'2012-05-01T14:00:00');
> INSERT 0 1
> tztest=# insert into foo values (2,'2012-05-01T14:00:00 bc');
> INSERT 0 1
> tztest=# select * from foo;
>  bar |             baz
> - -----+------------------------------
>    1 | 2012-05-01 14:00:00+08
>    2 | 2012-05-01 14:00:00-15:56 BC

> Now that's strange already.

No, it's perfectly normal, per the Olson timezone database info for
Manila:

# Zone    NAME        GMTOFF    RULES    FORMAT    [UNTIL]
Zone    Asia/Manila    -15:56:00 -    LMT    1844 Dec 31
            8:04:00 -    LMT    1899 May 11
            8:00    Phil    PH%sT    1942 May
            9:00    -    JST    1944 Nov
            8:00    Phil    PH%sT

The Olson database generally uses local mean solar time for the named
city as the reference point in years before that locality adopted any
official standard time reference.  Very few places are at an exact hour
offset from Greenwich, so you see all sorts of weird UTC offsets there.
In this case it looks like Manila changed their minds as to whether to
reckon themselves west or east of Greenwich back in 1844.

> tztest=# \i tztest@pht.sql
> [...]
> psql:tztest@pht.sql:20: ERROR:  time zone displacement out of range: "2012-05-01 14:00:00-15:56 BC"
> KONTEXT:  COPY foo, line 2, column baz: "2012-05-01 14:00:00-15:56 BC"

This, on the other hand, is unfortunate.  If we're going to dump the UTC
offset that way, we surely ought to reload it.  I've not looked at the
code yet, but from memory we allow +/-14 hours as a sane range.  Looks
like maybe we'd better run through all the Olson entries and see what is
least sane ...

Will fix, thanks for the report.

            regards, tom lane