Re: Duplicate key insert question

Shridhar Daithankar wrote:

Again, please let me preface my email. The application in question is
not written by me. I am trying to find a simple and easily implementable
way of making the current implementation more postgres friendly :)

> With postgresql, if you use transacations, no valid inserts are ever lost
> unless there is a deadlock.

Just needed to make sure as I don't know much about triggers.

>>2- reduce the number of error messages logged as a result of
>>'collisions' between two backends trying to insert duplicate primary key
>>rows.
>
> Log of what? Postgresql will log all the errors. If you don't want to see them,
> tune the logging option.

I don't want to turn all error logging off, actually I don't want to
turn any error logging off :) If errors are logged it usually means
there is a bug some somewhere ...

> If your code is doing any checking for return value etc., check if it is a
> duplicate message and discard it. That way you can minimize your application
> logging.

It's not my application logging, it's postgres logging the fact that a
duplicate insertion was attempted:

ERROR: Cannot insert a duplicate key into unique index pot_documents_pkey

> Otherwise use select/insert behaviour referred earlier.

But the trigger is nicer since none of the application code would need
to be changed at all. I would just add a trigger to the database
creation script.

> And if your dulicates are bit too frequent for comfort, you might
> have some more issues w.r.t database table design to look at.

They are frequent, and I agree that there are issues with the database
design. But the application developers don't want to address them (if
it's broke why fix it kind of thing) ...

I just noticed that their app was generating a *lot* of log entries when
used with a postgres database and I offered to try and find a way of
reducing the number of errors generated.

Just trying to find a way to make *their* app play nice with postgres so
that people who do use their app will use it with postgres as a DB and
not that *other* DB ...

Jean-Christian Imbeault