Re: BUG or strange behaviour of update on primary key

Hello there
Thanks Tom!
By the way I find something very funny :

Oracle 11gR2 :

SQL> create table testup ( a number ) ;

Tabella creata.

SQL> alter table testup add primary key (a) NOT DEFERRABLE INITIALLY
IMMEDIATE ;

Tabella modificata.

SQL> insert into testup values (1 ) ;

Creata 1 riga.

SQL> insert into testup values (2 ) ;

Creata 1 riga.

SQL> commit ;

Commit completato.

SQL> update testup set a=a+1 ;

Aggiornate 2 righe. -->>> Oracle Bug ??

SQL> commit ;

Commit completato.

Postgresql :

create table testup ( a int ) ;

alter table testup add primary key(a) NOT DEFERRABLE INITIALLY IMMEDIATE ;

insert into testup values (1);

insert into testup values (2);

update testup set a=a+1 ;
ERROR: duplicate key value violates unique constraint "testup_pkey"

Like Tom correctly says :

alter table testup DROP CONSTRAINT testup_pkey ;

alter table testup add primary key(a) DEFERRABLE INITIALLY IMMEDIATE ;

update testup set a=a+1 ;
UPDATE 2

commit;

Seems an Oracle bug not Postgresql one!

Regards, Mat

2011/10/18 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>

> desmodemone <desmodemone(at)gmail(dot)com> writes:
> > create table testup ( a int ) ;
>
> > alter table testup add primary key (a ) ;
>
> > insert into testup values (1);
>
> > insert into testup values (2);
>
> > update testup set a=a+1 ;
> > ERROR: duplicate key value violates unique constraint "testup_pkey"
> > DETTAGLI: Key (a)=(2) already exists.
>
> If you want that to work reliably, you need to mark the primary key
> constraint as deferred. By default, uniqueness is checked immediately
> when a row is inserted or updated --- and here, when you update 1 to 2,
> it's not unique because the second row hasn't been visited yet.
>
> regards, tom lane
>