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

> From: Gary Cowell <gary.cowell@gmail.com>
>To: pgsql-general@postgresql.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
okaywith 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
thebehavior 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