Re: RE: [GENERAL] update inside transaction violates unique constraint?

"Mikheev, Vadim" wrote:

I get an error (which is good). But, if I do

#BEGIN;
#SELECT * FROM name_and_ip WHERE name = 'foo' OR name = 'bar' FOR
UPDATE;
#UPDATE name_and_ip SET ip = '192.168.186.249' where name = 'foo';
UPDATE 1
#COMMIT;
COMMIT

Btree doesn't take into account that tuple was just marked for update
but still alive. Seems it was handled properly in 6.5.X ?

Nope. It has been broken a long time...

hannu=> select version();
version
-------------------------------------------------------------------
PostgreSQL 6.5.3 on i586-pc-linux-gnu, compiled by gcc egcs-2.91.66
(1 row)

hannu=> create table T(i int);
CREATE
hannu=> create unique index TUI on T(I);
CREATE
hannu=> insert into T values(1);
INSERT 109150 1
hannu=> insert into T values(2);
INSERT 109151 1
hannu=> begin;
BEGIN
hannu=> select * from T where I in (1,2)for update;
i
-
1
2
(2 rows)

hannu=> update T set I=1 where I=2;
UPDATE 1
hannu=> commit;
END
hannu=> select * from T;
i
-
1
1
(2 rows)