serial in postgres is being increased even though I added on conflict do nothing

Question

I'm using Postgres 9.5 and seeing some wired things here.

I've a cron job running ever 5 mins firing a sql statement that is adding a list of records if not existing.

INSERT INTO     sometable (customer, balance) VALUES     (:customer, :balance) ON CONFLICT (customer) DO NOTHING 

sometable.customer is a primary key (text)

sometable structure is:
id: serial
customer: text
balance: bigint

Now it seems like everytime this job runs, the id field is silently incremented +1. So next time, I really add a field, it is thousands of numbers above my last value. I thought this query checks for conflicts and if so, do nothing but currently it seems like it tries to insert the record, increased the id and then stops.

Any suggestions?

Answer 1

The reason this feels weird to you is that you are thinking of the increment on the counter as part of the insert operation, and therefore the "DO NOTHING" ought to mean "don't increment anything". You're picturing this:

1. Check values to insert against constraint
2. If duplicate detected, abort
3. Increment sequence
4. Insert data

But in fact, the increment has to happen before the insert is attempted. A SERIAL column in Postgres is implemented as a DEFAULT which executes the nextval() function on a bound SEQUENCE. Before the DBMS can do anything with the data, it's got to have a complete set of columns, so the order of operations is like this:

1. Resolve default values, including incrementing the sequence
2. Check values to insert against constraint
3. If duplicate detected, abort
4. Insert data

This can be seen intuitively if the duplicate key is in the autoincrement field itself:

CREATE TABLE foo ( id SERIAL NOT NULL PRIMARY KEY, bar text ); -- Insert row 1 INSERT INTO foo ( bar ) VALUES ( 'test' ); -- Reset the sequence SELECT setval(pg_get_serial_sequence('foo', 'id'), 0, true); -- Attempt to insert row 1 again INSERT INTO foo ( bar ) VALUES ( 'test 2' )      ON CONFLICT (id) DO NOTHING; 

Clearly, this can't know if there's a conflict without incrementing the sequence, so the "do nothing" has to come after that increment.

Answer 2

As already said by @a_horse_with_no_name and @Serge Ballesta serials are always incremented even if INSERT fails.

You can try to "rollback" serial value to maximum id used by changing the corresponding sequence:

SELECT setval('sometable_id_seq', MAX(id), true) FROM sometable;