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.klaver@aklaver.com