How can I get PostgreSQL to not advance sequences when COPY fails?

Question

I'm importing CSV files into a relatively simple PostgreSQL database. (The CSVs are sometimes created manually from information in a book, sometimes created from spreadsheets with a little massaging via scripting.) I do it in small batches to make sure things are going ok, and I've noticed that when COPY fails, it doesn't roll back the sequence attached to the column in the table. (Note: I am importing via pgAdmin.)

For example, let's say this is my table:

TABLE
------------------
id | data1 | data2
------------------
1  | abc   | def
2  | ghi   | klm

Then let's say I try to import two more columns and it fails. I fix the error, and then the import succeeds. I'd expect the table to look like this:

TABLE
------------------
id | data1 | data2
------------------
1  | abc   | def
2  | ghi   | klm
3  | nop   | qrs
4  | tuv   | wxy

Instead, it looks like this:

TABLE
------------------
id | data1 | data2
------------------
1  | abc   | def
2  | ghi   | klm
5  | nop   | qrs
6  | tuv   | wxy

The tables all rely on each other (i.e. pretty much every table has a FK pointing to the ID of some other table), so if the IDs stay predictable, my data entry job gets a lot easier. If not, I have to keep double checking the IDs when I finish a section.

Is there any way to prevent this behavior?

Answer 1

The sequences are designed like that, the are not supposed to produce contiguous numbers.

However, you can reset them before (re)trying the COPY with

SELECT setval('sequence_name', (SELECT max(id) FROM your_table));

Answer 2

Don't use a sequence if you want contiguous numbers that roll back with an aborted transaction.

Write a pl/pgsql procedure implementing a nextval_gapless that increments a counter in a table. This counter will get rolled back if the transaction aborts.

Of course, you also get the downsides of this approach: no concurrency, and under some circumstances possible deadlock transaction aborts if you attempt concurrency.

Simple example of an id generator:

CREATE TABLE mytable_gapless_seq(nextid integer);

INSERT INTO mytable_gapless_seq(nextid) VALUES (0);

CREATE OR REPLACE FUNCTION nextval_gapless(idtable regclass) RETURNS integer
LANGUAGE plpgsql
VOLATILE 
AS $$
DECLARE
    newid integer;
BEGIN
    EXECUTE format('UPDATE %I SET nextid = nextid + 1 RETURNING nextid', idtable) 
            INTO STRICT newid;
    RETURN newid;
END;
$$;

then use DEFAULT nextval_gapless('mytable_gapless_seq') instead of DEFAULT nextval('my_id_seq').

If you don't need a generic plpgsql function that can support multiple different sequence tables, like nextval, you could just write:

CREATE OR REPLACE FUNCTION nextval_gapless_mytable() RETURNS integer
LANGUAGE sql VOLATILE 
AS $$
UPDATE mytable_gapless_seq SET nextid = nextid + 1 RETURNING nextid;
$$;