Re: Clarification, please

On Wed, Dec 1, 2010 at 8:46 AM, Mladen Gogala <mladen(dot)gogala(at)vmsinfo(dot)com> wrote:

> PostgreSQL 9.0, however, creates a unique index:
>
>   scott=# create table test1
>   scott-# (col1 integer,
>   scott(#  constraint test1_pk primary key(col1) deferrable);
>   NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
>   "test1_pk" for table "test1"
>   CREATE TABLE
>   Time: 67.263 ms
>   scott=# select indexdef from pg_indexes where indexname='test1_pk';
>                            indexdef
> ----------------------------------------------------------
>    CREATE UNIQUE INDEX test1_pk ON test1 USING btree (col1)
>   (1 row)
>
> When the constraint is deferred in the transaction block, however, it
> tolerates duplicate values until the end of transaction:
>
>   scott=# begin;                               BEGIN
>   Time: 0.201 ms
>   scott=# set constraints test1_pk  deferred;
>   SET CONSTRAINTS
>   Time: 0.651 ms
>   scott=# insert into test1 values(1);
>   INSERT 0 1
>   Time: 1.223 ms
>   scott=# insert into test1 values(1);
>   INSERT 0 1
>   Time: 0.390 ms
>   scott=# rollback;
>   ROLLBACK
>   Time: 0.254 ms
>   scott=#
>
>
> No errors here. How is it possible to insert the same value twice into a
> UNIQUE index? What's going on here?

http://www.postgresql.org/docs/9.0/interactive/sql-createtable.html
DEFERRABLE
NOT DEFERRABLE

This controls whether the constraint can be deferred. A constraint
that is not deferrable will be checked immediately after every
command. Checking of constraints that are deferrable can be postponed
until the end of the transaction (using the SET CONSTRAINTS command).
NOT DEFERRABLE is the default. Currently, only UNIQUE, PRIMARY KEY,
EXCLUDE, and REFERENCES (foreign key) constraints accept this clause.
NOT NULL and CHECK constraints are not deferrable.

It looks like the check isn't preformed until COMMIT.

--
Regards,
Richard Broersma Jr.