pg_dump/pg_restore with time zone displacement out of range

From: Patric Bechtel <patric(dot)bechtel(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: pg_dump/pg_restore with time zone displacement out of range
Date: 2012-05-30 21:12:03
Message-ID: 4FC68D23.9050402@googlemail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

-----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(at)pht(dot)sql" tztest
$ grep -e '^[12]' tztest(at)pht(dot)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(at)pht(dot)sql
[...]
psql:tztest(at)pht(dot)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-----

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2012-05-30 21:19:09 Re: overwriting an existing .so while being used crashes the server process
Previous Message Tomas Vondra 2012-05-30 21:08:11 Re: overwriting an existing .so while being used crashes the server process