PostgreSQL seems to maintance the unique index when updating each row.
If the insert sequence is 1, 2, 3, 4, 5, when doing UPDATE testtabelle SET c001 = c001 - 1
It happens to process rows 1, 2, 3, 4, 5 in the same order as you insert.
Thus we see the UPDATE finished successfully. But, if we do UPDATE testtabelle SET c001 = c001 + 1
instead, it will fail. Still a defect in 7.4.
"Hans-J�rgen Sch�nig" <postgres@cybertec.at>
> I have seen that a bug related to duplicated keys is in 7.4rc2. As far
> as I have seen a bug like that has already been discovered during the
> 7.3 era. Is this bug going to be fixed?
>
> Here s the description:
>
> DROP TABLE public.testtabelle;
>
> begin;
>
> CREATE TABLE public.testtabelle
> (
> c000 varchar(20),
> c001 int4 NOT NULL
> ) WITH OIDS;
>
> create unique index prim_index_testtabelle on public.testtabelle
> (c001);
>
> delete from public.testtabelle;
>
> insert into public.testtabelle values ('a', 1);
> insert into public.testtabelle values ('b', 2);
> insert into public.testtabelle values ('c', 3);
>
> -- insert into public.testtabelle values ('d', 4);
> insert into public.testtabelle values ('e', 5);
> insert into public.testtabelle values ('d', 4);
>
> delete from public.testtabelle where c001 = 3;
>
> update public.testtabelle set c001 = c001 - 1 where c001 > 3;
>
> select * from public.testtabelle;
>
> commit;
>
> this will fail on Pg 7.3.3 and Pg 7.4 rc2. However, if 4 is inserted
> before 5 it will work for some reason.
>
> does anybody have an explanation for this behaviour?
>
> Cheers,
>
> Hans
>
> --
> Cybertec Geschwinde u Schoenig
> Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria
> Tel: +43/2952/30706 or +43/660/816 40 77
> www.cybertec.at, www.postgresql.at, kernel.cybertec.at
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
