Postgresql insert stopped working, duplicate key value violations

Question

About 8 months ago I used a suggestion to set up a holding table, then push to the formal table and prevent duplicate entries, per this post: Best way to prevent duplicate data on copy csv postgresql

It's been working very nicely, but today I noticed some errors and gaps in the data.

Here's my insert statement:

And here's how the index is set up:

And here's an example of the error I'm getting, although on the next chron insert, it went through.

Here's it going through fine:

I haven't noted any large changes in the data incoming. Here's what the data looks like that's coming in now: In summary, I've noticed recent oddities with the insert statements, and success is erratic, resulting in large data gaps in the database. Thanks for any help, and I'm happy to provide more details, but I wanted to see if my information sounds like something someone else has already dealt with.

Thanks very much for any help, S

Answer 1

As Gordon pointed out in his answer to your previous question, this approach only works if you have exclusive access to the table. There is a delay between the existence check and the insert itself, and if another process modifies the table during this window, you may end up with duplicates.

If you're on Postgres 9.5+, the best approach is to skip the existence check and simply use an INSERT ... ON CONFLICT DO NOTHING statement.

On earlier versions, the simplest solution (if you can afford to do so) would be to lock the table for the duration of the import. Otherwise, you can emulate ON CONFLICT DO NOTHING (albeit less efficiently) using a loop and an exception handler:

DO $$
DECLARE r RECORD;
BEGIN
  FOR r IN (SELECT * FROM holding) LOOP
    BEGIN
      INSERT INTO ltg_data (pulsecount, intensity, time, lon, lat, ltg_geom)
      VALUES (r.pulsecount, r.intensity, r.time, r.lon, r.lat, r.ltg_geom);
    EXCEPTION WHEN unique_violation THEN
    END;
  END LOOP;
END
$$

---

As an aside, DELETE FROM holding is time-consuming and probably unnecessary. Create your staging table as TEMP, and it will be cleaned up automatically at the end of your session. You can easily build a table which matches the structure of your import target:

CREATE TEMP TABLE holding (LIKE ltg_data);