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

On 06/19/2015 04:15 AM, Gary Cowell wrote:
> 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"

Different implementations of autocommit.

For psql see here:
http://www.postgresql.org/docs/9.4/interactive/app-psql.html
AUTOCOMMIT

For ODBC see here:
https://msdn.microsoft.com/en-us/library/ms131281.aspx

For JDBC see here:
https://jdbc.postgresql.org/documentation/94/query.html
Example 5.2

>
> Thanks
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com