Thread: plans and estimates for supporting JDBC 3.0

I'd like to ask for help in figuring out where the current
implementation stands with respect to JDBC 3.0 compliance, with a
particular emphasis on support for distributed transactions.

The current source tree contains about 35,200 lines of .java code [1],
of which 7,800 lines are in org/postgresql/test/.  The latter number
breaks down as follows:

   7,000 lines in org/postgresql/test/jdbc2/
     260 lines in org/postgresql/test/jdbc3/

There seems to be a fair amount of code that has already been written to
support JDBC 3.0.

As far as I can tell, XADataSource, XAConnection, and XAResource have
not been implemented yet.

  | $ find org/ -name \*.java | \
  |  xargs grep -E "XA(DataSource|Connection|Resource)" | wc -l
  |       0


Is there a plan for doing this and an estimate of the required effort?
(As far as I understand, support for the XA stuff needs to be added on
the backend side.  Ignoring that for a second, are there any estimates
for the amount of work required on the driver side?)


Thanks,
Vadim


Footnotes
 1. http://people.redhat.com/~vadimn/scratch/pgsql-jdbc/file-stats.txt

Vadim Nasardinov wrote:

> As far as I can tell, XADataSource, XAConnection, and XAResource have
> not been implemented yet.
>
>   | $ find org/ -name \*.java | \
>   |  xargs grep -E "XA(DataSource|Connection|Resource)" | wc -l
>   |       0

Yes; there's no point in implementing XA* until we have a 2PC
implementation on the server side to back it up.

As I understand it, you do not need an XA implementation to be JDBC3
compliant anyway.

> Is there a plan for doing this and an estimate of the required effort?
> (As far as I understand, support for the XA stuff needs to be added on
> the backend side.  Ignoring that for a second, are there any estimates
> for the amount of work required on the driver side?)

It depends on what the server interface to 2PC ends up looking like.
With current proposals it doesn't seem like much work on the driver side.

See http://www.hut.fi/~hlinnaka/pgsql/ for some 2PC (backend & JDBC
driver) work.

-O

On Wednesday 27 October 2004 16:33, Oliver Jowett wrote:
> Yes; there's no point in implementing XA* until we have a 2PC
> implementation on the server side to back it up.

Ok.  I hear that Heikki's 2PC patches are unlikely to get merged any
earlier than 8.1.

> As I understand it, you do not need an XA implementation to be JDBC3
> compliant anyway.

Yes and no.  Section 6.5 [1] does not list XA as an explicit
requirement for 3.0 compliance.  So, yes, you're right.

On the other hand, Section 6.2 [2] seems to suggest that you must
implement XA, if the backend supports 2PC.

> > Is there a plan for doing this and an estimate of the required
> > effort?  (As far as I understand, support for the XA stuff needs
> > to be added on the backend side.  Ignoring that for a second, are
> > there any estimates for the amount of work required on the driver
> > side?)
>
> It depends on what the server interface to 2PC ends up looking like.

I spoke with Tom Lane about this.  If I understood him correctly,
chances are, the interface will end up looking very much like whatever
Heikki currently has.

> With current proposals it doesn't seem like much work on the driver
> side.

To get a better feel for what "not much work" actually looks like, I
googled for existing implementations.

The lower bound estimate is provided by implementations that "emulate"
distributed transactions.  The way this works is, you implement
XAResource#prepare(Xid) [3] to always return XA_OK.  In the second
phase, when XAResource.commit(Xid, int) [4] is called, the local
transaction is actually committed.

Despite the fact that this approach cannot provide transactional
semantics, it's pretty widely used.

The MySQL JDBC driver used to have this up to and including version
2.0.14 [5].  Since it didn't really work, it was removed from later
versions.  MySQL's implementation was about 2100+ lines of code [6].

JBoss provides (or used to provide) similar wrappers in its
org.jboss.pool.jdbc.xa.wrapper.* package, but I can't seem to find the
relevant sources [7].

Jonas, too, provides XA wrappers implemented in 1300+ lines of code
[8].

There's also XAPool.  The size of its "standard" implementation is
about 3100 lines of code[9].  (They have a test case for PostgreSQL
[10].)

So, based on this, I'd say it takes about 1300 to 3100 lines of code
to provide fake XA support.

As a side note, it looks like the PostgreSQL JDBC driver used to
provide emulated XA support [11].

As far as real implementations go, MaxDB (previously known as SAPDB)
claims to support 2PC and its JDBC driver implements XA.  The size of
MaxDB's implementation is about 1800+ lines of code [12].

One could also look at the size of the relevant .class files in the
Oracle JDBC driver as an upper bound estimate.


Vadim


------------------------------------------------------------------------------

Footnotes

1. JDBC 3.0 API Compliance, p. 40:

   | A driver that is compliant with the JDBC 3.0 API must do the
   |  following:
   |
   |   * Comply with the JDBC 2.0 API requirements
   |
   |   * Include the following required interfaces:
   |
   |     * java.sql.ParameterMetaData
   |     * java.sql.Savepoint
   |
   |   * Fully implement the DatabaseMetaData interface, including the
   |     following methods added in the JDBC 3.0 API:
   |
   |     * supportsSavepoints
   |     * supportsNamedParameters
   |     * supportsMultipleOpenResults
   |     * supportsGetGeneratedKeys
   |     * getSuperTypes
   |     * getSuperTables
   |     * getAttributes
   |     * getResultSetHoldability
   |     * supportsResultSetHoldability
   |     * getSQLStateType
   |     * getDatabaseMajorVersion
   |     * getDatabaseMinorVersion
   |     * getJDBCMajorVersion
   |     * getJDBCMinorVersion

2. Guidelines and Requirements, p. 38

   | * Drivers should provide access to every feature implemented by
   |   the underlying data source, including features that extend the
   |   JDBC API. When a feature is not supported, the corresponding
   |   methods throw an SQLException. The intent is for applications
   |   using the JDBC API to have access to the same feature set as
   |   native applications.

3. http://java.sun.com/j2se/1.4.2/docs/api/javax/transaction/xa/XAResource.html#prepare%28javax.transaction.xa.Xid%29

4.
http://java.sun.com/j2se/1.4.2/docs/api/javax/transaction/xa/XAResource.html#commit%28javax.transaction.xa.Xid,%20boolean%29

5. http://prdownloads.sourceforge.net/mmmysql/mm.mysql-2.0.14-you-must-unjar-me.jar?download

6. This is for mm.mysql-2.0.14-you-must-unjar-me.jar:
   | $ find . -name \*.java |  grep xa/
   | ./org/gjt/mm/mysql/xa/ClientConnection.java
   | ./org/gjt/mm/mysql/xa/TwoPhaseConnection.java
   | ./org/gjt/mm/mysql/xa/TxConnection.java
   | ./org/gjt/mm/mysql/xa/XAConnectionImpl.java
   | ./org/gjt/mm/mysql/xa/XADataSourceImpl.java
   | $ find . -name \*.java |  grep xa/ | xargs cat | wc -l
   |    2176

7. http://jboss.sourceforge.net/snapshots/jboss-head-snapshot.tar.gz

   This 66MB file does not have anything under
   jboss-head/pool/src/main/org/jboss/pool/jdbc/xa/wrapper

   The CVS repository has way too much stuff in it.  I'm not sure
   where to look for org.jboss.pool.jdbc.xa.wrapper.* classes:
     http://cvs.sourceforge.net/viewcvs.py/jboss/

8. http://cvs.forge.objectweb.org/cgi-bin/viewcvs.cgi/jonas/jonas/src/org/objectweb/jonas/jdbc_xa/

   | $ cat ./jonas/src/org/objectweb/jonas/jdbc_xa/*.java | wc -l
   |    1326

9. http://cvs.forge.objectweb.org/cgi-bin/viewcvs.cgi/xapool/xapool/src/org/enhydra/jdbc/standard/

   | $ cat xapool/src/org/enhydra/jdbc/standard/*.java | wc -l
   |    3107

10.
http://cvs.forge.objectweb.org/cgi-bin/viewcvs.cgi/*checkout*/xapool/xapool/test/jotmxapooltest/config/postgres.properties?rev=1.1

11. http://people.redhat.com/~vadimn/scratch/pgsql-jdbc/changelog.html#2002-08-14-16:35

12. Download maxdb-source-7_5_00_18.tgz from
     http://www.mysql.com/products/maxdb/
     http://dev.mysql.com/downloads/maxdb/7.5.00.html
     http://dev.mysql.com/get/Downloads/MaxDB/7.5.00/maxdb-source-7_5_00_18.tgz/from/pick

   The source for the JDBC driver is in
     V75_00_18/MaxDB_ORG/sys/src/jv/sapdbc.src.jar

   After unjarring,

   | $ grep -lF XA com/sap/dbtech/jdbcext/*.java | xargs cat | wc -l
   |    1806
   | $ cat com/sap/dbtech/jdbcext/XA*.java | wc -l
   |    1138

Vadim Nasardinov wrote:
> On Wednesday 27 October 2004 16:33, Oliver Jowett wrote:
>
>>With current proposals it doesn't seem like much work on the driver
>>side.
>
> To get a better feel for what "not much work" actually looks like, I
> googled for existing implementations. [...]

Another data point is that Heikki's JDBC patch is ~300 lines and seems
to do pretty much everything it needs to.

-O

On Tue, 2 Nov 2004, Vadim Nasardinov wrote:

> > With current proposals it doesn't seem like much work on the driver
> > side.
>
> To get a better feel for what "not much work" actually looks like, I
> googled for existing implementations.
>

You might be interested to know that pg used to have a fake XA
implementation in the driver as well.

http://gborg.postgresql.org/project/pgjdbc/cvs/cvs.php/pgjdbc/org/postgresql/xa?sa=1

I don't think a straight lines of code count can really indicate the
difficulty of implementation.  If the backend API matches up with the XA
API then you can easily write even thousands of lines of boilerplate code.
If the APIs don't match then you may have to do some complicated mapping.
All of the real work for XA is on the backend side.

Kris Jurka

Vadim Nasardinov wrote:

> On the other hand, Section 6.2 [2] seems to suggest that you must
> implement XA, if the backend supports 2PC.

> 2. Guidelines and Requirements, p. 38
>
>    | * Drivers should provide access to every feature implemented by
>    |   the underlying data source, including features that extend the
>    |   JDBC API. When a feature is not supported, the corresponding
>    |   methods throw an SQLException. The intent is for applications
>    |   using the JDBC API to have access to the same feature set as
>    |   native applications.

I think this means that if a native app can issue "PREPARE TRANSACTION
'abcd'", then you should be able to do that via JDBC too. That's true
regardless of whether we implement the XA interface or not. But XA would
still be nice to have.

-O

On Tuesday 02 November 2004 15:09, Kris Jurka wrote:
> You might be interested to know that pg used to have a fake XA
> implementation in the driver as well.
>
> http://gborg.postgresql.org/project/pgjdbc/cvs/cvs.php/pgjdbc/org/postgresql/xa?sa=1

Yes, I've seen this.  As I mentioned in the original post:

 |  As a side note, it looks like the PostgreSQL JDBC driver used to
 |  provide emulated XA support [11].
 | [...]
 |  Footnotes
 | [...]
 |  11. http://people.redhat.com/~vadimn/scratch/pgsql-jdbc/changelog.html#2002-08-14-16:35

The commit message by "barry" (Barry Lind?) does not elaborate
on the reasons why these classes were removed:

  | Also note that this checkin removes the PostgresqlDataSource and
  | files in the xa directory.  A recent checkin has added new
  | datasource support that replaces the functionality provided by
  | these classes.

Do you know why this code was removed?  (That is, aside from the fact that it
couldn't possibly work.)


Thanks,
Vadim

This is exactly why it was removed, because it didn't really work.

Dave

Vadim Nasardinov wrote:

>On Tuesday 02 November 2004 15:09, Kris Jurka wrote:
>
>
>>You might be interested to know that pg used to have a fake XA
>>implementation in the driver as well.
>>
>>http://gborg.postgresql.org/project/pgjdbc/cvs/cvs.php/pgjdbc/org/postgresql/xa?sa=1
>>
>>
>
>Yes, I've seen this.  As I mentioned in the original post:
>
> |  As a side note, it looks like the PostgreSQL JDBC driver used to
> |  provide emulated XA support [11].
> | [...]
> |  Footnotes
> | [...]
> |  11. http://people.redhat.com/~vadimn/scratch/pgsql-jdbc/changelog.html#2002-08-14-16:35
>
>The commit message by "barry" (Barry Lind?) does not elaborate
>on the reasons why these classes were removed:
>
>  | Also note that this checkin removes the PostgresqlDataSource and
>  | files in the xa directory.  A recent checkin has added new
>  | datasource support that replaces the functionality provided by
>  | these classes.
>
>Do you know why this code was removed?  (That is, aside from the fact that it
>couldn't possibly work.)
>
>
>Thanks,
>Vadim
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>
>
>

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

    I don't think there was a point to pretending to have XA support
when the product really doesn't have it, and on top of that it actually
makes the situation worse.

    The only possible safe way to include PostgreSQL in an XA
transaction is to involve at most one local (PostgreSQL) resource, and
prepare it last and commit it first.  That could be done by an application
server if it manages the process and knows that the PostgreSQL driver is
just a local resource.  But if the PostgreSQL driver pretends to be an XA
resource, then the app server can't tell that PostgreSQL is really just
using a local transaction under the covers, so it thinks it has all XA
resources and can't arrange for the local one to go in the correct order.
So again, a fake XA implementation specifically defeats the one possible
safe way to include a local resource in an XA transaction.

Aaron

On Tue, 2 Nov 2004, Vadim Nasardinov wrote:
> On Tuesday 02 November 2004 15:09, Kris Jurka wrote:
> > You might be interested to know that pg used to have a fake XA
> > implementation in the driver as well.
> >
> > http://gborg.postgresql.org/project/pgjdbc/cvs/cvs.php/pgjdbc/org/postgresql/xa?sa=1
>
> Yes, I've seen this.  As I mentioned in the original post:
>
>  |  As a side note, it looks like the PostgreSQL JDBC driver used to
>  |  provide emulated XA support [11].
>  | [...]
>  |  Footnotes
>  | [...]
>  |  11. http://people.redhat.com/~vadimn/scratch/pgsql-jdbc/changelog.html#2002-08-14-16:35
>
> The commit message by "barry" (Barry Lind?) does not elaborate
> on the reasons why these classes were removed:
>
>   | Also note that this checkin removes the PostgresqlDataSource and
>   | files in the xa directory.  A recent checkin has added new
>   | datasource support that replaces the functionality provided by
>   | these classes.
>
> Do you know why this code was removed?  (That is, aside from the fact that it
> couldn't possibly work.)
>
>
> Thanks,
> Vadim
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

On November 2, 2004 04:12 pm, Aaron Mulder wrote:
>         The only possible safe way to include PostgreSQL in an XA
> transaction is to involve at most one local (PostgreSQL) resource, and
> prepare it last and commit it first.  That could be done by an application
> server if it manages the process and knows that the PostgreSQL driver is
> just a local resource.

This is exactly what Weblogic lets you do, but you probably already knew
that :)

JdV!!


--
--------------------------------------------------------------
Jan de Visser                     jdevisser@digitalfairway.com

                Baruk Khazad! Khazad ai-menu!
--------------------------------------------------------------