From: | Dean Rasheed <dean(dot)a(dot)rasheed(at)googlemail(dot)com> |
---|---|
To: | depesz(at)depesz(dot)com |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: misleading error message in 8.5, and bad (?) way deferred uniqueness works |
Date: | 2009-08-11 12:21:25 |
Message-ID: | 8e2dbb700908110521w9e2b735sfceb93f41d17b100@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
2009/8/11 hubert depesz lubaczewski <depesz(at)depesz(dot)com>:
> While testing deferred unique constraints I found this:
>
> # CREATE TABLE test (
> i INT4 PRIMARY KEY
> );
> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test"
> CREATE TABLE
>
> # set constraints test_pkey deferred;
> ERROR: constraint "test_pkey" does not exist
>
The constraint needs to be declared DEFERRABLE before you can defer
it, but yes, I agree this is not a helpful error message.
[The reason is that it actually searches for the trigger enforcing the
constraint, and there isn't one if it's not deferrable. So the current
code can't distinguish between a non-existent unique constraint and a
non-deferrable one.]
> As I understand (I might be wrong, so please clarify if I am), when I
> create table with primary key that is "deferrable initially immediate",
> it will act as immediate unless i will set it to deferred with "set
> constraints".
>
> If that's true, then why it works:
> # INSERT INTO test (i) values (1), (2), (3);
> INSERT 0 3
> # update test set i = i + 1;
> UPDATE 3
>
> shouldn't it raise exception? and work *only* if i set the constraint to
> deferred?
>
"Immediate" actually means at the end of the statement rather than
after each row for deferrable constraints. See
http://developer.postgresql.org/pgdocs/postgres/sql-createtable.html
- Dean
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2009-08-11 16:40:30 | Re: misleading error message in 8.5, and bad (?) way deferred uniqueness works |
Previous Message | hubert depesz lubaczewski | 2009-08-11 11:14:46 | misleading error message in 8.5, and bad (?) way deferred uniqueness works |