Thread: error handling unique key constraint in pgsql

error handling unique key constraint in pgsql

From
Dinesh
Date:
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.

Thanks,

Dinesh

Re: error handling unique key constraint in pgsql

From
"Scott Marlowe"
Date:
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.

Re: error handling unique key constraint in pgsql

From
Dinesh
Date:
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.
>
>


Re: error handling unique key constraint in pgsql

From
"Alex Hunsaker"
Date:
On Tue, Jun 10, 2008 at 2:46 PM, Dinesh <dbhandary@mongonet.net> wrote:
> 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.


See http://www.postgresql.org/docs/8.3/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING,
specifically  example Example 38-1.

Re: error handling unique key constraint in pgsql

From
Dinesh
Date:
Great. That's what I was looking for. Thanks a lot guys.

Dinesh

Alex Hunsaker wrote:
> On Tue, Jun 10, 2008 at 2:46 PM, Dinesh <dbhandary@mongonet.net> wrote:
>
>> 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.
>>
>
>
> See http://www.postgresql.org/docs/8.3/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING,
> specifically  example Example 38-1.
>
>