![]() I think it's a mistake not to abort the whole tx. Transactions provide an "all-or-nothing" proposition, stating thatĮach work-unit performed in a database must either complete in its It's like IT Stockholm Syndrome.ĭoes not it violate even the definition of the transaction? We really shouldn't be too proud of any workarounds we've developedįor this pathological behavior. There'sĬertainly no API / UX reason why it should work this way. Part of the SQL standard that everyone else sensibly ignores. Guts that requires this behavior, or that it conforms to some obscure I can only assume there's either some limitation deep in the Postgres There no intuitive reason why a syntaxĮrror, or any other error for that matter, should abort a transaction. Have no experience with MSSQL or DB2 but I'll bet a dollar each theyĭon't work this way either. Oracle doesn't work this way, nor does MySQL. Throw an exception and cause your current transaction to be marked asĪborted. I don't understand this, though: "Normally, any error you make will ![]() Why does Oracle have such way of transaction management as a default, and why is it considered good practice?įor example, some random guy here in comments If one of the inserts fails I want to be sure that other inserts will be rollbacked or not even evaluated after the first error, which is exactly how it's done in PostgreSQL. What if my multiple inserts comprise some kind of an object or data structure? I end up completely unaware of the data state in my database and should be checking it after the commit. When I start a transaction I want to be sure that all the statements were successfull. Isn't this completely counterintuitive and dangerous? In Oracle, this is the default transaction management behaviour, which surprises me. ![]() database=# \set ON_ERROR_ROLLBACK interactive You can change that by setting ON_ERROR_ROLLBACK to "on" or "interactive", after that you can do multiple inserts ignoring errors, commit and have only successfully inserted rows in table after transaction end. ![]() In PostgreSQL, if you encounter an error in transaction (for example when your insert statement violates unique constraint), the whole transaction is aborted, you cannot commit it and no rows are inserted: database=# begin ĭatabase=# insert into table (id, something) values ('1','whatever') ĮRROR: duplicate key value violates unique constraint "table_id_key"ĭatabase=# insert into table (id, something) values ('2','whatever') ĮRROR: current transaction is aborted, commands ignored until end of transaction block ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |