JDBC CTS 1.2.1 (was: Re: patch: bring org/postgresql/test/README up to date)

Is this the right list for sending JDBC-specific patches or should
they be directed to pgsql-patches(at)postgresql(dot)org?

Anyhow-

Changes

This patch partially brings org/postgresql/test/README up to date.
The document doesn't seem to haved changed much since 2001:
http://archives.postgresql.org/pgsql-jdbc/2001-08/msg00350.php
http://archives.postgresql.org/pgsql-patches/2001-09/msg00000.php
http://archives.postgresql.org/pgsql-jdbc/2001-09/msg00155.php

Since the JDBC driver is no longer part of the main tree in
http://developer.postgresql.org/cvsweb.cgi/pgsql/ I removed all
references to the latter.

I also updated the document to reflect the fact that the tests are
now run via "ant test" rather than "make; make check". I also
updated a couple of paths to files that seem to have been shuffled
around.

Lastly, I deleted trailing whitespace.

Question

The document refers to
http://java.sun.com/products/jdbc/jdbctestsuite-1_2_1.html

Would it make sense to provide instructions for the seemingly
newer test suite available at
http://java.sun.com/products/jdbc/jdbctestsuite-1_3_1.html

Thanks,
Vadim

On Fri, 22 Oct 2004, Vadim Nasardinov wrote:

> Is this the right list for sending JDBC-specific patches or should
> they be directed to pgsql-patches(at)postgresql(dot)org?

Everything about JDBC happens here.

> This patch partially brings org/postgresql/test/README up to date.

I've applied this, but I think more effort should be made in getting this
information into the documentation on the website:

http://jdbc.postgresql.org/development/intro.html#Test+Suite

This file is hard to find and we want to centralize rather than duplicate
information.

> The document refers to
> http://java.sun.com/products/jdbc/jdbctestsuite-1_2_1.html
> Would it make sense to provide instructions for the seemingly
> newer test suite available at
> http://java.sun.com/products/jdbc/jdbctestsuite-1_3_1.html

Yes. I know Dave Cramer was spending some time trying to get PostgreSQL
to pass the newer version, so you might hit him up for the sql scripts and
configuration he used.

Kris Jurka

On Friday 22 October 2004 18:40, Kris Jurka wrote:
> I've applied this, but I think more effort should be made in getting
> this information into the documentation on the website:
>
> http://jdbc.postgresql.org/development/intro.html#Test+Suite

Sure, that makes sense. I hadn't noticed this document. The CVS page
[1] did not mention that in addition to

$ cvs -z3 -d :pserver:anonymous(at)gborg(dot)postgresql(dot)org:/usr/local/cvsroot/pgjdbc co -P pgjdbc

you could also check out

$ cvs -z3 -d :pserver:anonymous(at)gborg(dot)postgresql(dot)org:/usr/local/cvsroot/pgjdbc co -P www

Now that you mentioned it, I'll keep this in mind.

> > The document refers to
> > http://java.sun.com/products/jdbc/jdbctestsuite-1_2_1.html
> > Would it make sense to provide instructions for the seemingly
> > newer test suite available at
> > http://java.sun.com/products/jdbc/jdbctestsuite-1_3_1.html
>
> Yes. I know Dave Cramer was spending some time trying to get
> PostgreSQL to pass the newer version, so you might hit him up for
> the sql scripts and configuration he used.

Before attempting to tackle the 1.3.1 suite, I tried to get 1.2.1 up
and running. My results are as follows:

PASSED: 1778
FAILED: 510

The attached report lists the names of failing tests. Do these
numbers seem to be in the right ballpark, or am I wildly off base
here? I haven't begun to take a closer look at failures [2] yet.
Thought I'd ask here first.

BTW, I wrote a little script to automate the testsuite setup [3]. If
anyone finds it useful, it might make sense to find a place for it in
CVS.

Thanks,
Vadim

Footnotes
1. http://jdbc.postgresql.org/development/cvs.html
2. http://people.redhat.com/~vadimn/scratch/pgsql-jdbc/logs/logs-2004-10-26.tar.gz
3. http://people.redhat.com/~vadimn/scratch/pgsql-jdbc/cts/cts-setup.py

Vadim Nasardinov wrote:

> Before attempting to tackle the 1.3.1 suite, I tried to get 1.2.1 up
> and running. My results are as follows:
>
> PASSED: 1778
> FAILED: 510
>
> The attached report lists the names of failing tests. Do these
> numbers seem to be in the right ballpark, or am I wildly off base
> here? I haven't begun to take a closer look at failures [2] yet.
> Thought I'd ask here first.

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.

-O

On Wednesday 27 October 2004 18:54, Oliver Jowett wrote:
> > PASSED: 1778
> > FAILED: 510
>
> Lots of those failures seem to be this same error:
[...]
> which looks like a setup problem of some sort rather than a real failure.

Ok, thanks. I'll look into this. What number of passing tests should I be
shooting for, if/when I resolve apparent configuration problems in my setup?

Vadim,

You will need to remove all of the tests below that depend on in/out
parameters

ie

#**********************************************************************
# SQL Statements for creating procedures for appropriate JDBC datatypes
#**********************************************************************

#Numeric_ProcCreate=

#Decimal_ProcCreate=

#Double_ProcCreate=

#Float_ProcCreate=

#Real_ProcCreate=

#Bit_ProcCreate=

#Smallint_ProcCreate=

#Tinyint_ProcCreate=

#Integer_ProcCreate=

#Bigint_ProcCreate=

#Char_ProcCreate=

#Varchar_ProcCreate=

#Longvarchar_ProcCreate=

#Longvarcharnull_ProcCreate=

#Date_ProcCreate=

#Time_ProcCreate=

#Timestamp_ProcCreate=

#Binary_ProcCreate=

#Varbinary_ProcCreate=

#Longvarbinary_ProcCreate=

#Integer_InOut_ProcCreate=

#SelCoffee_ProcCreate=

#IOCoffee_ProcCreate=

#Numeric_Io_Max_ProcCreate=
#Numeric_Io_Min_ProcCreate=
#Numeric_Io_Null_ProcCreate=

#Decimal_Io_Max_ProcCreate=
#Decimal_Io_Min_ProcCreate=
#Decimal_Io_Null_ProcCreate=

#Double_Io_Max_ProcCreate=
#Double_Io_Min_ProcCreate=
#Double_Io_Null_ProcCreate=

#Float_Io_Max_ProcCreate=
#Float_Io_Min_ProcCreate=
#Float_Io_Null_ProcCreate=

#Real_Io_Max_ProcCreate=
#Real_Io_Min_ProcCreate=
#Real_Io_Null_ProcCreate=

#Bit_Io_Max_ProcCreate=
#Bit_Io_Min_ProcCreate=
#Bit_Io_Null_ProcCreate=

#Smallint_Io_Max_ProcCreate=
#Smallint_Io_Min_ProcCreate=
#Smallint_Io_Null_ProcCreate=

#Tinyint_Io_Max_ProcCreate=
#Tinyint_Io_Min_ProcCreate=
#Tinyint_Io_Null_ProcCreate=

#Integer_Io_Max_ProcCreate=
#Integer_Io_Min_ProcCreate=
#Integer_Io_Null_ProcCreate=

#Bigint_Io_Max_ProcCreate=
#Bigint_Io_Min_ProcCreate=
#Bigint_Io_Null_ProcCreate=

#Char_Io_Name_ProcCreate=
#Char_Io_Null_ProcCreate=

#Varchar_Io_Name_ProcCreate=
#Varchar_Io_Null_ProcCreate=

#Longvarchar_Io_Name_ProcCreate=
#Longvarchar_Io_Null_ProcCreate=

#Date_Io_Mfg_ProcCreate=
#Date_Io_Null_ProcCreate=

#Time_Io_Brk_ProcCreate=
#Time_Io_Null_ProcCreate=

#Timestamp_Io_Intime_ProcCreate=
#Timestamp_Io_Null_ProcCreate=

#Binary_Proc_Io_ProcCreate=

#Varbinary_Proc_Io_ProcCreate=

#Longvarbinary_Io_ProcCreate=

Vadim Nasardinov wrote:

>On Wednesday 27 October 2004 18:54, Oliver Jowett wrote:
>
>
>>> PASSED: 1778
>>> FAILED: 510
>>>
>>>
>>Lots of those failures seem to be this same error:
>>
>>
>[...]
>
>
>>which looks like a setup problem of some sort rather than a real failure.
>>
>>
>
>Ok, thanks. I'll look into this. What number of passing tests should I be
>shooting for, if/when I resolve apparent configuration problems in my setup?
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
>
>
>

--
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561

On Wednesday 27 October 2004 14:15, Vadim Nasardinov wrote:
> Before attempting to tackle the 1.3.1 suite, I tried to get 1.2.1 up
> and running. My results are as follows:
>
> PASSED: 1778
> FAILED: 510

I took a closer look at the failures and discovered that the situation
was a lot better than the above figures suggest. It turns out that
that the testsuite only has 575 tests or so -- not 1700+, as one might
imagine by looking at the above.

(If you use the slightly newer exclusion list available from
http://java.sun.com/products/jdbc/exclude.1.2.1.txt?button=Download
then the number of tests goes down to 548.)

Each test is run in four different "vehicles": appclient, ejb, jsp,
and servlet. If you sort passed and failed tests by vehicle, the
following picture emerges:

-------+-----------+-----+-----+--------
| appclient | ejb | jsp | servlet
-------+-----------+-----+-----+--------
PASSED | 551 | 129 | 549 | 549
-------+-----------+-----+-----+--------
FAILED | 24 | 442 | 22 | 22
-------+-----------+-----+-----+--------
TOTAL | 575 | 571 | 571 | 571
-------+-----------+-----+-----+--------

Note that the "ejb" vehicle is an outlier. I haven't taken a close
look at it yet, but I suspect that the vast majority of tests that
fail in the ejb vehicle do so for reasons unrelated to the JDBC
driver.

A more correct breakdown is as follows:

PASSED: 549
FAILED: 22
TOTAL: 571

Quite respectable, if you ask me. Furthermore, I suspect that most
(if not all) of the remaining 22 failures are due to lack of support
for in/out parameters in stored procedures in the current backend, as
Dave Cramer pointed out in
http://archives.postgresql.org/pgsql-jdbc/2004-10/msg00290.php

The updated version of the script that counts passed and failed tests
is available here:
http://people.redhat.com/~vadimn/scratch/pgsql-jdbc/cts/cts-logs.py

Vadim

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

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

Vadim Nasardinov wrote:

> 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

On Tuesday 16 November 2004 18:30, Dave Cramer wrote:
> I doubt changing the backend version will help.

The reason I asked was because I seemed to recall vaguely that there
had been some change in the way autocommit worked. Having refreshed
my recollection, I realize that the issue I was thinking of is
unrelated to the current situation:

http://www.postgresql.org/docs/7.4/static/release-7-4.html

| Observe the following incompatibilities:
|
| * The server-side autocommit setting was removed and
| reimplemented in client applications and
| languages. Server-side autocommit was causing too many
| problems with languages and applications that wanted to
| control their own autocommit behavior, so autocommit was
| removed from the server and added to individual client APIs as
| appropriate.

On Tuesday 16 November 2004 18:47, Oliver Jowett wrote:
> 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?

I'm not sure.

I (temporarily) patched the driver in a couple of places (see
attached) and ran the tests in the ejb and jsp vehicles to try and
figure out exactly where their behavior diverges. Initially, both
vehicles call connection.setAutoCommit(false), like so:

| at org.postgresql.jdbc2.AbstractJdbc2Connection.setAutoCommit(AbstractJdbc2Connection.java:598)
| at com.sun.enterprise.resource.JdbcConnectionAllocator.createResource(JdbcConnectionAllocator.java:75)
| at com.sun.enterprise.resource.PoolManagerImpl.getResourceFromPool(PoolManagerImpl.java:150)
| at com.sun.enterprise.resource.JdbcXAConnection.<init>(JdbcXAConnection.java:59)
| at com.sun.enterprise.resource.JdbcConnectionAllocator.createResource(JdbcConnectionAllocator.java:83)
| at com.sun.enterprise.resource.PoolManagerImpl.getResourceFromPool(PoolManagerImpl.java:150)
| at com.sun.enterprise.resource.PoolManagerImpl.getResource(PoolManagerImpl.java:100)
| at com.sun.enterprise.resource.JdbcDataSource.getConnection(JdbcDataSource.java:101)
| at com.sun.enterprise.resource.JdbcDataSource.getConnection(JdbcDataSource.java:66)
| at com.sun.cts.tests.jdbc.ee.common.DataSourceConnection.getConnection(DataSourceConnection.java:39)
| at com.sun.cts.tests.jdbc.ee.dbMeta.dbMeta1.dbMetaClient1.setup(dbMetaClient1.java:120)

From this point on, the bevahiors of the ejb and jsp vehicles differ.
The ejb vehicle does not make any further changes to the autocommit
setting. The jsp vehicle does. It calls
connection.setAutoCommit(true) like so:

| at org.postgresql.jdbc2.AbstractJdbc2Connection.setAutoCommit(AbstractJdbc2Connection.java:598)
| at com.sun.enterprise.resource.JdbcXAConnection$JdbcConnection.getCurrentConnection(JdbcXAConnection.java:507)
| at com.sun.enterprise.resource.JdbcXAConnection$JdbcConnection.createStatement(JdbcXAConnection.java:254)
| at com.sun.cts.tests.jdbc.ee.common.dbSchema.dropTables(dbSchema.java:196)
| at com.sun.cts.tests.jdbc.ee.common.dbSchema.createTable(dbSchema.java:94)
| at com.sun.cts.tests.jdbc.ee.common.dbSchema.createData(dbSchema.java:48)
| at com.sun.cts.tests.jdbc.ee.dbMeta.dbMeta1.dbMetaClient1.setup(dbMetaClient1.java:123)

This has the following effect. The method
AbstractJdbc2Statement#execute(Query query, ParameterList params, int
flags) has the following piece of code:

if (connection.getAutoCommit())
flags |= QueryExecutor.QUERY_SUPPRESS_BEGIN;

So, when the tests are run in the jsp vehicle, the
QUERY_SUPPRESSION_BEGIN bit is toggled on. In the ejb vehicle, it is
left unset.

In org.postgresql.core.v2.QueryExecutorImpl, we have

private void execute(V2Query query,
SimpleParameterList parameters,
ResultHandler handler,
int maxRows, int flags) throws SQLException {

// ...
String queryPrefix = null;
if (protoConnection.getTransactionState() ==
ProtocolConnection.TRANSACTION_IDLE &&
(flags & QueryExecutor.QUERY_SUPPRESS_BEGIN) == 0) {
queryPrefix = "BEGIN;";

// ...
}

Since the ejb vehicle does not set the QUERY_SUPPRESS_BEGIN flag, the
queryPrefix variable is set to "BEGIN;". The end result is that in
the case of the appclient, jsp, and servlet vehicles, each statement
is treated as its own transaction. This allows tests to proceed
merrily despite the failure of DROP TABLE statements.

In the case of ejb, each test runs in a single transaction which
includes DROP TABLE statements. As soon as a "DROP TABLE" statement
fails, the rest of the transaction goes up in flames and the test
fails.

Not sure if anything can be done about it.

> 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.

Right. Moreover, Savepoint seems to only be present in jdbc3:
org/postgresql/jdbc3/PSQLSavepoint.java

As far as I can tell, CTS 1.2.1 is meant to be run against the RI of
J2EE 1.2.1. The latter only works under JDK 1.3. It has a hard-coded
version check.

> 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.

Yes, this sounds like it may work. But, like I said, you'd have to go
through some contortions to make CTS 1.2.1 work in this setup. It's
probably possible to run J2EE 1.2.1 under JDK 1.3 while at the same
time running CTS 1.2.1 under JDK 1.4 with a driver that supports
savepoints.

Alternatively, you could just say we're not even going to try to make
CTS 1.2.1 pass.

Vadim

On Wed, 17 Nov 2004, Vadim Nasardinov wrote:

> Alternatively, you could just say we're not even going to try to make
> CTS 1.2.1 pass.
>

Right, our goal should be to pass the newer 1.3.1 CTS. It does not suffer
this problem. Unfortunately it has code that depends on CallableStatement
dependent code all over the place. So you can't just not run that
particular directory of tests.

You seem to be going through some complicated gyrations to test outdated
software. Why not test 8.0beta and CTS 1.3.1?

Kris Jurka

Vadim Nasardinov wrote:

> Alternatively, you could just say we're not even going to try to make
> CTS 1.2.1 pass.

Unless you have some particular reason you need to pass 1.2.1, I'd just
try the newer version.

Did you get any useful test results from the non-EJB modes? Anything the
driver does wrong there?

-O

On Wednesday 17 November 2004 15:25, Oliver Jowett wrote:
> Did you get any useful test results from the non-EJB modes? Anything
> the driver does wrong there?

No, I didn't see any cases where the blame could be pegged squarely on
the driver, except the testSupportsStoredProcedures test.

If I run the test with this exclusion list:
http://java.sun.com/products/jdbc/exclude.1.2.1.txt?button=Download

I get 18 failures out of 548 tests. One of them is

testSupportsStoredProcedures

This fails because in
org/postgresql/jdbc2/AbstractJdbc2DatabaseMetaData.java, we have

public boolean supportsStoredProcedures() throws SQLException
{
return false;
}

whereas the test harness expects this method to return true.

One other failure looks like a bug in the harness. Specifically,
testGetSQLState
fails because this condition
b.getSQLState() instanceof java.lang.String

tests false, where b is a SQLException. Now, the only way this can
possibly happen is if getSQLState() returns null. Looking at the
javadoc
http://java.sun.com/j2se/1.4.2/docs/api/java/sql/SQLException.html#getSQLState%28%29

I don't see anything that says the method is not allowed to return
null. Looks like a bug in the CTS.

The remaining 16 failures are all due to the same reason. The backend
chokes on the following query (or some variation thereof):

| DB1=> select substring(version(), 1, 20) as version;
| version
| ----------------------
| PostgreSQL 7.3.4-RH
| (1 row)
| DB1=> create table Real_Tab (MAX_VAL REAL, MIN_VAL REAL,NULL_VAL REAL);
| CREATE TABLE
| DB1=> insert into Real_Tab values(3.4E38,1.4E-45, null);
| ERROR: Bad float4 input format -- underflow

The funny part is that I can do

| DB1=> select 1.4E-45;
| ?column?
| --------------------------------------------------
| 0.0000000000000000000000000000000000000000000014
| (1 row)
| DB1=> select 3.4E38;
| ?column?
| -----------------------------------------
| 340000000000000000000000000000000000000
| (1 row)

But I would have to test this against a more recent version of the
backend. In any case, these 16 failures don't be seem to be caused
by the driver.

Vadim

I've sort of eluded to this before. testSupportsStoredProcedures is
allowed to return false.
The test suite is quite arbitrary, I'd sooner see your (and others)
efforts going towards beefing up our internal test suite.

Dave

Vadim Nasardinov wrote:

>On Wednesday 17 November 2004 15:25, Oliver Jowett wrote:
>
>
>>Did you get any useful test results from the non-EJB modes? Anything
>>the driver does wrong there?
>>
>>
>
>No, I didn't see any cases where the blame could be pegged squarely on
>the driver, except the testSupportsStoredProcedures test.
>
>If I run the test with this exclusion list:
>http://java.sun.com/products/jdbc/exclude.1.2.1.txt?button=Download
>
>I get 18 failures out of 548 tests. One of them is
>
> testSupportsStoredProcedures
>
>This fails because in
>org/postgresql/jdbc2/AbstractJdbc2DatabaseMetaData.java, we have
>
> public boolean supportsStoredProcedures() throws SQLException
> {
> return false;
> }
>
>whereas the test harness expects this method to return true.
>
>One other failure looks like a bug in the harness. Specifically,
> testGetSQLState
>fails because this condition
> b.getSQLState() instanceof java.lang.String
>
>tests false, where b is a SQLException. Now, the only way this can
>possibly happen is if getSQLState() returns null. Looking at the
>javadoc
>http://java.sun.com/j2se/1.4.2/docs/api/java/sql/SQLException.html#getSQLState%28%29
>
>I don't see anything that says the method is not allowed to return
>null. Looks like a bug in the CTS.
>
>
>The remaining 16 failures are all due to the same reason. The backend
>chokes on the following query (or some variation thereof):
>
> | DB1=> select substring(version(), 1, 20) as version;
> | version
> | ----------------------
> | PostgreSQL 7.3.4-RH
> | (1 row)
> | DB1=> create table Real_Tab (MAX_VAL REAL, MIN_VAL REAL,NULL_VAL REAL);
> | CREATE TABLE
> | DB1=> insert into Real_Tab values(3.4E38,1.4E-45, null);
> | ERROR: Bad float4 input format -- underflow
>
>
>The funny part is that I can do
>
> | DB1=> select 1.4E-45;
> | ?column?
> | --------------------------------------------------
> | 0.0000000000000000000000000000000000000000000014
> | (1 row)
> | DB1=> select 3.4E38;
> | ?column?
> | -----------------------------------------
> | 340000000000000000000000000000000000000
> | (1 row)
>
>But I would have to test this against a more recent version of the
>backend. In any case, these 16 failures don't be seem to be caused
>by the driver.
>
>
>
>Vadim
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>
>
>

--
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561

On Wednesday 17 November 2004 15:12, Kris Jurka wrote:
> You seem to be going through some complicated gyrations to test
> outdated software. Why not test 8.0beta and CTS 1.3.1?

I _am_ going to run CTS 1.3.1 sometime soon. Just wanted to make sure
I understood what the story was with 1.2.1. When I first posted my
results in
http://archives.postgresql.org/pgsql-jdbc/2004-10/msg00279.php, no one
seemed to know whether or not 510 failures out of 1778 tests was the
correct number and why the failure rate was so high. I now have a
satisfactory answer to that question. In case anyone cares, my
scripts for running CTS 1.2.1 and generating an error report can be
found here:
http://people.redhat.com/vadimn/scratch/pgsql-jdbc/cts/

Dave suggested repeatedly that energy would be better spent on
expanding existing test coverage. I agree that this is a worthwhile
goal and will be happy to help when and where I can.

Here's what the current coverage looks like:
http://people.redhat.com/vadimn/scratch/pgsql-jdbc/emma/2004-11-18/

Vadim