Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3774.600.62\))
Subject: Re: Reset sequence to current maximum value of rows
From: Alban Hertroys <haramrae@gmail.com>
In-Reply-To: <b6b331ff-913d-d5d8-905-aba54e90c99f@appl-ecosys.com>
Date: Fri, 14 Jun 2024 09:08:51 +0200
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <C077D8F1-3A32-493C-95F8-53C21130E96A@gmail.com>
References: <387fa6c2-5de-f24-d522-46282597703@appl-ecosys.com>
 <CAKFQuwba=vb16vR47W0oo3SirkJ+UqLEg5j9-fqe+vm=0Q7_qw@mail.gmail.com>
 <1b139d9a-b95f-f7c8-52fb-9d7442c69ad9@appl-ecosys.com>
 <CAKFQuwY4PXdUYWxWzZ+idD64GLA2vrKf_UDcSPf=5VuYSDNRUA@mail.gmail.com>
 <97d69b9-48de-3bd4-95d-df5614ff44a@appl-ecosys.com>
 <4f319e27-0e54-40dd-888f-1e4c856a1b02@aklaver.com>
 <b6b331ff-913d-d5d8-905-aba54e90c99f@appl-ecosys.com>
To: Rich Shepard <rshepard@appl-ecosys.com>
Archived-At: <https://www.postgresql.org/message-id/C077D8F1-3A32-493C-95F8-53C21130E96A%40gmail.com>
Precedence: bulk

> company_nbr |                  company_name =
-------------+-------------------------------------------------
>           1 | Markowitz Herbold PC
>           2 | Markowitz Herbold PC
>           3 | Markowitz Herbold PC
>           4 | Markowitz Herbold PC
>           5 | Markowitz Herbold PC
>           6 | Markowitz Herbold PC
>           7 | Markowitz Herbold PC
>           8 | Markowitz Herbold PC
>           9 | Markowitz Herbold PC
>          10 | Markowitz Herbold PC
>          11 | Markowitz Herbold PC
>          12 | Markowitz Herbold PC
>          13 | Markowitz Herbold PC
>          14 | Markowitz Herbold PC
>          15 | Markowitz Herbold PC
>          16 | Markowitz Herbold PC
>          17 | Markowitz Herbold PC
>          18 | Markowitz Herbold PC
>          19 | Markowitz Herbold PC
>          20 | Markowitz Herbold PC
>          22 | Markowitz Herbold PC
>          23 | Markowitz Herbold PC
> --More--

Did those rows contain these values in some earlier transaction in your =
data-entry process perhaps? I=E2=80=99m thinking that perhaps you =
overwrote them in a later transaction with the correct values for the =
names, but forgot to commit that transaction?

It=E2=80=99s either that, or you did run an UPDATE statement against =
those rows without specifying a WHERE-clause, as others already =
suggested as a likely cause.


I think we can rule out the possibility of index corruption (a very rare =
occurrence, usually caused by factors external to PG) for your case. A =
data-set this limited would most likely result in an execution plan =
using a sequential scan instead of an index scan (an EXPLAIN ANALYZE of =
above select statement would show proof).

> It might be quicker for me to restore the entire database from that =
backup
> and then insert all new table rows since I have saved all the scripts.

If you end up in the same situation again after doing that, then you =
know at least it=E2=80=99s repeatable and can analyse how you got there.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.