From: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
---|---|
To: | jonathan vanasco <postgres(at)2xlp(dot)com>, pgsql-general general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: inserts failed because of primary key conflict, weird sequence issue. what could cause a reset? |
Date: | 2017-12-13 07:57:35 |
Message-ID: | 1513151855.2545.4.camel@cybertec.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Yogesh Sharma | 2017-12-13 12:42:36 | Re: Size of pg_multixact/members increases 11355 |
Previous Message | Yogesh Sharma | 2017-12-13 03:26:24 | Re: Size of pg_multixact/members increases 11355 |