Re: misleading error message in 8.5, and bad (?) way deferred uniqueness works

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