On Tue, Sep 24, 2013 at 10:19 PM, François Beausoleil <francois@xxxxxxxxxxx> wrote: > Hi all! > > I import many, many rows of data into a table, from three or more computers, 4 times per hour. I have a primary key, and the query I use to import the data is supposed to dedup before inserting, but I still get primary key violations. > > The import process is: > > * Load CSV data into temp table > * INSERT INTO dest SELECT DISTINCT (pkey) FROM temp WHERE NOT EXISTS(temp.pkey = dest.pkey) > > I assumed (erroneously) that this would guarantee no duplicate data could make it into the database. The primary key violations are proving me wrong. Right. Transaction A and B are interleaved: they both run the same check against the same id at the same time. Both checks pass because neither transaction is committed. This problem is not solvable by adjusting the isolation level. Typical solutions might be to: A. Lock the table while inserting B. Retry the transaction following an error. C. Import the records to a staging table, then copy the do the deduplication check when moving from the staging table merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
