Обсуждение: Gaps in PK sequence numbers

Over the years I've deleted rows from tables leaving gaps in the PK sequence
numbers. I've now learned that using nextval('sequencename') finds those
gaps and tells me that the value after the gap is already assigned.

I found a web page that explains how to find the gaps in a sequence, yet I
want to understand why nextval() doesn't begin with the max(FK)+1 value.

When I tried inserting new rows in the companies table psql told me that PK
value 2310 already existed. Selecting max(PK) returned 2341. When entering
multiple new rows is there a way to ignore gaps?

TIA,

Rich

> On Jun 10, 2024, at 15:57, Rich Shepard <rshepard@appl-ecosys.com> wrote:
> When I tried inserting new rows in the companies table psql told me that PK
> value 2310 already existed. Selecting max(PK) returned 2341. When entering
> multiple new rows is there a way to ignore gaps?

Strictly speaking, the sequence underlying nextval() has no idea what primary keys are or are not in use.  It's just a
transaction-ignoringcounter that increases with each nextval() call.  The only reason that you'd get duplicate key
errorsin this case are: 

1. The sequence was reset to a different, lower value.
2. Rows were inserted that didn't use the sequence to select a primary key.

On Mon, 10 Jun 2024, Christophe Pettus wrote:

> Strictly speaking, the sequence underlying nextval() has no idea what
> primary keys are or are not in use. It's just a transaction-ignoring
> counter that increases with each nextval() call. The only reason that
> you'd get duplicate key errors in this case are:
>
> 1. The sequence was reset to a different, lower value.
> 2. Rows were inserted that didn't use the sequence to select a primary key.

Thanks, Christophe. Is there a way to reset the sequence to the maximum
number +1? I don't recall seeing this in the postgres docs but will look
again.

Regards,

Rich

On Mon, 10 Jun 2024, David G. Johnston wrote:

> For efficiency the only thing used to determine the next value of a
> sequence is the stored value of the last sequence value issued. Where that
> value may have been used, in a table as a PK or some other purpose, does
> not enter into it. Using a sequence as a default does indeed become
> problematic if you don't use it exclusively. If you do use it exclusively
> usually you just set the last value to be the maximum needed and use it
> going forward. The numbers from deleted rows simply remain missing in the
> table.

David,

Thanks for the explanation. I had entered PKs without using the sequence.

Regards,

Rich

> On Jun 10, 2024, at 18:10, Rich Shepard <rshepard@appl-ecosys.com> wrote:
> Thanks, Christophe. Is there a way to reset the sequence to the maximum
> number +1? I don't recall seeing this in the postgres docs but will look
> again.

The sequence functions are documented here:

    https://www.postgresql.org/docs/current/functions-sequence.html

setval is the function you want.  You can use a SELECT so you don't have to copy values around:

    select setval('t_pk_seq', (select max(pk) from t));

That form of setval() sets the sequence so that the next value will be one more than the value you set it to, so you
don'thave to do max()+1 there. 

Is there a way to reset the sequence to the maximum
number +1? I don't recall seeing this in the postgres docs but will look
again.

On Mon, 10 Jun 2024, Christophe Pettus wrote:

> The sequence functions are documented here:
>     https://www.postgresql.org/docs/current/functions-sequence.html
> setval is the function you want. You can use a SELECT so you don't have to
> copy values around:
>     select setval('t_pk_seq', (select max(pk) from t));
> That form of setval() sets the sequence so that the next value will be one
> more than the value you set it to, so you don't have to do max()+1 there.

Thanks again, Christophe.

Regards,

Rich