Tom Lane wrote: > 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? TODO updated: * Allow DEFERRABLE and end-of-statement UNIQUE constraints? This would allow UPDATE tab SET col = col + 1 to work if col has a unique index. Currently, uniqueness checks are done while the command is being executed, rather than at the end of the statement or transaction. and moved to "referential integrity" section. -- Bruce Momjian bruce@xxxxxxxxxx EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
