Re: insert fail gracefully if primary key already exists

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