Martijn van Oosterhout <kleptog@xxxxxxxxx> writes:
> Sure, UNIQUE constraints are not deferrable. With normal constraints
> you can defer the check until the end of transaction and be in an
> inconsistant state for while. However, PostgreSQL doesn't support this
> for uniqueness checks.
Actually, what the spec says is (SQL92 4.10.1)
The checking of a constraint depends on its constraint mode within
the current SQL-transaction. If the constraint mode is immedi-
ate, then the constraint is effectively checked at the end of
each SQL-statement. If the constraint mode is deferred, then the
constraint is effectively checked when the constraint mode is
changed to immediate either explicitly by execution of a <set con-
straints mode statement>, or implicitly at the end of the current
SQL-transaction.
So even for a non-deferred unique constraint, it should be legal to
update multiple rows to new non-conflicting values within a single
UPDATE command. Plus, as Martijn says, we have no support at all
for the defer-to-end-of-transaction case.
We've discussed this before, and I thought it was on the TODO list,
but AFAICS the only entry there is
* Allow DEFERRABLE UNIQUE constraints?
which is misfiled under "Triggers" and doesn't cover the existing
spec violation anyway. Bruce?
regards, tom lane
