From: | Bartosz Dmytrak <bdmytrak(at)eranet(dot)pl> |
---|---|
To: | Merlin Moncure <mmoncure(at)gmail(dot)com>, Alessandro Gagliardi <alessandro(at)path(dot)com> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: insert fail gracefully if primary key already exists |
Date: | 2012-02-17 21:39:18 |
Message-ID: | CAD8_UcbSWGw=rfRo3Ve57qo5q_M1M0=k9GSavC5Q8jqU_mEDjQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Alessandro Gagliardi | 2012-02-17 22:40:18 | execute many for each commit |
Previous Message | Alessandro Gagliardi | 2012-02-17 21:24:11 | Re: insert fail gracefully if primary key already exists |