Message-ID: <4CF67BE3.30304@vmsinfo.com>
Date: Wed, 01 Dec 2010 11:46:27 -0500
From: Mladen Gogala <mladen.gogala@vmsinfo.com>
Organization: VMS
User-Agent: Thunderbird 2.0.0.24 (X11/20101027)
MIME-Version: 1.0
To: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Clarification, please
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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