Hi All,
I encountered an odd issue regarding check constraints complaining
when they're not really violated.
For this particular machine, I am running 8.3.7, but on a machine
running 8.3.5, it seems to have succeeded. I also upgraded a third
machine from 8.3.5 to 8.3.7, and the query succeeded (so I'm thinking
it's not related to different postgres versions)
I have a table called "m_class" and the definition is something like
this:
tii=# \d m_class
Table "public.m_class"
Column | Type
| Modifiers
-------------------------+--------------------------
+--------------------------------------------------------------
id | integer | not null
default nextval(('m_class_id_seq'::text)::regclass)
class_type | smallint | not null
title | character varying(100) | not null
...snip...
date_setup | timestamp with time zone | not null
default ('now'::text)::date
date_start | timestamp with time zone | not null
date_end | timestamp with time zone | not null
term_length | interval | not null
default '5 years'::interval
...snip...
max_portfolio_file_size | integer |
Indexes:
"m_class_pkey" PRIMARY KEY, btree (id)
"m_class_account_idx" btree (account)
"m_class_instructor_idx" btree (instructor)
Check constraints:
"end_after_start_check" CHECK (date_end >= date_start)
"end_within_term_length" CHECK (date_end <= (date_start +
term_length))
"min_password_length_check" CHECK
(length(enrollment_password::text) >= 4)
"positive_term_length" CHECK (term_length > '00:00:00'::interval)
"start_after_setup_check" CHECK (date_start >= date_setup)
...snip...
When I run my update, it fails:
tii=# begin; update only "public"."m_class" set date_end='2009-09-03
05:38:24.030331-07',term_length='177 days 17:59:09.868431' where
id='2652020';
BEGIN
ERROR: new row for relation "m_class" violates check constraint
"end_within_term_length"
tii=# rollback;
ROLLBACK
The data reads:
tii=# select date_start, date_end, term_length, '2009-09-03
05:38:24.030331-07'::timestamptz - date_start AS new_term_length
from m_class where id = 2652020;
date_start | date_end |
term_length | new_term_length
-----------------------------+-----------------------------
+-------------+--------------------------
2009-03-09 11:39:14.1619-07 | 2009-04-08 11:39:14.1619-07 | 30
days | 177 days 17:59:09.868431
Based on new_term_length, the update should succeed. However, it
doesn't. Would anyone have an explanation?
Thanks for your help!
--Richard
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
