Re: JDBC CTS 1.2.1 - Mailing list pgsql-jdbc

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