Re: bug in 7.4 ...

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-Jrgen Schnig" <postgres(at)cybertec(dot)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
>