Message-ID: <484EE821.6090700@mongonet.net>
Date: Tue, 10 Jun 2008 13:46:25 -0700
From: Dinesh <dbhandary@mongonet.net>
User-Agent: Thunderbird 2.0.0.14 (Windows/20080421)
MIME-Version: 1.0
To: Scott Marlowe <scott.marlowe@gmail.com>
CC: pgsql-admin@postgresql.org
Subject: Re: error handling unique key constraint in pgsql
References: <484EB14B.4010003@mongonet.net>
	<dcc563d10806101209x4edfd77dl28bf514a0742d845@mail.gmail.com>
In-Reply-To: <dcc563d10806101209x4edfd77dl28bf514a0742d845@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi Scott,

Thanks for your reply.

Is there a built in exception in pl/pgsql ( similar to oracle's 
|DUP_VAL_ON_INDEX ) that I can use?

I ran into an unique situation a couple of days ago. Procedure that 
inserts a new value into unique index column was called at the same 
time. I even check if the value exist before inserting, but one of the 
instance of the function gave me an unique constraint violation error.

My code looks like this:

select into var * from table;

if not found then
 insert into table
 values (a);
end if;
|
I would imagine the postgres db would inherently handle situation like 
this, but it did not.

Thanks.

Dinesh


Scott Marlowe wrote:
> On Tue, Jun 10, 2008 at 10:52 AM, Dinesh <dbhandary@mongonet.net> wrote:
>   
>> Hi All,
>>
>> I was wondering if there is an easy way to handle unique key constraint in
>> pgsql. Right now my entire transaction aborts if it tries to insert
>> duplicate keys, but I would like to handle this
>> scenario in such a way that the transaction will not break.
>>
>> Please let me know if you have done something like this. I greatly
>> appreciate your help.
>>     
>
> There are a couple of approaches:
>
> process the inserts in a pl/pgsql function  and use the exception
> handling in there to catch them.
>
> Use savepoints.
>
> Both of these use the same underlying mechanisms, and both tend to be
> expensive in terms of overhead.
>
>