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
rasmus@mindplay.dk writes:
It looks like constraints are being checked row-by-row while the udpate is
happening?
This is documented somewhere ... ah, here, in the COMPATIBILITY section of
the CREATE TABLE reference page:
Non-deferred Uniqueness Constraints
When a UNIQUE or PRIMARY KEY constraint is not deferrable, PostgreSQL
checks for uniqueness immediately whenever a row is inserted or
modified. The SQL standard says that uniqueness should be enforced only
at the end of the statement; this makes a difference when, for example,
a single command updates multiple key values. To obtain
standard-compliant behavior, declare the constraint as DEFERRABLE but
not deferred (i.e., INITIALLY IMMEDIATE). Be aware that this can be
significantly slower than immediate uniqueness checking.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
On Wed, Mar 22, 2017 at 5:30 AM, <rasmus@mindplay.dk> wrote:
CREATE TABLE public.test
(
name character varying(100),
index integer,
CONSTRAINT unique_index PRIMARY KEY (index)
);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;
Try the following with a deferred constraint:
CREATE TABLE public.test
(
name character varying(100),
index integer,
CONSTRAINT unique_index PRIMARY KEY (index) DEFERRABLE INITIALLY DEFERRED
);
This behaviour is documented at
https://www.postgresql.org/docs/current/static/sql-createtable.html
On Wed, Mar 22, 2017 at 7:45 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
rasmus@mindplay.dk writes:
It looks like constraints are being checked row-by-row while the udpate
is
happening?
This is documented somewhere ... ah, here, in the COMPATIBILITY section of
the CREATE TABLE reference page:Non-deferred Uniqueness Constraints
When a UNIQUE or PRIMARY KEY constraint is not deferrable, PostgreSQL
checks for uniqueness immediately whenever a row is inserted or
modified. The SQL standard says that uniqueness should be enforced only
at the end of the statement; this makes a difference when, for example,
a single command updates multiple key values. To obtain
standard-compliant behavior, declare the constraint as DEFERRABLE but
not deferred (i.e., INITIALLY IMMEDIATE). Be aware that this can be
significantly slower than immediate uniqueness checking.
"SET CONSTRAINTS" is also required if using the standard behavior due to
the "initially immediate" specification.
The need for foresight is the only troubling piece of all of this. Given
that "update tbl set pk = pk + 1;" is so rare most people would not think
to define their uniqueness constraints with deferrability. In the rare
case that you then need the behavior you are forced to drop and recreate
the constraint and backing index because ALTER TABLE ... ALTER CONSTRAINT
can only be used on foreign key constraints (which means exclusion
constraints are also problematic).
Given that the default SET CONSTRAINT behavior is IMMEDIATE, and that
triggers are defined DEFERRABLE, what harm would there be to default to the
standard mandated behavior noted above?
You can add a deferrable constraint to a pre-existing unique index which
suggests that if changing the default is not desirable someone motivated
enough could devise a way to "detach the unique index from the constraint,
drop/update the constraint, then add/re-attach the constraint to the index"
- or, more simply put, make alter table ... alter constraint work when
targeting pk/unique constraints. This might extend to exclusion
constraints too...
David J.
Thanks for the detailed replies, folks!
I had no idea "deferrable" was even a thing.
Well, the default behavior is still surprising, I think - and it sounds
like this may deviate from the standard behavior?
If so, maybe a future release could align better with the standard behavior
on this point - even if this has performance implications, in my opinion,
fewer surprises is better; someone could of course still optimize by using
NOT DEFERRED.
I guess this would be a breaking change however?
On Wed, Mar 22, 2017 at 4:31 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:
Show quoted text
On Wed, Mar 22, 2017 at 7:45 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
rasmus@mindplay.dk writes:
It looks like constraints are being checked row-by-row while the udpate
is
happening?
This is documented somewhere ... ah, here, in the COMPATIBILITY section of
the CREATE TABLE reference page:Non-deferred Uniqueness Constraints
When a UNIQUE or PRIMARY KEY constraint is not deferrable, PostgreSQL
checks for uniqueness immediately whenever a row is inserted or
modified. The SQL standard says that uniqueness should be enforced only
at the end of the statement; this makes a difference when, for example,
a single command updates multiple key values. To obtain
standard-compliant behavior, declare the constraint as DEFERRABLE but
not deferred (i.e., INITIALLY IMMEDIATE). Be aware that this can be
significantly slower than immediate uniqueness checking."SET CONSTRAINTS" is also required if using the standard behavior due to
the "initially immediate" specification.The need for foresight is the only troubling piece of all of this.
Given that "update tbl set pk = pk + 1;" is so rare most people would not
think to define their uniqueness constraints with deferrability. In the
rare case that you then need the behavior you are forced to drop and
recreate the constraint and backing index because ALTER TABLE ... ALTER
CONSTRAINT can only be used on foreign key constraints (which means
exclusion constraints are also problematic).Given that the default SET CONSTRAINT behavior is IMMEDIATE, and that
triggers are defined DEFERRABLE, what harm would there be to default to the
standard mandated behavior noted above?You can add a deferrable constraint to a pre-existing unique index which
suggests that if changing the default is not desirable someone motivated
enough could devise a way to "detach the unique index from the constraint,
drop/update the constraint, then add/re-attach the constraint to the index"
- or, more simply put, make alter table ... alter constraint work when
targeting pk/unique constraints. This might extend to exclusion
constraints too...David J.
"David G. Johnston" <david.g.johnston@gmail.com> writes:
On Wed, Mar 22, 2017 at 7:45 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
... Be aware that this can be
significantly slower than immediate uniqueness checking.
Given that the default SET CONSTRAINT behavior is IMMEDIATE, and that
triggers are defined DEFERRABLE, what harm would there be to default to the
standard mandated behavior noted above?
The performance hit is one very large problem. Another is that we don't
support using deferrable indexes for purposes such as foreign keys,
which means that
create table x (f1 int primary key);
create table y (f1 int references x);
would fail if "primary key" defaulted to meaning "deferrable". So the
standards noncompliance would just move somewhere else.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
On Wed, Mar 22, 2017 at 8:41 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"David G. Johnston" <david.g.johnston@gmail.com> writes:
On Wed, Mar 22, 2017 at 7:45 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
... Be aware that this can be
significantly slower than immediate uniqueness checking.Given that the default SET CONSTRAINT behavior is IMMEDIATE, and that
triggers are defined DEFERRABLE, what harm would there be to default tothe
standard mandated behavior noted above?
The performance hit is one very large problem.
I wasn't expecting a noticeable performance hit unless actual deferred
behavior was requested for the transaction...
Another is that we don't
support using deferrable indexes for purposes such as foreign keys,
which means thatcreate table x (f1 int primary key);
create table y (f1 int references x);would fail if "primary key" defaulted to meaning "deferrable". So the
standards noncompliance would just move somewhere else.
Interesting...I've only resorted to using this once in an ETL scenario
(recently, which is why the alter table annoyance was on my mind) and the
particular implementation didn't require a FK to point back to the
problematic PK.
Compliance doesn't really move, though, we are just non-compliant twice
right now - presuming that the standard doesn't allow for FK to only target
non-deferrable PKs. While this is noted in the description of FK in CREATE
TABLE it isn't noted in the Compatibility section. Only the default
definition of PK deferrability is.
This does seem like a show-stopper for any changes to the defaults in this
area. It doesn't preclude making changing deferrabiltiy easier (i.e., not
involving drop index) for those cases when it is the only answer. Failing
the alter table if FK constraints exist would be expected.
David J.