Re: Transaction abort difference between psql, isql, ODBC and JDBC pgsql 8.4

> From: Gary Cowell <gary(dot)cowell(at)gmail(dot)com>
>To: pgsql-general(at)postgresql(dot)org
>Sent: Friday, 19 June 2015, 12:15
>Subject: [GENERAL] 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