Re: Transaction abort difference between psql, isql, ODBC and JDBC pgsql 8.4 - Mailing list pgsql-general
From | Adrian Klaver |
---|---|
Subject | Re: Transaction abort difference between psql, isql, ODBC and JDBC pgsql 8.4 |
Date | |
Msg-id | 55841D26.3020201@aklaver.com Whole thread Raw |
In response to | Transaction abort difference between psql, isql, ODBC and JDBC pgsql 8.4 (Gary Cowell <gary.cowell@gmail.com>) |
List | pgsql-general |
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
pgsql-general by date: