Hello,
I met the following problem when i am using PostgreSQL as a backend database:
I have a table "tabrel(a int primary key, b int)", and now there are for example 100 records with column 'a' be from 1 to 100. When i'm going to add one to each 'a' and intended to get 'a' varing from 2 to 101, i use SQL query as "update tabrel set a=a+1" , but i got " ERROR: duplicate key violates unique constraint tabrel_a_key".
In my experience with using Oracle, SQL Server and Mysql, such SQL should be executed succussfully. After i met this problem, i turned back to some textbook about database transaction and i got information like this "it is ok to have some inconsistent state during one transaction, only if it is consistent after the transaction is finished succesfully."
And now, i'm wondering what's the problem with this SQL query? How 'update' is executed in the backend?
I'm using PostgreSQL 8.1.4.
Thanks
Shawn Gao
2006-08-22