Skip site navigation (1) Skip section navigation (2)

Clarification, please

From: Mladen Gogala <mladen(dot)gogala(at)vmsinfo(dot)com>
To: "pgsql-novice(at)postgresql(dot)org >> \"pgsql-novice(at)postgresql(dot)org\"" <pgsql-novice(at)postgresql(dot)org>
Subject: Clarification, please
Date: 2010-12-01 16:49:02
Message-ID: 4CF67C7E.9040307@vmsinfo.com (view raw or flat)
Thread:
Lists: pgsql-novice
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




Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2010-12-01 16:59:07
Subject: Re: Clarification, please
Previous:From: Tom LaneDate: 2010-11-30 21:27:11
Subject: Re: RV: encoding problem

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group