Re: INSERT only unique records

On Fri, 2009-07-10 at 13:32 -0700, Mark Felegyhazi wrote:

> 1. put a unique constraint on num in to_t
> -> problem: the first violation breaks the subquery and the remaining records are never inserted - I don't know how to catch the error in subqueries
>
> 2. create the following insert rule:

3. Use a BEFORE INSERT OR UPDATE ... FOR EACH ROW trigger to test for a
duplicate row and return NULL (making the query a no-op) if so.

Your trigger will need to obtain a LOCK TABLE ... IN EXCLUSIVE MODE lock
on the table to prevent concurrent inserts resulting in duplicates.
Because the INSERT / UPDATE on the table will've already acquired a
lesser lock, your trigger will be attempting a lock upgrade, which has a
pretty strong chance of resulting in a deadlock if you have concurrent
inserts on the table. To avoid this, make sure your transactions obtain
an EXCLUSIVE lock on the table before attempting the insert. Failure to
do so won't risk data integrity, but may result in an automatic
transaction rollback due to deadlock if concurrent insert/update queries
are in progress.

Make sure you have a unique constraint in place. It'll help the planner
out, and will catch mistakes.

This approach is nasty in an environment where concurrent inserts are
common.

4. Rely on the unique constraint, and do your inserts one-per-statement
with something like:

INSERT INTO to_t (num)
SELECT 4 WHERE NOT EXISTS (SELECT 1 FROM to_t AS tt WHERE tt.num = 4)

5. Don't worry about the duplicates. Let them be inserted, and weed them
out later or use a view with a GROUP BY to pick distinct rows.

6. Do your inserts via a PL/PgSQL function that sets a savepoint before
each insert and rolls back to the savepoint if the unique constraint
generates a unique violation exception.

--
Craig Ringer