Re: insert fail gracefully if primary key already exists

On Mon, Feb 13, 2012 at 4:36 PM, Alessandro Gagliardi
<alessandro(at)path(dot)com> wrote:
> Sorry I wasn't clear. The application is written in Python. It services
> requests that may go to an external API or to our production database (which
> is Mongo). I should add that we have a somewhat unusual situation in which
> we have two parallel databases: MongoDB for OLTP and newer PostgreSQL for
> OLAP. Because Postgres is only consuming, it would waste precious cycles to
> have the client check to see if a record already exists in Postgres before
> trying to insert it. I'd rather let Postgres deal with that (which it does
> well enough anyway with my primary key constraints). My Postgres instance is
> hosted by Heroku and they provide me with a log which is currently being
> spammed by these "duplicate key value violates unique constraint" errors
> making it impossible to see if there are other errors I need to be paying
> more attention to.

If your insertion process is single threaded (you don't have to worry
about concurrent inserts on the same key), convert your INSERT ...
VALUES to a INSERT SELECT ... WHERE NOT EXISTS().

If you have some concurrency, but not a lot such that you can
serialize all your inserts, you can do the above like this:

BEGIN;
LOCK foo;
INSERT INTO FOO SELECT ... WHERE NOT EXISTS().
COMMIT;

One reason to maybe not do that is if you have a high latency
connection to the database and your client api does not support
sending statements in batches.

Finally, if you have a lot of concurrency, you have to do the try
insert/loop on failure method on the client (which pollutes the log)
or the server (which does not, at least in plpgsql).

merlin