Re: 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

Mladen Gogala <mladen(dot)gogala(at)vmsinfo(dot)com> writes:
> In Oracle, deferrable primary keys are enforced by non-unique indexes.
> That seems logical,

... maybe to an Oracle guy ...

> When the constraint is deferred in the transaction block, however, it
> tolerates duplicate values until the end of transaction:

Sure. That's exactly per spec: the check is deferred to end of
transaction. If the duplicated index entries are both/all still live
at that time, you get an error.

We do still execute the insertion-time uniqueness check, but instead of
throwing an error on failure, we just queue a trigger event to recheck
that key before commit. If the insertion-time check passes then there's
no need for a recheck later. This is a win because the insertion-time
check is cheap, being integrated into the insertion process itself.

regards, tom lane

Tom Lane wrote:
> Mladen Gogala <mladen(dot)gogala(at)vmsinfo(dot)com> writes:
>
>> In Oracle, deferrable primary keys are enforced by non-unique indexes.
>> That seems logical,
>>
>
> ... maybe to an Oracle guy ...
>

I humbly admit being one. I am getting used to the life without the dark
side of the force, however. I saw the light, I am saved. When the
rapture comes, I will not be left behind. However, I still have to
maintain a rather big 4-way Oracle RAC configuration and some satellite
Oracle databases.

>
>> When the constraint is deferred in the transaction block, however, it
>> tolerates duplicate values until the end of transaction:
>>
>
> Sure. That's exactly per spec: the check is deferred to end of
> transaction. If the duplicated index entries are both/all still live
> at that time, you get an error.
>

I agree with you. I was only wandering how was it done with a unique index.

> We do still execute the insertion-time uniqueness check, but instead of
> throwing an error on failure, we just queue a trigger event to recheck
> that key before commit. If the insertion-time check passes then there's
> no need for a recheck later. This is a win because the insertion-time
> check is cheap, being integrated into the insertion process itself.
>
> regards, tom lane
>
Thanks for a wonderful explanation. That's all I needed.

--

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(dot)gogala(at)vmsinfo(dot)com> writes:
> I agree with you. I was only wandering how was it done with a unique index.

Maybe the point you're missing is that PG unique indexes aren't unique
in the sense of it being physically impossible to represent duplicate
keys. The uniqueness property just means that there's a check that
throws error instead of allowing a live duplicate to be inserted.
(Dead duplicates are OK --- in particular, we have to allow multiple
nominal duplicates to support MVCC, since an update requires a new
physical table entry and hence a new index entry pointing at it, in
most cases.)

regards, tom lane