On 06/19/2015 04:15 AM, Gary Cowell wrote:
HelloI'm aware of the automatic transaction abort that occurs in PostgreSQL if you have DML throw an error during a transaction, this prevents future selects within transaction, until rollback or commit (and hence, new transaction). I'm okay with this. Doing all this on Red Hat 6.5 with Postgresql 8.4 (shipped repository version in Red Hat 6.5). Example in psql: $ psql psql (8.4.20) Type "help" for help. e5=# begin transaction; BEGIN e5=# select 1; ?column? ---------- 1 (1 row) e5=# insert into conc values(1,'mouse'); ERROR: duplicate key value violates unique constraint "conc_key" e5=# select 1; ERROR: current transaction is aborted, commands ignored until end of transaction block e5=# \q So I start a transaction, then get a DML error, and I can't select any more. Same thing happens with JDBC : $ java -cp .:/usr/share/java/postgresql-jdbc3.jar t PostgreSQL 8.4.20 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-11), 64-bit Jun 19, 2015 11:39:55 AM t main SEVERE: ERROR: duplicate key value violates unique constraint "conc_key" org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "conc_key" at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2094) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1827) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:508) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:370) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:362) at t.main(t.java:48) Jun 19, 2015 11:39:55 AM t main SEVERE: ERROR: current transaction is aborted, commands ignored until end of transaction block org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2094) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1827) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:508) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:370) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:250) at t.main(t.java:56) I'm just selecting version() before and after a duplicate insert. Again the transaction is aborted. But with ODBC in isql, and with other ODBC apps, we get this: +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> begin transaction SQLRowCount returns -1 SQL> select 1 +------------+ | ?column? | +------------+ | 1 | +------------+ SQLRowCount returns 1 1 rows fetched SQL> insert into conc values(1,'mouse'); [23505][unixODBC]ERROR: duplicate key value violates unique constraint "conc_key"; Error while executing the query [ISQL]ERROR: Could not SQLExecute SQL> select 1 +------------+ | ?column? | +------------+ | 1 | +------------+ SQLRowCount returns 1 1 rows fetched The transaction is not aborted with ODBC, but is with JDBC My odbcinst.ini says: # Driver from the postgresql-odbc package # Setup from the unixODBC package [PostgreSQL] Description = ODBC for PostgreSQL Driver = /usr/lib/psqlodbc.so Setup = /usr/lib/libodbcpsqlS.so Driver64 = /usr/lib64/psqlodbc.so Setup64 = /usr/lib64/libodbcpsqlS.so FileUsage = 1 and the driver odbc.ini: [e5] Description = Test to Postgres Driver = PostgreSQL Trace = Yes TraceFile = sql.log Database = e5 Servername = localhost UserName = Password = Port = 5432 Protocol = 6.4 ReadOnly = No RowVersioning = No ShowSystemTables = No ShowOidColumn = No FakeOidIndex = No ConnSettings = I don't mind which way it works, either aborting transactions after failed dml, or not. But I would like to know why the behavior is inconsistent between connection methods. Even if the answer is 'upgrade' or "you've messed a setting up"
Different implementations of autocommit. For psql see here: http://www.postgresql.org/docs/9.4/interactive/app-psql.html AUTOCOMMIT For ODBC see here: https://msdn.microsoft.com/en-us/library/ms131281.aspx For JDBC see here: https://jdbc.postgresql.org/documentation/94/query.html Example 5.2
Thanks
-- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
