Обсуждение: BUG #13849: Need a parameter added similar to "edb_stmt_level_tx"
The following bug has been logged on the website: Bug reference: 13849 Logged by: David Skinner Email address: skinneda@us.ibm.com PostgreSQL version: 9.5rc1 Operating system: Windows Description: To add support for PostgreSQL, we need the equivalent of EDB's edb_stmt_level_tx parameter, which you can see explained here: http://www.enterprisedb.com/docs/en/8.4/oracompat/Postgres_Plus_Advanced_Server_Oracle_Compatibility_Guide-04.htm We've read all about the new "ON CONFLICT" keyword in 9.5, but coding it into our complex ECM product would be kludgy and an incomplete fix. Our use case (for example) is adding a folder, and preventing a duplicate key error on the (enforced unique) containment name. Rather than rollback and retry the entire txn, we only need to catch the SQL error and retry the INSERT (which failed on dup. key error). There are similar situations in over 50 places in our code that relies on NOT rolling back the txn on SQL error. NOT rolling back is the standard behavior on Oracle, SQL Server, Informix and DB2. We understand the reasons for PG's current behavior, but it is not practical in the complex real world, especially since it should be extremely simple to support as EDB has. Thanks! Also, when is 9.5 GA expected?
Postgres supports the behaviour you want but the interface is different. Instead you need to create a subtransaction around each statement you want to catch errors in. The way to do this depends on the PL language or language binding you're executing these commands in. In psql you set ON_ERROR_ROLLBACK on. In pl/pgsql you add BEGIN blocks (http://www.postgresql.org/docs/9.5/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING), in plpython and other languages they're mapped to the language's exception handling, see http://www.postgresql.org/docs/current/static/plpython-subtransaction.html EDB's documentation does say "Note: Use edb_stmt_level_tx set to TRUE only when absolutely necessary, as this may cause a negative performance impact." which makes me assume it is just doing the same thing ON_ERROR_ROLLBACK does which is wrapping every statement in a subtransaction. EDB is in the business of adding features to make things compatible with Oracle but Postgres less so. If there was a major advantage to the Oracle interface or it was in the standard then it might be more compelling but just for compatibility with Oracle probably not. Especially since Postgres has had bad experiences with server-side configuration options that change the transaction semantics in the past.