Thread: [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
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
);
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
Re: [BUGS] BUG #14596: False primary/unique key constraint violations
From
"David G. Johnston"
Date:
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:
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
Re: [BUGS] BUG #14596: False primary/unique key constraint violations
From
"David G. Johnston"
Date:
"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.
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 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.
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.