Re: Atomicity?

Tom Lane wrote:
> 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?

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(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +