Re: different transaction handling between postgresql and

On Mon, 14 Jul 2003, Jörg Schulz wrote:

> 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?

The first I believe

Transactions have to be committed in there entirety or not at all.
MySql does not do transactions on its standard tables anyway you have to
switch them on at table create time (early versions could not cope with
them at all!) I have this feeling the reason Oracle gives this result may
be again because transactions have been switched off. If you want the
second result in Postgres just switch auto-commit on!

Peter Childs

> 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
>