MIME-Version: 1.0
References: <35fd10ab-5f7f-7a57-9cc5-c03d81476d13@appl-ecosys.com>
In-Reply-To: <35fd10ab-5f7f-7a57-9cc5-c03d81476d13@appl-ecosys.com>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Mon, 10 Jun 2024 16:03:50 -0700
Message-ID: <CAKFQuwboV8qvZrGWb+TOsNVYXzf=j=gE3fYMYFoNg2JT2_0YqA@mail.gmail.com>
Subject: Re: Gaps in PK sequence numbers
To: Rich Shepard <rshepard@appl-ecosys.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000db0dc9061a912b77"
Archived-At: <https://www.postgresql.org/message-id/CAKFQuwboV8qvZrGWb%2BTOsNVYXzf%3Dj%3DgE3fYMYFoNg2JT2_0YqA%40mail.gmail.com>
Precedence: bulk

--000000000000db0dc9061a912b77
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Mon, Jun 10, 2024 at 3:57=E2=80=AFPM Rich Shepard <rshepard@appl-ecosys.=
com>
wrote:

>
> 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.
>

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 J.

--000000000000db0dc9061a912b77
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><div class=3D"gmail_default" style=3D"fon=
t-family:arial,helvetica,sans-serif"><span style=3D"font-family:Arial,Helve=
tica,sans-serif">On Mon, Jun 10, 2024 at 3:57=E2=80=AFPM Rich Shepard &lt;<=
a href=3D"mailto:rshepard@appl-ecosys.com">rshepard@appl-ecosys.com</a>&gt;=
 wrote:</span></div></div><div class=3D"gmail_quote"><blockquote class=3D"g=
mail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204=
,204,204);padding-left:1ex">
<br>
I found a web page that explains how to find the gaps in a sequence, yet I<=
br>
want to understand why nextval() doesn&#39;t begin with the max(FK)+1 value=
.<br></blockquote><div><br></div><div class=3D"gmail_default" style=3D"font=
-family:arial,helvetica,sans-serif">For efficiency the only thing used to d=
etermine the next value of a sequence is the stored value of the last seque=
nce value issued.=C2=A0 Where that value may have been used, in a table as =
a PK or some other purpose, does not enter into it.=C2=A0 Using a sequence =
as a default does indeed become problematic if you don&#39;t use it exclusi=
vely.=C2=A0 If you do use it exclusively usually you just set the last valu=
e to be the maximum needed and use it going forward.=C2=A0 The numbers from=
 deleted rows simply remain missing in the table.</div><div class=3D"gmail_=
default" style=3D"font-family:arial,helvetica,sans-serif"><br></div><div cl=
ass=3D"gmail_default" style=3D"font-family:arial,helvetica,sans-serif">Davi=
d J.</div><div class=3D"gmail_default" style=3D"font-family:arial,helvetica=
,sans-serif"><br></div></div></div>

--000000000000db0dc9061a912b77--