> On Sep 10, 2020, at 6:43 AM, Abraham, Danny <danny_abra...@bmc.com> wrote:
>
> Hi,
>
> We have seen several times a situation where a PKEY is compromised and
> duplicate values are created within a table.
>
> This has happened so far on PG 928 on Linux and Windows, and also on PG955 on
> AIX.
>
> We ran massive test on PG10.4 but it has not recreated the problem.
>
> Has anyone faced the same issue? Any known bug?
In my experience, this is usually due to an application writing PK values
rather than allowing a Postgres sequence to generate them, or due to the
application misusing sequences. Postgres sequences are monotonically
increasing, so even if you insert sequence values “out of order”, they won’t
overlap. That’s not to say a bug in Postgres is impossible, but every time I’ve
seen this problem, it’s been my application, not Postgres.
Postgres 10 added new syntax for PK columns "GENERATED ALWAYS AS IDENTITY”
(https://www.postgresql.org/docs/10/sql-createtable.html). Postgres will raise
an exception if an application tries to insert a specific value into a column
defined this way. When we upgraded from 9.6 to 11 we converted all of our PK
columns to use this syntax which added a nice safety net for us.
You’re probably already aware that 9.5.23 is the oldest supported version in
the 9.x series, so the sooner you can upgrade those unsupported 9.x versions
the better. Maybe you’re in the middle of an upgrade already which is why this
came up. :-)
Cheers
Philip
