[GENERAL] different transaction handling between postgresql and oracle/mysql

[Jörg Schulz]

Suppose the following:

create table test (a int primary key);
insert into test values (1);

select * from test;
a
=
1

In Postgresql if you do the following in a transaction (either with
autocommit=off or with an explizit begin):

insert into test values (2); -> ok
insert into test values (1); -> error (duplicate key)
insert into test values (3); -> error (transaction aborted)
commit;

You get:

select * from test;
a
=
1


In Oracle/MySQL if you do the same you get:

insert into test values (2); -> ok
insert into test values (1); -> error (duplicate key)
insert into test values (3); -> ok
commit;

select * from test;
a
=
1
2
3

Which behavior is right? 
Is there a way to make Postgresql behave like the other databases?
Which other Databases act like Postgresql and which do it like Oracle/MySQL?

Jörg

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster