Thread: nextval() on serial using old, existing value on insert?
common_key | integer | | not null | nextval('alias.identity_common_key_seq'::regclass) | plain
wells.oliver@gmail.com
I have a simple table with a given column defined like so:common_key | integer | | not null | nextval('alias.identity_common_key_seq'::regclass) | plain
Very very very infrequently, on an INSERT where this column is not specified, this column will be assigned a value that already exists in the table, versus the next presumably unused value in the sequence. I cannot figure this out. Is there any reason why this might be the case?--Wells Oliver
wells.oliver@gmail.com
When using a standard sequence as a default value, unless you have some other restrictions on the table, someone could still manually insert a value into the table that is ahead of the current sequence value. Then when someone tries to do an insert without specifying the column, it tries to use the next available value which eventually hits the value someone else inserted. Also assuming you have some sort of unique index on this column, that would then cause a constraint violation.
CREATE TABLE distributors ( did integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY, name varchar(40) NOT NULL CHECK (name <> '') );
\d+ distributors
Table "public.distributors"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+-----------------------+-----------+----------+------------------------------+----------+-------------+--------------+-------------
did | integer | | not null | generated always as identity | plain | | |
name | character varying(40) | | not null | | extended | | |
Indexes:
"distributors_pkey" PRIMARY KEY, btree (did)
Check constraints:
"distributors_name_check" CHECK (name::text <> ''::text)
Access method: heap
On Wednesday, May 10, 2023, Wells Oliver <wells.oliver@gmail.com> wrote:
I have a simple table with a given column defined like so:common_key | integer | | not null | nextval('alias.identity_
common_key_seq'::regclass) | plain Very very very infrequently, on an INSERT where this column is not specified, this column will be assigned a value that already exists in the table, versus the next presumably unused value in the sequence. I cannot figure this out. Is there any reason why this might be the case?
On Wednesday, May 10, 2023, Wells Oliver <wells.oliver@gmail.com> wrote:I have a simple table with a given column defined like so:common_key | integer | | not null | nextval('alias.identity_common_key_seq'::regclass) | plain
Very very very infrequently, on an INSERT where this column is not specified, this column will be assigned a value that already exists in the table, versus the next presumably unused value in the sequence. I cannot figure this out. Is there any reason why this might be the case?Most likely someone inserted data without using the sequence and eventually the sequence catches up with that previously inserted data.David J.
wells.oliver@gmail.com
Ah, I think that must be it-- there are 200 some rows where manually supplied values for that common_key column are higher than the nextval() on the serial. So eventually they might be "re-used".On Wed, May 10, 2023 at 9:55 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Wednesday, May 10, 2023, Wells Oliver <wells.oliver@gmail.com> wrote:I have a simple table with a given column defined like so:common_key | integer | | not null | nextval('alias.identity_common_key_seq'::regclass) | plain
Very very very infrequently, on an INSERT where this column is not specified, this column will be assigned a value that already exists in the table, versus the next presumably unused value in the sequence. I cannot figure this out. Is there any reason why this might be the case?Most likely someone inserted data without using the sequence and eventually the sequence catches up with that previously inserted data.David J.--Wells Oliver
wells.oliver@gmail.com
wells.oliver@gmail.com
As a follow up, I've selected max(common_key) from the table and setval()'ed on the sequence to that +1 and I think that should make this go away. Any reason why that's insane?On Wed, May 10, 2023 at 10:02 PM Wells Oliver <wells.oliver@gmail.com> wrote:Ah, I think that must be it-- there are 200 some rows where manually supplied values for that common_key column are higher than the nextval() on the serial. So eventually they might be "re-used".On Wed, May 10, 2023 at 9:55 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Wednesday, May 10, 2023, Wells Oliver <wells.oliver@gmail.com> wrote:I have a simple table with a given column defined like so:common_key | integer | | not null | nextval('alias.identity_common_key_seq'::regclass) | plain
Very very very infrequently, on an INSERT where this column is not specified, this column will be assigned a value that already exists in the table, versus the next presumably unused value in the sequence. I cannot figure this out. Is there any reason why this might be the case?Most likely someone inserted data without using the sequence and eventually the sequence catches up with that previously inserted data.David J.--Wells Oliver
wells.oliver@gmail.com
On Thu, May 11, 2023 at 1:07 AM Wells Oliver <wells.oliver@gmail.com> wrote:As a follow up, I've selected max(common_key) from the table and setval()'ed on the sequence to that +1 and I think that should make this go away. Any reason why that's insane?On Wed, May 10, 2023 at 10:02 PM Wells Oliver <wells.oliver@gmail.com> wrote:Ah, I think that must be it-- there are 200 some rows where manually supplied values for that common_key column are higher than the nextval() on the serial. So eventually they might be "re-used".On Wed, May 10, 2023 at 9:55 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Wednesday, May 10, 2023, Wells Oliver <wells.oliver@gmail.com> wrote:I have a simple table with a given column defined like so:common_key | integer | | not null | nextval('alias.identity_common_key_seq'::regclass) | plain
Very very very infrequently, on an INSERT where this column is not specified, this column will be assigned a value that already exists in the table, versus the next presumably unused value in the sequence. I cannot figure this out. Is there any reason why this might be the case?Most likely someone inserted data without using the sequence and eventually the sequence catches up with that previously inserted data.David J.--Wells Oliver
wells.oliver@gmail.comThat will make it go away for the values currently in the table, but does nothing to prevent it happening again in the future.Keith
wells.oliver@gmail.com
On Wed, May 10, 2023 at 10:06:28PM -0700, Wells Oliver wrote: > As a follow up, I've selected max(common_key) from the table and > setval()'ed on the sequence to that +1 and I think that should make this go > away. Any reason why that's insane? If you're on pg 10 or newer, you can make the id to be "generated always as identity" which will make it impossible to provide id value from user, thus making the problem impossible to happen. Best regards, depesz
Am 11.05.23 um 14:38 schrieb hubert depesz lubaczewski: > On Wed, May 10, 2023 at 10:06:28PM -0700, Wells Oliver wrote: >> As a follow up, I've selected max(common_key) from the table and >> setval()'ed on the sequence to that +1 and I think that should make this go >> away. Any reason why that's insane? > If you're on pg 10 or newer, you can make the id to be "generated always > as identity" which will make it impossible to provide id value from > user, thus making the problem impossible to happen. > > Best regards, > > depesz Impossible to happen by chance. A malicious user could always use OVERRIDING SYSTEM VALUE clause. Kind Regards, Holger -- Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
Attachment
If this is a concern, you could use a trigger to ignore the user-provided value or fetch from the sequence. > On May 11, 2023, at 11:10 AM, Holger Jakobs <holger@jakobs.com> wrote: > > Am 11.05.23 um 14:38 schrieb hubert depesz lubaczewski: >>> On Wed, May 10, 2023 at 10:06:28PM -0700, Wells Oliver wrote: >>> As a follow up, I've selected max(common_key) from the table and >>> setval()'ed on the sequence to that +1 and I think that should make this go >>> away. Any reason why that's insane? >> If you're on pg 10 or newer, you can make the id to be "generated always >> as identity" which will make it impossible to provide id value from >> user, thus making the problem impossible to happen. >> >> Best regards, >> >> depesz > > Impossible to happen by chance. A malicious user could always use OVERRIDING SYSTEM VALUE clause. > > Kind Regards, > > Holger > > -- > Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012 >