Обсуждение: Clarification, please

In Oracle, deferrable primary keys are enforced by non-unique indexes.
That seems logical, because index should tolerate duplicate values for
the duration of transaction:

    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
    With the Partitioning, OLAP, Data Mining and Real Application
    Testing options

    SQL> create table test1
      2  (col1 integer,
      3   constraint test1_pk primary key(col1) deferrable);

    Table created.

    Elapsed: 00:00:00.35
    SQL> select uniqueness from user_indexes where index_name='TEST1_PK';

    UNIQUENES
    ---------
    NONUNIQUE

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?


--

Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com
The Leader in Integrated Media Intelligence Solutions

On Wed, Dec 1, 2010 at 8:46 AM, Mladen Gogala <mladen.gogala@vmsinfo.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.

Richard Broersma wrote:
>
> It looks like the check isn't preformed until COMMIT.
>
>
So, the index is not actually updated until commit? Hmmmm, that seems
unlikely.

--

Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com
The Leader in Integrated Media Intelligence Solutions

Mladen Gogala <mladen.gogala@vmsinfo.com> wrote:

> How is it possible to insert the same value twice into a UNIQUE
> index?

You get multiple entries for the same value in a UNIQUE indexes all
the time in PostgreSQL.  Any non-HOT update of a table with a UNIQUE
index will cause that.  You just can't have duplicate entries with
overlapping visibility.

-Kevin