Re: Duplicate key insert question

Nigel J. Andrews wrote:
>
> Skipping ahead without reading all the messages in this thread (got to rush
> out) what about using a before insert trigger, doing the check in there,
> returning null if the insert would fail and see if they complain about the slow
> down :)

Oooh! I think I like that .... there would be no penalty for the MySQL
version since this would be a trigger and only in the postgres database
version of the code.

Will a trigger still allow one insert to succeed if there are multiple
backends trying to insert the same primary key in a table? There must be
no 'lost' inserts ....


> Of course it's still got the race condition for the application unless you also
> lock the table and it'll screw up any use of currval(sequence) afterwards that
> expects to get the id of the row inserted with a id obtained from
> nextval(sequence)

I just want two things from any valid solution:

1- if there is an insert and there is not row with the new insert's
primary key then the insert is made. If there are multiple inserts one
succeeds. i.e. No valid inserts will be 'lost'.

2- reduce the number of error messages logged as a result of
'collisions' between two backends trying to insert duplicate primary key
rows.

I am not very familiar with triggers so I would very much appreciate any
feedback. But I think the use of a trigger might safe?

Thanks,

Jean-Christian Imbeault