Re: Atomicity?

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