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"

Thanks