Обсуждение: different transaction handling between postgresql and oracle/mysql

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

Um, the behaviour you are seeing is what would happen in PostgreSQL if
everything were all in one transaction. What you show for Oracle is what
would happen if each statement were in it's own transaction.

On the postgresql server here, without transactions:

create temp table test (a int primary key);
insert into test values (1);  -> ok
insert into test values (2);  -> ok
insert into test values (1);  -> duplicate key
insert into test values (3);  -> ok

With transactions you get:

create temp table test (a int primary key);
insert into test values (1);  -> ok
begin;
insert into test values (2);  -> ok
insert into test values (1);  -> duplicate key
insert into test values (3);  -> aborted transaction
abort;

These are both correct behaviour AFAIK.

Hpoe this helps,

On Mon, Jul 14, 2003 at 09:47:30AM +0200, 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?
> 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

--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> "the West won the world not by the superiority of its ideas or values or
> religion but rather by its superiority in applying organized violence.
> Westerners often forget this fact, non-Westerners never do."
>   - Samuel P. Huntington

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
>

> ... I have this feeling the reason Oracle gives this result may
> be again because transactions have been switched off!

This snippet comes from the Oracle console:
(table name is "a" not "test" / messages are in german)

SQL> show autocommit;
autocommit OFF
SQL> select * from a;

         A
----------
         1

SQL> commit;

Transaktion mit COMMIT abgeschlossen.

SQL> insert into a values (2);

1 Zeile wurde erstellt.

SQL> insert into a values (3);

1 Zeile wurde erstellt.

SQL> insert into a values (1);
insert into a values (1)
*
FEHLER in Zeile 1:
ORA-00001: Verstoß gegen Eindeutigkeit, Regel (KLAX.SYS_C001753)


SQL> insert into a values (4);

1 Zeile wurde erstellt.

SQL> commit;

Transaktion mit COMMIT abgeschlossen.

SQL> select * from a;

         A
----------
         1
         3
         4
         2

SQL>

> ...If you want the
> second result in Postgres just switch auto-commit on

Maybe I have to do other things in another table. So I must
do it inside a transaction.

Jörg

Jörg Schulz wrote:

>>... I have this feeling the reason Oracle gives this result may
>>be again because transactions have been switched off!
>
> This snippet comes from the Oracle console:
> (table name is "a" not "test" / messages are in german)
>
...

> SQL> select * from a;
>
>          A
> ----------
>          1
>          3
>          4
>          2

Presumably Oracle is not rolling back a duplicate key violation,
allowing the transaction to continue. This is an often requested
feature not present in PostgreSQL.

Mike Mascari
mascarm@mascari.com

On Mon, 14 Jul 2003, Mike Mascari wrote:

> Jörg Schulz wrote:
>
> >>... I have this feeling the reason Oracle gives this result may
> >>be again because transactions have been switched off!
> >
> > This snippet comes from the Oracle console:
> > (table name is "a" not "test" / messages are in german)
> >
> ...
>
> > SQL> select * from a;
> >
> >          A
> > ----------
> >          1
> >          3
> >          4
> >          2
>
> Presumably Oracle is not rolling back a duplicate key violation,
> allowing the transaction to continue. This is an often requested
> feature not present in PostgreSQL.

Bug. Not Feature

    Transactions must be all or nothing. If one step fails for what
ever reason all steps must be failed and rolled back. While in this simple
case ignoring the statment may look fine in more complex examples (where
the is more data in the table...) this can mean data loss and massive
problems!

Peter Childs

>
> Mike Mascari
> mascarm@mascari.com
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>

Oracle does not roll back any transaction unless explicitly requested by
the client application. If there are errors while executing statements
inside a transaction, their effect is rolled back, not the whole
transaction. The application can then decide if the successful part of
the transaction is rolled back as a whole, or committed as a whole...

This is contrasting with postgreSQL behavior of rolling back the
transaction automatically after any error.

This feature is often requested because it's very useful, especially in
big imports (where you don't want to roll back your whole import because
of 2 invalid lines).
Of course others say that input validation is the application's
responsibility, which is also a valid point, but I can tell you there
are other valid usages of this feature, like complicated transactions
where you want to take one course of action or other depending on the
success/failure of a query, which is only possible using hacks in
postgres right now, and doesn't even always work (like for a duplicate
key insert, you can't 100% make sure it won't fail). The suggested
solution on this list is to be prepared to repeat the transaction, but
this leads to more complicated application code.

The main reason why this feature is painful to implement in Postgres is
the lack of nested transactions. Until they are not implemented, chunk
your error prone transactions as small as you can, filter out invalid
data, and be prepared to repeat the cycle.

Cheers,
Csaba.


On Mon, 2003-07-14 at 10:34, Mike Mascari wrote:
> Jörg Schulz wrote:
>
> >>... I have this feeling the reason Oracle gives this result may
> >>be again because transactions have been switched off!
> >
> > This snippet comes from the Oracle console:
> > (table name is "a" not "test" / messages are in german)
> >
> ...
>
> > SQL> select * from a;
> >
> >          A
> > ----------
> >          1
> >          3
> >          4
> >          2
>
> Presumably Oracle is not rolling back a duplicate key violation,
> allowing the transaction to continue. This is an often requested
> feature not present in PostgreSQL.
>
> Mike Mascari
> mascarm@mascari.com
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>

On Mon, 2003-07-14 at 10:43, Peter Childs wrote:
> On Mon, 14 Jul 2003, Mike Mascari wrote:
>
> > Jörg Schulz wrote:
> >
> > >>... I have this feeling the reason Oracle gives this result may
> > >>be again because transactions have been switched off!
> > >
> > > This snippet comes from the Oracle console:
> > > (table name is "a" not "test" / messages are in german)
> > >
> > ...
> >
> > > SQL> select * from a;
> > >
> > >          A
> > > ----------
> > >          1
> > >          3
> > >          4
> > >          2
> >
> > Presumably Oracle is not rolling back a duplicate key violation,
> > allowing the transaction to continue. This is an often requested
> > feature not present in PostgreSQL.
>
> Bug. Not Feature
>
>     Transactions must be all or nothing. If one step fails for what
> ever reason all steps must be failed and rolled back. While in this simple
> case ignoring the statment may look fine in more complex examples (where
> the is more data in the table...) this can mean data loss and massive
> problems!

Wrong. Oracle is NOT ignoring the error, it responds with an error
message, which is telling the user that the current query had an error
(in Java you get an exception).
Now the application can decide if this means an error for the whole
transaction or just for the current query. If your application rolles
back on all errors, this is the exact behavior which is forced on you by
postgres. But if the application can decide the last error is ignorable
(which can be true in some cases, as in this example), and the
transaction is still valid despite of the latest error, then you get in
a lot of cases more elegant/readable application code. You could also
easily log the failed inserts and do something else on that data, while
the valid entries are committed.
I see this as a feature, because the programmer gets more control, more
options, and the original behavior is still implemented.

Cheers,
Csaba.

>
> Peter Childs
>
> >
> > Mike Mascari
> > mascarm@mascari.com
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 7: don't forget to increase your free space map settings
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

Peter Childs wrote:

> On Mon, 14 Jul 2003, Mike Mascari wrote:
>
>>Jörg Schulz wrote:
>>
>>Presumably Oracle is not rolling back a duplicate key violation,
>>allowing the transaction to continue. This is an often requested
>>feature not present in PostgreSQL.
>
>
> Bug. Not Feature
>
>     Transactions must be all or nothing. If one step fails for what
> ever reason all steps must be failed and rolled back. While in this simple
> case ignoring the statment may look fine in more complex examples (where
> the is more data in the table...) this can mean data loss and massive
> problems!

I agree. However a common scenario that has appeared on these lists is
a request for an atomic 'CREATE IF NOT EXISTS, ELSE REPLACE' without
race conditions. Because Oracle doesn't rollback the transaction, it
is implementable in SQL. For PostgreSQL, you either need to use
various locking techniques which reduces concurrency or be prepared to
resubmit the entire transaction. Savepoints and/or nested transactions
may alleviate the situation in the future, however.

Mike Mascari
mascarm@mascari.com

On 14 Jul 2003 at 5:18, Mike Mascari wrote:

> I agree. However a common scenario that has appeared on these lists is
> a request for an atomic 'CREATE IF NOT EXISTS, ELSE REPLACE' without
> race conditions. Because Oracle doesn't rollback the transaction, it
> is implementable in SQL. For PostgreSQL, you either need to use
> various locking techniques which reduces concurrency or be prepared to
> resubmit the entire transaction. Savepoints and/or nested transactions
> may alleviate the situation in the future, however.

Recognising the need of such, SQL standard has been extended to accommodate a
merge command which is create if not exists else update types.

Correct me if I am wrong..

BTW, what's wrong with select for update in such scenario?




Bye
 Shridhar

--
Feel free to contact me (flames about my english and the useless of thisdriver
will be redirected to /dev/null, oh no, it's full...).(Michael Beck, describing
the PC-speaker sound device)

This has been discussed for many times on this list, but shortly: when
inserting a new row, there's no previous row to select for update. If
you have 2 concurrent transactions, both of them can execute the select
for update at the same time, select nothing, and then try to insert the
same key, and bang: one of them fails.

Cheers,
Csaba.


On Mon, 2003-07-14 at 11:31, Shridhar Daithankar wrote:
> On 14 Jul 2003 at 5:18, Mike Mascari wrote:
>
> > I agree. However a common scenario that has appeared on these lists is
> > a request for an atomic 'CREATE IF NOT EXISTS, ELSE REPLACE' without
> > race conditions. Because Oracle doesn't rollback the transaction, it
> > is implementable in SQL. For PostgreSQL, you either need to use
> > various locking techniques which reduces concurrency or be prepared to
> > resubmit the entire transaction. Savepoints and/or nested transactions
> > may alleviate the situation in the future, however.
>
> Recognising the need of such, SQL standard has been extended to accommodate a
> merge command which is create if not exists else update types.
>
> Correct me if I am wrong..
>
> BTW, what's wrong with select for update in such scenario?
>
>
>
>
> Bye
>  Shridhar
>
> --
> Feel free to contact me (flames about my english and the useless of thisdriver
> will be redirected to /dev/null, oh no, it's full...).(Michael Beck, describing
> the PC-speaker sound device)
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match
>

On Mon, 2003-07-14 at 04:07, Csaba Nagy wrote:
[snip]
> This feature is often requested because it's very useful, especially in

Amen!  Give the app developer the opportunity to travel down a different
code path if s/he tries, for example, to insert a duplicate key.

[snip]
> The main reason why this feature is painful to implement in Postgres is
> the lack of nested transactions. Until they are not implemented, chunk
> your error prone transactions as small as you can, filter out invalid
> data, and be prepared to repeat the cycle.

Lack of nested transactions should not be a barrier.  The RDBMS that
I use professionally (Rdb/VMS) does not have nested transactions,
yet it and Oracle do the same thing: return an error code and allow
the app to decide what to do.

Yes, many times that will be to rollback the transaction, but it
might also be to change the data and try the statement again.

Let the developer decide!!!

--
+-----------------------------------------------------------+
| Ron Johnson, Jr.     Home: ron.l.johnson@cox.net          |
| Jefferson, LA  USA   http://members.cox.net/ron.l.johnson |
|                                                           |
| 4 degrees from Vladimir Putin
+-----------------------------------------------------------+

On Mon, 14 Jul 2003, Peter Childs wrote:

> On Mon, 14 Jul 2003, Mike Mascari wrote:
>
> > J�rg Schulz wrote:
> >
> > >>... I have this feeling the reason Oracle gives this result may
> > >>be again because transactions have been switched off!
> > >
> > > This snippet comes from the Oracle console:
> > > (table name is "a" not "test" / messages are in german)
> > >
> > ...
> >
> > > SQL> select * from a;
> > >
> > >          A
> > > ----------
> > >          1
> > >          3
> > >          4
> > >          2
> >
> > Presumably Oracle is not rolling back a duplicate key violation,
> > allowing the transaction to continue. This is an often requested
> > feature not present in PostgreSQL.
>
> Bug. Not Feature

Well as far as spec compliance goes it's not. Our behavior is mostly
compliant by explicitly saying that all errors are unrecoverable ones.
The spec explicitly allows (or one could say expects) behavior like
Oracle's for any error that doesn't occur on the execution of the commit
itself. As to whether it's a good idea or not, ...