Skip site navigation (1) Skip section navigation (2)

Re: Clarification, please

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Mladen Gogala <mladen(dot)gogala(at)vmsinfo(dot)com>
Cc: "pgsql-novice(at)postgresql(dot)org >> \"pgsql-novice(at)postgresql(dot)org\"" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Clarification, please
Date: 2010-12-01 16:59:07
Message-ID: 17839.1291222747@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

Responses

pgsql-novice by date

Next:From: Mladen GogalaDate: 2010-12-01 17:28:17
Subject: Re: Clarification, please
Previous:From: Mladen GogalaDate: 2010-12-01 16:49:02
Subject: Clarification, please

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group