Thread: 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
Re: different transaction handling between postgresql and oracle/mysql
From
Martijn van Oosterhout
Date:
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
Attachment
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, ...