Обсуждение: date_trunc check constraint causes errors when restoring in a db with a different time zone
date_trunc check constraint causes errors when restoring in a db with a different time zone
От
Anthony Manfredi
Дата:
When I create a database dump from a database with time zone = UTC (my production machine) and attempt to load it in a database with the 'US/Eastern' time zone (my development machine), pg_restore reports that the dump violates a check constraint. The constraint uses date_trunc('day', <timestamptz>) to ensure that all values in the table are truncated to the same precision. I did not see this error before upgrading from to Postgresql 9.0 from 8.4. The following commands will reproduce the error: amanfredi@mercury:[~]$ createdb test_db amanfredi@mercury:[~]$ psql test_db psql (9.0.2) Type "help" for help. test_db=# select version(); version ------------------------------------------------------------------------------------------------------------------------------------------ PostgreSQL 9.0.2 on x86_64-apple-darwin10.5.0, compiled by GCC i686-apple-darwin10-gcc-4.2.1 (GCC) 4.2.1 (Apple Inc. build 5664), 64-bit (1 row) test_db=# set time zone 0; SET test_db=# show time zone; TimeZone ---------- 00:00:00 (1 row) test_db=# create table test_table ( start_time timestamp with time zone NOT NULL, CONSTRAINT time_days_start_time_ck CHECK ((start_time = date_trunc('day'::text, start_time))) ); CREATE TABLE test_db=# insert into test_table (start_time) values (date_trunc('day', 'Jan 15, 2010'::timestamptz)); INSERT 0 1 test_db=# select * from test_table; start_time ------------------------ 2010-01-15 00:00:00+00 (1 row) test_db=# \q amanfredi@mercury:[~]$ pg_dump -Fc -o -x test_db > test_db_dump.dmp amanfredi@mercury:[~]$ createdb test_db_2 amanfredi@mercury:[~]$ psql test_db_2 psql (9.0.2) Type "help" for help. test_db_2=# show time zone; TimeZone ------------ US/Eastern (1 row) test_db_2=# \q amanfredi@mercury:[~]$ pg_restore -d test_db_2 test_db_dump.dmp pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 1778; 0 505055 TABLE DATA test_table amanfredi pg_restore: [archiver (db)] COPY failed: ERROR: new row for relation "test_table" violates check constraint "time_days_start_time_ck" CONTEXT: COPY test_table, line 1: "2010-01-14 19:00:00-05" WARNING: errors ignored on restore: 1 Best, Anthony
Anthony, As per my knowledge, this can't work on pg84 dump/restore too. You should match timezone on restoring database with the dump database to load it successfully. You can set at user level or database level. On restoring database: ALTER DATABASE test_db_2 SET TIMEZONE TO 'UTC'; OR ALTER USER postgres SET TIMEZONE TO 'UTC'; Hope , it will help. On Sat, Jan 15, 2011 at 10:50 AM, Anthony Manfredi <amanfredi@gilt.com>wrote: > When I create a database dump from a database with time zone = UTC (my > production machine) and attempt to load it in a database with the > 'US/Eastern' time zone (my development machine), pg_restore reports > that the dump violates a check constraint. The constraint uses > date_trunc('day', <timestamptz>) to ensure that all values in the > table are truncated to the same precision. I did not see this error > before upgrading from to Postgresql 9.0 from 8.4. > > The following commands will reproduce the error: > > amanfredi@mercury:[~]$ createdb test_db > amanfredi@mercury:[~]$ psql test_db > psql (9.0.2) > Type "help" for help. > > test_db=# select version(); > > version > > ------------------------------------------------------------------------------------------------------------------------------------------ > PostgreSQL 9.0.2 on x86_64-apple-darwin10.5.0, compiled by GCC > i686-apple-darwin10-gcc-4.2.1 (GCC) 4.2.1 (Apple Inc. build 5664), > 64-bit > (1 row) > > test_db=# set time zone 0; > SET > test_db=# show time zone; > TimeZone > ---------- > 00:00:00 > (1 row) > > test_db=# create table test_table ( start_time timestamp with time > zone NOT NULL, CONSTRAINT time_days_start_time_ck CHECK ((start_time = > date_trunc('day'::text, start_time))) ); > CREATE TABLE > test_db=# insert into test_table (start_time) values > (date_trunc('day', 'Jan 15, 2010'::timestamptz)); > INSERT 0 1 > test_db=# select * from test_table; > start_time > ------------------------ > 2010-01-15 00:00:00+00 > (1 row) > > test_db=# \q > amanfredi@mercury:[~]$ pg_dump -Fc -o -x test_db > test_db_dump.dmp > amanfredi@mercury:[~]$ createdb test_db_2 > amanfredi@mercury:[~]$ psql test_db_2 > psql (9.0.2) > Type "help" for help. > > test_db_2=# show time zone; > TimeZone > ------------ > US/Eastern > (1 row) > > test_db_2=# \q > amanfredi@mercury:[~]$ pg_restore -d test_db_2 test_db_dump.dmp > pg_restore: [archiver (db)] Error while PROCESSING TOC: > pg_restore: [archiver (db)] Error from TOC entry 1778; 0 505055 TABLE > DATA test_table amanfredi > pg_restore: [archiver (db)] COPY failed: ERROR: new row for relation > "test_table" violates check constraint "time_days_start_time_ck" > CONTEXT: COPY test_table, line 1: "2010-01-14 19:00:00-05" > WARNING: errors ignored on restore: 1 > > > Best, > Anthony > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs > -- Denish Patel, OmniTi Computer Consulting Inc. Database Administrator, Phone: 443.325.1357 x 232
Re: date_trunc check constraint causes errors when restoring in a db with a different time zone
От
Anthony Manfredi
Дата:
Thanks, that is helpful. There was a separate reason it didn't appear to be happening before the 8.4 upgrade. I couldn't find this mentioned in the documentation anywhere. Should it be included in the pg_restore section? -Anthony On Sun, Jan 16, 2011 at 11:06 AM, Denish Patel <denish@omniti.com> wrote: > Anthony, > As per my knowledge, this can't work on pg84 dump/restore too. You should > match timezone on restoring database with the dump database to load it > successfully. You can set at user level or database level. > =A0On restoring database: > =A0=A0 ALTER DATABASE=A0test_db_2 SET TIMEZONE TO 'UTC'; > =A0=A0 =A0OR > =A0=A0 ALTER USER postgres=A0SET TIMEZONE TO 'UTC'; > Hope , it will help.