Anoo Sivadasan Pillai wrote:
> Even though many of the list members of pgsql-general@postgresql.org
> suggest that the following is an expected behaviour, my experience in
> other databases doesn't permit me accept it as such. I am putting this
> for the kind consideration of this list
I think it's more of a "known limitation" than anything else.
> Description : I have two tables with the same data , While I issue an
> update command to increment the value of a unique field by 1, the
> statement fails in one table and will succeed in the other table.
> Following is the script to reproduce the behaviour.
AFAIK (and I'm not a developer) the problem is in two parts:
1. The only way to enforce UNIQUE at the moment is via a unique index.
2. A unique index enforces its requirements immediately.
The reasons why it's not been addressed yet are:
1. Most unique constraints are on keys which aren't updated, so many
people never have this problem.
2. It's quite a bit of work to solve.
3. There is a work-around (x=-x; x=-x + 1)
It's already on the TODO: http://www.postgresql.org/docs/faqs.TODO.html
Search for "Allow DEFERRABLE and end-of-statement UNIQUE constraints"
If you can program in "C" or can fund someone who can, I'm sure people
would like to see it fixed for version 8.4. Don't underestimate the work
involved though.
HTH
-- Richard Huxton Archonet Ltd