Thread: bug in 7.4 ...

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

Hans-Jürgen Schönig <postgres@cybertec.at> writes:
> 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?

We do not have, and never have had, deferred unique constraints, which
is what would be needed to make this UPDATE work safely.  As-is, you
update the '5' entry to '4', and this fails if the '4' row hasn't been
updated yet, which depends on the order the rows happen to be visited
in.

It's on the TODO list but don't hold your breath ...
        regards, tom lane

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
>