PKEY getting corrupted

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

PKEY getting corrupted

danny_abraham@bmc.com
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?

Thanks

Danny


Reply | Threaded
Open this post in threaded view
|

Re: PKEY getting corrupted

Philip Semanchuk-2


> On Sep 10, 2020, at 6:43 AM, Abraham, Danny <[hidden email]> 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

Reply | Threaded
Open this post in threaded view
|

Re: PKEY getting corrupted

Adrian Klaver-4
In reply to this post by danny_abraham@bmc.com
On 9/10/20 3:43 AM, Abraham, Danny wrote:
> Hi,
>
> We have seen several times a situation where a PKEY is compromised and duplicate values are created within a table.

Table definition?

The situation is?

Postgres log entries from the error?


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

The test would be?

>
> Has anyone faced the same issue? Any known bug?
>
> Thanks
>
> Danny
>
>


--
Adrian Klaver
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: PKEY getting corrupted

Alvaro Herrera-9
In reply to this post by danny_abraham@bmc.com
Danny,

On 2020-Sep-10, Abraham, Danny wrote:

> We have seen several times a situation where a PKEY is compromised and
> duplicate values are created within a table.

Yeah, this is not unheard of.

> This has happened so far on PG 928 on Linux and Windows, and also on
> PG955 on AIX.

You do realize that 9.2.8 is not only missing the many bugfixes that
happened until 9.2.24 was released in November 2017, but also any
bugfixes that were done after that branch was abandoned?

9.5.5 sounds more interesting as a target, but it's still missing all
updates until 9.5.23 released last month.

--
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services