2012/2/17 Merlin Moncure
<mmoncure@gmail.com>On Fri, Feb 17, 2012 at 12:46 PM, Alessandro Gagliardi
<
alessandro@path.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice