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

Re: insert fail gracefully if primary key already exists

From: Merlin Moncure <mmoncure(at)gmail(dot)com>
To: Alessandro Gagliardi <alessandro(at)path(dot)com>
Cc: Bartosz Dmytrak <bdmytrak(at)eranet(dot)pl>, pgsql-novice(at)postgresql(dot)org
Subject: Re: insert fail gracefully if primary key already exists
Date: 2012-02-17 19:39:27
Message-ID: CAHyXU0xQ_CcosWYMDU2nA29Ydz3nDQDB77x_Px_PNEvTi7x0Pg@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
On Fri, Feb 17, 2012 at 12:46 PM, Alessandro Gagliardi
<alessandro(at)path(dot)com> wrote:
> With some experimentation, it seems critical that the SELECT statement use
> the exact same order of columns as the table (which means I have to fill in
> NULL values and the like). That is an acceptable nuisance, but I thought I'd
> ask in case there's a better way.
>
> I'm not sure if this approach will work anyway though since I've got
> concurrency (about a dozen API servers constantly writing to the database).
> Locking tables seems like a bad idea in this case. What would happen if I
> didn't lock and I tried this? It seems like it should just throw the same
> error I'm already used to getting, though hopefully with less frequency (as
> it would only occur if the same insert was attempted twice simultaneously).
> Is there any chance I could actually end up getting dupes if I tried this
> without a lock?

no dupes.  agree that lock is not a good fit for your case -- you can
just deal with the occasional bump (you'll only seem them if and only
if two sessions attempt to write to the same key at approximately the
same time) or expend the extra effort to remove them completely if you
want with a plpgsql error handling routine.

I personally dislike wrapping trivial SQL operations with plpgsql...it
deabstractifies the SQL language.

merlin

In response to

Responses

pgsql-novice by date

Next:From: Bartosz DmytrakDate: 2012-02-17 21:18:48
Subject: Re: insert fail gracefully if primary key already exists
Previous:From: Léa MassiotDate: 2012-02-17 19:31:27
Subject: Re: Clusters list - Windows PostgreSQL server

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