[BUGS] BUG #14596: False primary/unique key constraint violations

The following bug has been logged on the website:

Bug reference:      14596
Logged by:          Rasmus Schultz
Email address:      rasmus@mindplay.dk
PostgreSQL version: 9.5.6
Operating system:   Win10 Pro/64
Description:

Given the following schema:

CREATE TABLE public.test
(
   name character varying(100), 
   index integer, 
   CONSTRAINT unique_index PRIMARY KEY (index)
) 
WITH (
  OIDS = FALSE
);

And the following sample data:

INSERT INTO "test" ("name", "index") VALUES ('A', 0);
INSERT INTO "test" ("name", "index") VALUES ('B', 1);
INSERT INTO "test" ("name", "index") VALUES ('C', 2);

The following query will fail:

UPDATE "test" SET "index" = "index" + 1 WHERE "index" >= 0;

With the following error-message:

ERROR: duplicate key value violates unique constraint "unique_index"
SQL state: 23505
Detail: Key (index)=(1) already exists.

The reported constraint violation is incorrect - the net update does not
produce any violation of the constraint.

Dropping the index and executing the query, then recreating the index,
proves that the query does not in fact lead to a key violation.

The same happens with a non-primary unique constraint.

The same happens even if I wrap the update in a transaction. (which
shouldn't be necessary, given that a single statement should be atomic
either way.)

It looks like constraints are being checked row-by-row while the udpate is
happening?

I was expecting constraints would be checked at the end of an update, such
that an update producing a valid net update would execute fully - the fact
that constraints are checked while the update is still in progress seems
like an implementation detail, and I was not expecting that such a detail
would affect my ability to perform an update with a net valid result.

I was quite surprised by this, as PostgreSQL is generally super "correct"
about things, but in this case I was surprised.

It looks like my only option at this time is to forego any index on this
table?



--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs