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

Re: Clarification, please

From: Mladen Gogala <mladen(dot)gogala(at)vmsinfo(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
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 17:28:17
Message-ID: 4CF685B1.1040601@vmsinfo.com (view raw or flat)
Thread:
Lists: pgsql-novice
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




In response to

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2010-12-01 17:34:53
Subject: Re: Clarification, please
Previous:From: Tom LaneDate: 2010-12-01 16:59:07
Subject: Re: Clarification, please

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