pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feednextval() on serial using old, existing value on insert?
10+ messages / 6 participants
[nested] [flat]
* nextval() on serial using old, existing value on insert?
@ 2023-05-11 04:18 Wells Oliver <wells.oliver@gmail.com>
0 siblings, 2 replies; 10+ messages in thread
From: Wells Oliver @ 2023-05-11 04:18 UTC (permalink / raw)
To: pgsql-admin
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 <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: nextval() on serial using old, existing value on insert?
@ 2023-05-11 04:54 Keith <keith@keithf4.com>
parent: Wells Oliver <wells.oliver@gmail.com>
1 sibling, 0 replies; 10+ messages in thread
From: Keith @ 2023-05-11 04:54 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; +Cc: pgsql-admin
On Thu, May 11, 2023 at 12:19 AM 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?
>
> --
> Wells Oliver
> wells.oliver@gmail.com <wellsoliver@gmail.com>
>
Seeing the complete schema of your table would help with clarity. Can you
share the full \d+ output of it if the below doesn't answer your question?
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.
If you need to enforce that the sequence values are the only ones allowed
for this column, I would recommend looking into the IDENTITY property for
table columns. This allows you to enforce that the column's value can only
be obtained from a sequence.
Adjusted example from the documentation (
https://www.postgresql.org/docs/15/sql-createtable.html) to enforce
sequence only values (changed GENERATED BY DEFAULT to GENERATED ALWAYS)
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
Hope that helps!
Keith
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: nextval() on serial using old, existing value on insert?
@ 2023-05-11 04:55 David G. Johnston <david.g.johnston@gmail.com>
parent: Wells Oliver <wells.oliver@gmail.com>
1 sibling, 1 reply; 10+ messages in thread
From: David G. Johnston @ 2023-05-11 04:55 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; +Cc: pgsql-admin
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: nextval() on serial using old, existing value on insert?
@ 2023-05-11 05:02 Wells Oliver <wells.oliver@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Wells Oliver @ 2023-05-11 05:02 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-admin
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 <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: nextval() on serial using old, existing value on insert?
@ 2023-05-11 05:06 Wells Oliver <wells.oliver@gmail.com>
parent: Wells Oliver <wells.oliver@gmail.com>
0 siblings, 2 replies; 10+ messages in thread
From: Wells Oliver @ 2023-05-11 05:06 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-admin
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 <wellsoliver@gmail.com>
>
--
Wells Oliver
wells.oliver@gmail.com <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: nextval() on serial using old, existing value on insert?
@ 2023-05-11 05:09 Keith <keith@keithf4.com>
parent: Wells Oliver <wells.oliver@gmail.com>
1 sibling, 1 reply; 10+ messages in thread
From: Keith @ 2023-05-11 05:09 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-admin
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.com <wellsoliver@gmail.com>
>
That will make it go away for the values currently in the table, but does
nothing to prevent it happening again in the future.
Keith
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: nextval() on serial using old, existing value on insert?
@ 2023-05-11 05:10 Wells Oliver <wells.oliver@gmail.com>
parent: Keith <keith@keithf4.com>
0 siblings, 0 replies; 10+ messages in thread
From: Wells Oliver @ 2023-05-11 05:10 UTC (permalink / raw)
To: Keith <keith@keithf4.com>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-admin
Agreed, it does not stop some human from going in there and putting in
higher values than the sequence, but it's good to realize that's what
happened, and we have workflows/restrictions in place where it's unlikely
to happen again.
On Wed, May 10, 2023 at 10:09 PM Keith <keith@keithf4.com> wrote:
>
>
> 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.com <wellsoliver@gmail.com>
>>
>
>
> That 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
wells.oliver@gmail.com <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: nextval() on serial using old, existing value on insert?
@ 2023-05-11 12:38 hubert depesz lubaczewski <depesz@depesz.com>
parent: Wells Oliver <wells.oliver@gmail.com>
1 sibling, 1 reply; 10+ messages in thread
From: hubert depesz lubaczewski @ 2023-05-11 12:38 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: nextval() on serial using old, existing value on insert?
@ 2023-05-11 15:09 Holger Jakobs <holger@jakobs.com>
parent: hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 1 reply; 10+ messages in thread
From: Holger Jakobs @ 2023-05-11 15:09 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
Attachments:
[application/pgp-signature] OpenPGP_signature (202B, ../../8234056e-9d60-2a41-1701-1afb08e330db@jakobs.com/2-OpenPGP_signature)
download
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: nextval() on serial using old, existing value on insert?
@ 2023-05-11 15:46 Doug Reynolds <mav@wastegate.net>
parent: Holger Jakobs <holger@jakobs.com>
0 siblings, 0 replies; 10+ messages in thread
From: Doug Reynolds @ 2023-05-11 15:46 UTC (permalink / raw)
To: Holger Jakobs <holger@jakobs.com>; +Cc: pgsql-admin@lists.postgresql.org
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
>
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2023-05-11 15:46 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-05-11 04:18 nextval() on serial using old, existing value on insert? Wells Oliver <wells.oliver@gmail.com>
2023-05-11 04:54 ` Keith <keith@keithf4.com>
2023-05-11 04:55 ` David G. Johnston <david.g.johnston@gmail.com>
2023-05-11 05:02 ` Wells Oliver <wells.oliver@gmail.com>
2023-05-11 05:06 ` Wells Oliver <wells.oliver@gmail.com>
2023-05-11 05:09 ` Keith <keith@keithf4.com>
2023-05-11 05:10 ` Wells Oliver <wells.oliver@gmail.com>
2023-05-11 12:38 ` hubert depesz lubaczewski <depesz@depesz.com>
2023-05-11 15:09 ` Holger Jakobs <holger@jakobs.com>
2023-05-11 15:46 ` Doug Reynolds <mav@wastegate.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox