Re: JDBC CTS 1.2.1

Vadim,

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

Vadim Nasardinov wrote:

>On Wednesday 27 October 2004 18:54, Oliver Jowett wrote in
>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
http://www.postgresintl.com
519 939 0336
ICQ#14675561