Thread: inserts failed because of primary key conflict, weird sequence issue. what could cause a reset?

I can't figure out how this happened

I have table where the primary key is a sequence.  I was getting a lot of errors on insertions, and noticed the current
valuewas around 65k, but the max id in the database was around 160k.  everything works now that i've restarted it, but
Ican't find any record of a restart sequence accidentally being put in. 

the only thing I can think of, is the sequence went up several hundred billion and restarted from 0 when a drive
crashed.but that seems incredibly unlikely given the last known actual max id. 

does anyone have an idea what could cause this?  pg 9.6.6 on ubuntu

On Tue, 2017-12-12 at 18:10 -0500, jonathan vanasco wrote:
> I have table where the primary key is a sequence.  I was getting a lot of errors on insertions,
> and noticed the current value was around 65k, but the max id in the database was around 160k.
> everything works now that i've restarted it, but I can't find any record of a
> restart sequence accidentally being put in.
> 
> the only thing I can think of, is the sequence went up several hundred billion and restarted
> from 0 when a drive crashed. but that seems incredibly unlikely given the last known actual max id.
> 
> does anyone have an idea what could cause this?  pg 9.6.6 on ubuntu

There are only 2 ways:
Either rows were inserted with explizit values for the serial column,
or the sequence counter was reset.

The latter is hard to observe in the log, since it will only be logged
when all SELECT statements are logged.

One idea to prevent that is to revoke the UPDATE privilege on the sequence.
You can revoke that privilege from the owner as well.  Nothing keeps the
owner from granting the privilege to himself again, but until that is done,
every attempt to reset the sequence will lead to an error.

Yours,
Laurenz Albe