> From: Gary Cowell <gary.cowell@xxxxxxxxx> >To: pgsql-general@xxxxxxxxxxxxxx >Sent: Friday, 19 June 2015, 12:15 >Subject: Transaction abort difference between psql, isql, ODBC and JDBC pgsql 8.4 > > > >Hello > >I'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" > > I think you're after the PROTOCOL option, see: https://odbc.postgresql.org/docs/config.html -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
