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@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
