Every time I do an INSERT or UPSERT (ON CONFLICT UPDATE), the increments column on each table increments by the number of updates that came before it.
For instance, if I have this table:
id int4
title text
description text
updated_at timestamp
created_at timestamp
And then run these queries:
INSERT INTO notifications (title, description) VALUES ('something', 'whatever'); // Generates increments ID=1
UPDATE notifications title='something else' WHERE id = 1; // Repeat this query 20 times with different values.
INSERT INTO notifications (title, description) VALUES ('something more', 'whatever again'); // Generates increments ID=22
This is a pretty big issue. The script we are running processes 100,000+ notifications every day. This can create gaps between each insert on the order of 10,000, so we might start off with 100 rows but by the time we reach 1,000 rows we have an auto-incremented primary key ID value over 100000 for that last row.
We will quickly run out of auto-increment values on our tables if this continues.
Is our PostgreSQL server misconfigured? Using Postgres 9.5.3.
I'm using Eloquent Schema Builder (e.g. $table->increments('id')
) to create the table and I don't know if that has something to do with it.
A sequence will be incremented whenever an insertion is attempted regardless of its success. A simple update
(as in your example) will not increment it but an insert on conflict update
will since the insert
is tried before the update
.
One solution is to change the id
to bigint
. Another is not to use a sequence and manage it yourself. And another is to do a manual upsert:
with s as (
select id
from notifications
where title = 'something'
), i as (
insert into notifications (title, description)
select 'something', 'whatever'
where not exists (select 1 from s)
)
update notifications
set title = 'something else'
where id = (select id from s)
This supposes title
is unique.
You can reset auto increment column to max inserted value by run this command before insert command:
SELECT setval('notifications_id_seq', MAX(id)) FROM notifications;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With