From: Mladen Gogala <mladen.gogala@vmsinfo.com>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Clarification, please
Date: Wed, 01 Dec 2010 11:46:27 -0500
Message-ID: <4CF67BE3.30304@vmsinfo.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mladen.gogala@vmsinfo.com
Subject: Re: Clarification, please
In-Reply-To: <4CF67BE3.30304@vmsinfo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox