Re: JDBC CTS 1.2.1
Vadim Nasardinov
http://archives.postgresql.org/pgsql-jdbc/2004-10/msg00287.php
> Lots of those failures seem to be this same error:
>
> > SVR: createString1: create table ctstable1 (TYPE_ID int, TYPE_DESC varchar(32), primary key(TYPE_ID))
> > SVR-ERROR: SQLException creating ctstable2 or ctstable1 table
> > SVR-ERROR: ERROR: current transaction is aborted, queries ignored until end of transaction block
> > SVR: Closed the database connection
> > SVR-ERROR: Setup Failed!
>
> which looks like a setup problem of some sort rather than a real
> failure.
These failures occur only in the "ejb" vehicle. The other three
vehicles don't exhibit these errors. I mentioned this in
http://archives.postgresql.org/pgsql-jdbc/2004-10/msg00309.php
To try to figure out why "ejb" behaves differently, I turned on
statement logging in postgresql.conf and performed two runs, like so:
$ make jdbc-tests VEHICLE_DIRS=${CTS_HOME}/common/harness/vehicle/ejb
$ make jdbc-tests VEHICLE_DIRS=${CTS_HOME}/common/harness/vehicle/jsp
The former exercises only the ejb vehicle and the latter the jsp
vehicle.
The way the tests work is, first, they try to drop certain tables in
case the tables haven't been cleaned up by a previous run. If a table
does not exist, the raised SQLException is ignored. Second, the test
creates all the necessary tables and proceeds to test whatever it is
supposed to test.
Here's the (partial) output of the first test in the "ejb" vehicle:
| [1] FATAL: unsupported frontend protocol
| [1-1] LOG: query: set datestyle = 'ISO'; select version(), case when pg_encoding_to_char(1) = 'SQL_ASCII' then 'UNKNOWN' else
| [1-2] getdatabaseencoding() end
| [2] LOG: query: begin; set autocommit = on; set client_encoding = 'UNICODE'; commit
| [3] LOG: query: BEGIN;drop table ctstable2
| [4] ERROR: table "ctstable2" does not exist
| [5] LOG: query: drop table ctstable1
| [6] ERROR: current transaction is aborted, queries ignored until end of transaction block
| [7] LOG: query: create table ctstable1 (TYPE_ID int, TYPE_DESC varchar(32), primary key(TYPE_ID))
| [8] ERROR: current transaction is aborted, queries ignored until end of transaction block
| [9] LOG: query: drop table ctstable2
| [10] ERROR: current transaction is aborted, queries ignored until end of transaction block
| [11] LOG: query: drop table ctstable1
| [12] ERROR: current transaction is aborted, queries ignored until end of transaction block
| [13] LOG: query: COMMIT
| [14] LOG: query: BEGIN;drop table ctstable2
| [15] ERROR: table "ctstable2" does not exist
| [16] LOG: query: drop table ctstable1
| [17] ERROR: current transaction is aborted, queries ignored until end of transaction block
| [18] LOG: query: create table ctstable1 (TYPE_ID int, TYPE_DESC varchar(32), primary key(TYPE_ID))
| [19] ERROR: current transaction is aborted, queries ignored until end of transaction block
| [20] LOG: query: drop table ctstable2
| [21] ERROR: current transaction is aborted, queries ignored until end of transaction block
| [22] LOG: query: drop table ctstable1
| [23] ERROR: current transaction is aborted, queries ignored until end of transaction block
| [24] LOG: query: COMMIT
Here's the output of the same test in the "jsp" vehicle:
| [1] FATAL: unsupported frontend protocol
| [1-1] LOG: query: set datestyle = 'ISO'; select version(), case when pg_encoding_to_char(1) = 'SQL_ASCII' then 'UNKNOWN' else
| [1-2] getdatabaseencoding() end
| [2] LOG: query: begin; set autocommit = on; set client_encoding = 'UNICODE'; commit
| [3] LOG: query: drop table ctstable2
| [4] ERROR: table "ctstable2" does not exist
| [5] LOG: query: drop table ctstable1
| [6] ERROR: table "ctstable1" does not exist
| [7] LOG: query: create table ctstable1 (TYPE_ID int, TYPE_DESC varchar(32), primary key(TYPE_ID))
| [8] NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'ctstable1_pkey' for table 'ctstable1'
| [9-1] LOG: query: create table ctstable2 (KEY_ID int, COF_NAME varchar(32), PRICE float, TYPE_ID int, primary key(KEY_ID), foreign
| [9-2] key(TYPE_ID) references ctstable1)
| [10] NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'ctstable2_pkey' for table 'ctstable2'
| [11] NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
| [12] LOG: query: insert into ctstable1 values(1, 'Type-1')
| [13] LOG: query: insert into ctstable1 values(2, 'Type-2')
The crucial difference between these two vehicles is this. In the ejb
vehicle, each transaction is explicitly demarcated with "BEGIN
... COMMIT". When the first DROP TABLE statement fails, it causes the
backend to ignore any subsequent statements issued in the same
transaction. (As a parenthetical aside, other databases don't behave
this way. I believe this has been discussed countless times.)
In the jsp vehicle, no explicit transaction demarcation is performed.
Each statement seems to be executed in its own transaction. So, when
the first DROP TABLE statement fails as expected, it's no big deal.
Subsequent CREATE TABLE statements succeed.
So, my question is, Is this, as Oliver suggested, a setup problem,
possibly due to my using an old version of the backend? As I
mentioned in
http://archives.postgresql.org/pgsql-jdbc/2004-10/msg00303.php, my
current setup is
| test=> select substring(version(), 1, 20) as version;
| version
| ----------------------
| PostgreSQL 7.3.4-RH
| (1 row)
Or is something broken here irrespective of my setup?
Thanks,
Vadim
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
Dave Cramer
It looks like the ejb vehicle is trying to create the tables inside of a transaction. The first drop fails so all the rest fail as well.
I doubt changing the backend version will help.
Dave
-- Dave Cramer http://www.postgresintl.com 519 939 0336 ICQ#14675561
Oliver Jowett
> The crucial difference between these two vehicles is this. In the ejb
> vehicle, each transaction is explicitly demarcated with "BEGIN
> ... COMMIT". When the first DROP TABLE statement fails, it causes the
> backend to ignore any subsequent statements issued in the same
> transaction. (As a parenthetical aside, other databases don't behave
> this way. I believe this has been discussed countless times.)
It sounds like a bug in the CTS if it assumes it can run DDL (or for
that matter any query) in a transaction after a failure. This is not
specified by JDBC, AFAIK, and is demonstrably not a valid assumption to
make under PostgreSQL.
Is there any way to tell the CTS to run each bit of DDL in a separate
transaction?
If you can issue a SAVEPOINT before the DROP TABLE and ROLLBACK TO
SAVEPOINT on failure, it might work. But it seems unlikely you can do
this without modifying the CTS itself, and you'd need to use 8.0 for
SAVEPOINT support.
It might be useful to have an option to make the driver automatically
generate SAVEPOINTs before each query (when autocommit is off), and do
ROLLBACK TO SAVEPOINT or RELEASE SAVEPOINT as appropriate after the
query completes. This gives behaviour more like other DBs at some cost
to performance (I wouldn't want it to be on by default). Again, this
would only work against an 8.0 server.
-O
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match