Re: insert fail gracefully if primary key already exists

Few comments.

Regards,
Bartek

2012/2/17 Merlin Moncure <mmoncure(at)gmail(dot)com>

> 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.
>
Yes indeed - column order must be the same

> >
> > 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).
>
Why not? *All* checks cost - so transaction will be a little bit longer,
but You have to chose: longer transaction or log messages, we can discuss
which solution is faster (trigger, function or modified SQL statement)

> > Locking tables seems like a bad idea in this case.
>
Yes - I think this is not good idea to lock table, let postgres do this
kind of things in that case

> What would happen if I
> > didn't lock and I tried this?
>
Nothing :) postgre will lock table properly - triggers are part of
transation.

> 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.
>
There is alternate solution as You mentioned few posts ago :)
In my oppinion data logic should be kept as near to data as possible, it
means DB should protect itself against data inconsistency, but every
solution is good if is acceptable.

>
> merlin
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice
>