Re: nextval() on serial using old, existing value on insert?

From: Keith <keith(at)keithf4(dot)com>
To: Wells Oliver <wells(dot)oliver(at)gmail(dot)com>
Cc: pgsql-admin <pgsql-admin(at)postgresql(dot)org>
Subject: Re: nextval() on serial using old, existing value on insert?
Date: 2023-05-11 04:54:09
Message-ID: CAHw75vtvE1o1YqA65hYUy9y4dV69VAXwMrEORhcK7SLbmE6CZA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

On Thu, May 11, 2023 at 12:19 AM Wells Oliver <wells(dot)oliver(at)gmail(dot)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(dot)oliver(at)gmail(dot)com <wellsoliver(at)gmail(dot)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

In response to

Browse pgsql-admin by date

  From Date Subject
Next Message David G. Johnston 2023-05-11 04:55:12 Re: nextval() on serial using old, existing value on insert?
Previous Message Wells Oliver 2023-05-11 04:18:10 nextval() on serial using old, existing value on insert?