Обсуждение: 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

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