Обсуждение: 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.gogala@vmsinfo.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.gogala@vmsinfo.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.gogala@vmsinfo.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