pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedClarification, please
4+ messages / 3 participants
[nested] [flat]
* Clarification, please
@ 2010-12-01 16:46 Mladen Gogala <mladen.gogala@vmsinfo.com>
0 siblings, 2 replies; 4+ messages in thread
From: Mladen Gogala @ 2010-12-01 16:46 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Clarification, please
@ 2010-12-01 16:57 Richard Broersma <richard.broersma@gmail.com>
parent: Mladen Gogala <mladen.gogala@vmsinfo.com>
1 sibling, 1 reply; 4+ messages in thread
From: Richard Broersma @ 2010-12-01 16:57 UTC (permalink / raw)
To: Mladen Gogala <mladen.gogala@vmsinfo.com>; +Cc: pgsql-performance
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Clarification, please
@ 2010-12-01 17:06 Mladen Gogala <mladen.gogala@vmsinfo.com>
parent: Richard Broersma <richard.broersma@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Mladen Gogala @ 2010-12-01 17:06 UTC (permalink / raw)
To: Richard Broersma <richard.broersma@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Clarification, please
@ 2010-12-01 17:13 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Mladen Gogala <mladen.gogala@vmsinfo.com>
1 sibling, 0 replies; 4+ messages in thread
From: Kevin Grittner @ 2010-12-01 17:13 UTC (permalink / raw)
To: pgsql-performance; Mladen Gogala <mladen.gogala@vmsinfo.com>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2010-12-01 17:13 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-12-01 16:46 Clarification, please Mladen Gogala <mladen.gogala@vmsinfo.com>
2010-12-01 16:57 ` Richard Broersma <richard.broersma@gmail.com>
2010-12-01 17:06 ` Mladen Gogala <mladen.gogala@vmsinfo.com>
2010-12-01 17:13 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
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