Clarification, please

From: Mladen Gogala <mladen(dot)gogala(at)vmsinfo(dot)com>
To: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Clarification, please
Date: 2010-12-01 16:46:27
Message-ID: 4CF67BE3.30304@vmsinfo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Browse pgsql-performance by date

  From Date Subject
Next Message Richard Broersma 2010-12-01 16:57:01 Re: Clarification, please
Previous Message Mladen Gogala 2010-12-01 16:34:04 Re: SELECT INTO large FKyed table is slow