Re: Atomicity?

[Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>]

Martijn van Oosterhout <kleptog(at)svana(dot)org> 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