Обсуждение: 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
On Wed, Dec 1, 2010 at 8:46 AM, Mladen Gogala <mladen.gogala@vmsinfo.com> wrote: > 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? http://www.postgresql.org/docs/9.0/interactive/sql-createtable.html DEFERRABLE NOT DEFERRABLE This controls whether the constraint can be deferred. A constraint that is not deferrable will be checked immediately after every command. Checking of constraints that are deferrable can be postponed until the end of the transaction (using the SET CONSTRAINTS command). NOT DEFERRABLE is the default. Currently, only UNIQUE, PRIMARY KEY, EXCLUDE, and REFERENCES (foreign key) constraints accept this clause. NOT NULL and CHECK constraints are not deferrable. It looks like the check isn't preformed until COMMIT. -- Regards, Richard Broersma Jr.
Richard Broersma wrote: > > It looks like the check isn't preformed until COMMIT. > > So, the index is not actually updated until commit? Hmmmm, that seems unlikely. -- 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> wrote: > How is it possible to insert the same value twice into a UNIQUE > index? You get multiple entries for the same value in a UNIQUE indexes all the time in PostgreSQL. Any non-HOT update of a table with a UNIQUE index will cause that. You just can't have duplicate entries with overlapping visibility. -Kevin