Primary Key Update issue ?

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

Primary Key Update issue ?

pafiti

Hello,

 

While doing some testing on a Postgresql database, I encountered a strange behavior which is very simple to reproduce.

I just wanted to know if this is expected behavior or if it should be considered as an issue.

 

The scenario to reproduce it is the following.

 

CREATE TABLE Test ( pKey integer, Val integer, CONSTRAINT PK_Test PRIMARY Key( pKey ) );

 

INSERT INTO Test( pKey, Val ) VALUES ( 1, 1 );

INSERT INTO Test( pKey, Val ) VALUES ( 2, 2 );

 

UPDATE Test SET pKey = pKey + 1;

 

Here is the error that I get.

 

SQL Error [23505]: ERROR: duplicate key value violates unique constraint "pk_test"

  Detail: Key (pkey)=(2) already exists.

 

I was expecting pKey to be incremented for each row, which would still respect the unique constraint….

 

I’m currently using PostgreSQL 11.5 but have the same problem on PostgreSQL 10.3 server.

 

Best Regards,

 

Patrick

 

Reply | Threaded
Open this post in threaded view
|

Sv: Primary Key Update issue ?

Andreas Joseph Krogh-2
På fredag 06. september 2019 kl. 11:06:04, skrev Patrick FICHE <[hidden email]>:

Hello,

 

While doing some testing on a Postgresql database, I encountered a strange behavior which is very simple to reproduce.

I just wanted to know if this is expected behavior or if it should be considered as an issue.

 

The scenario to reproduce it is the following.

 

CREATE TABLE Test ( pKey integer, Val integer, CONSTRAINT PK_Test PRIMARY Key( pKey ) );

 

INSERT INTO Test( pKey, Val ) VALUES ( 1, 1 );

INSERT INTO Test( pKey, Val ) VALUES ( 2, 2 );

 

UPDATE Test SET pKey = pKey + 1;

 

Here is the error that I get.

 

SQL Error [23505]: ERROR: duplicate key value violates unique constraint "pk_test"

  Detail: Key (pkey)=(2) already exists.

 

I was expecting pKey to be incremented for each row, which would still respect the unique constraint….

 

I’m currently using PostgreSQL 11.5 but have the same problem on PostgreSQL 10.3 server.

 

Best Regards, 

 
It works if you add "DEFERRABLE INITIALLY DEFERRED" to the PK:
CREATE TABLE Test ( pKey integer, Val integer, CONSTRAINT PK_Test PRIMARY Key( pKey ) DEFERRABLE INITIALLY DEFERRED );
 
 
andreak@[local]:5432 11.5 test=# CREATE TABLE Test ( pKey integer, Val integer, CONSTRAINT PK_Test PRIMARY Key( pKey ) DEFERRABLE INITIALLY DEFERRED );
CREATE TABLE
andreak@[local]:5432 11.5 test=# INSERT INTO Test( pKey, Val ) VALUES ( 1, 1 );
INSERT 0 1
andreak@[local]:5432 11.5 test=# INSERT INTO Test( pKey, Val ) VALUES ( 2, 2 );
INSERT 0 1
andreak@[local]:5432 11.5 test=# UPDATE Test SET pKey = pKey + 1;
UPDATE 2

 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
 
Reply | Threaded
Open this post in threaded view
|

RE: Primary Key Update issue ?

pafiti

Hi Andreas,

 

Thanks a lot for your answer, which solves this case.

I was still a bit surprised as this is linked to transaction management while I have here a single statement until I saw the Compatibility Remark in documentation : Also, PostgreSQL checks non-deferrable uniqueness constraints immediately, not at end of statement as the standard would suggest.

 

Regards,

 

Patrick Fiche

 

From: Andreas Joseph Krogh <[hidden email]>
Sent: Friday, September 6, 2019 11:17 AM
To: [hidden email]
Subject: Sv: Primary Key Update issue ?

 

På fredag 06. september 2019 kl. 11:06:04, skrev Patrick FICHE <[hidden email]>:

Hello,

 

While doing some testing on a Postgresql database, I encountered a strange behavior which is very simple to reproduce.

I just wanted to know if this is expected behavior or if it should be considered as an issue.

 

The scenario to reproduce it is the following.

 

CREATE TABLE Test ( pKey integer, Val integer, CONSTRAINT PK_Test PRIMARY Key( pKey ) );

 

INSERT INTO Test( pKey, Val ) VALUES ( 1, 1 );

INSERT INTO Test( pKey, Val ) VALUES ( 2, 2 );

 

UPDATE Test SET pKey = pKey + 1;

 

Here is the error that I get.

 

SQL Error [23505]: ERROR: duplicate key value violates unique constraint "pk_test"

  Detail: Key (pkey)=(2) already exists.

 

I was expecting pKey to be incremented for each row, which would still respect the unique constraint….

 

I’m currently using PostgreSQL 11.5 but have the same problem on PostgreSQL 10.3 server.

 

Best Regards, 

 

It works if you add "DEFERRABLE INITIALLY DEFERRED" to the PK:

CREATE TABLE Test ( pKey integer, Val integer, CONSTRAINT PK_Test PRIMARY Key( pKey ) DEFERRABLE INITIALLY DEFERRED );
 

 

andreak@[local]:5432 11.5 test=# CREATE TABLE Test ( pKey integer, Val integer, CONSTRAINT PK_Test PRIMARY Key( pKey ) DEFERRABLE INITIALLY DEFERRED );
CREATE TABLE
andreak@[local]:5432 11.5 test=# INSERT INTO Test( pKey, Val ) VALUES ( 1, 1 );
INSERT 0 1
andreak@[local]:5432 11.5 test=# INSERT INTO Test( pKey, Val ) VALUES ( 2, 2 );
INSERT 0 1
andreak@[local]:5432 11.5 test=# UPDATE Test SET pKey = pKey + 1;
UPDATE 2

 

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

 

Reply | Threaded
Open this post in threaded view
|

RE: Primary Key Update issue ?

Andreas Joseph Krogh-2
På fredag 06. september 2019 kl. 11:25:36, skrev Patrick FICHE <[hidden email]>:

Hi Andreas,

 

Thanks a lot for your answer, which solves this case.

I was still a bit surprised as this is linked to transaction management while I have here a single statement until I saw the Compatibility Remark in documentation : Also, PostgreSQL checks non-deferrable uniqueness constraints immediately, not at end of statement as the standard would suggest.

 
FWIW - PostgreSQL behaves like Oracle in this regard.
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963