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

[Gary Cowell <gary.cowell@xxxxxxxxx>]

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