Re: [BUGS] date_trunc check constraint causes errors when restoring in a db with a different time zone
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 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', ) 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: [BUGS] Problems with adding a is not null to a query.
Tim Uckun writes: >> Possibly the table's never been ANALYZEd ... do you have autovacuum >> enabled? > I do have autovacuum enabled and I am running 8.4 Hmm, autovacuum *should* have been keeping track of things for you, but it might still be worth doing a manual ANALYZE against that table to see if the estimated rowcount changes. If not, you'll need to raise the statistics target for that column (and again ANALYZE). 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
Re: [BUGS] BUG #5835: PL/Python crashes
I just tried and it works. Thank you very much.
-Message d'origine-
De : Alex Hunsaker [mailto:bada...@gmail.com]
Envoyé : vendredi 14 janvier 2011 07:33
À : Julien Demoor
Cc : pgsql-bugs@postgresql.org
Objet : Re: [BUGS] BUG #5835: PL/Python crashes
On Thu, Jan 13, 2011 at 06:57, Julien Demoor wrote:
>
> The following bug has been logged online:
>
> Bug reference: 5835
> Logged by: Julien Demoor
> Email address: jdem...@gmail.com
> PostgreSQL version: 9.0.2
> Operating system: Ubuntu Linux
> Description:PL/Python crashes
> Details:
> BEGIN;
> CREATE OR REPLACE FUNCTION test_none_3(a numeric[]) RETURNS
> numeric(12,4) AS $$
>return 1;
> $$ LANGUAGE plpython2u;
> SELECT test_none_3('{NULL, NULL, NULL}'::numeric[]) FROM
> generate_series(1,244) ORDER BY generate_series DESC LIMIT 1;
> ROLLBACK;
It looks like we are not incrementing a reference count for NULL entries in an
array. The attached fixes it for me. I did look for other places where we
might have missed this and did not find any. ( the PyDict_SetItemString() that
use Py_None look fishy, but apparently work ).
Can you see if it fixes it for you?
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Problems with adding a is not null to a query.
> Hmm, autovacuum *should* have been keeping track of things for you, > but it might still be worth doing a manual ANALYZE against that table > to see if the estimated rowcount changes. If not, you'll need to raise > the statistics target for that column (and again ANALYZE). > I started a manual VACUUM ANALYZE on the table. It's been running for about an half hour now, The table gets quite busy so I am hoping there is no real harm in letting go to the end. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Problems with adding a is not null to a query.
> > Hmm, autovacuum *should* have been keeping track of things for you, > but it might still be worth doing a manual ANALYZE against that table > to see if the estimated rowcount changes. If not, you'll need to raise > the statistics target for that column (and again ANALYZE). The analyze finished. I re-ran the explain it was still taking a very long time. I stopped it eventually. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
