Search Postgresql Archives

Re: Atomicity?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux