Re: INSERT only unique records - Mailing list pgsql-general

From Craig Ringer
Subject Re: INSERT only unique records
Date
Msg-id 1247387295.18105.15.camel@ayaki
Whole thread Raw
In response to INSERT only unique records  (Mark Felegyhazi <m_felegyhazi@yahoo.com>)
List pgsql-general
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
tocatch 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


pgsql-general by date:

Previous
From: Scott Marlowe
Date:
Subject: Re: indexes on float8 vs integer
Next
From: Craig Ringer
Date:
Subject: Re: Weird disk/table space consumption problem