Re: [PATCH] PostgreSQL JDBC

On Sun, 8 Aug 1999, Philipp Matthias Hahn wrote:

> Hello again!
>
> The LargeObject-API does not work. By comparing your JDBC and the C chat I
> found some differences in the protocol-strings between the front- and
> backend:
> You have to wrap lo_create() and setInt(oid) in a transaction beginning
> with "begin" and ending with "end". I tried to solve the problem myself,
> but encountered some problems:

Whoa, don't do it!

The problem stem's from the fact that as of PostgreSQL 6.5, all lo
operations must now be wrapped within begin/end.

However, to do this correctly in jdbc, you simply use the
setAutoCommit(false) method in the Connection object to enable
transactions.

> > 1. You can't call "begin" when calling setBytes(byte[]), because some
> other statement with the same connection could do something in between
> befor you execute the PreparedStatement.

Exactly the reason to use setAutoCommit().

> 2. You could save the byte-array and delay the creation of the LargeObject
> till you execute the Statement. For batched-processing you must not call
> "begin" again, because postgresql does not support nested transactions.

The JDBC specs (both 1.x and 2.0) only support one level of transactions.

> 3. Somebody could execute "begin" by his own, so you have to check all
> sql-commands for a begin-of-transaction.

This is a big no-no with JDBC in general, and not just with PostgreSQL.
The setAutoCommit() method (and it's associated commit() and rollback()
methods) is the only true way of handling transactions within jdbc.

Also, this would blow thread safety out of the window.

> 4. A solution for 2&3 might look like this: Issue a "begin" regardless of
> the state and check for "NOTICE: BeginTransactionBlock and not in default
> state", create the LargeObject, execute the statement and issue an "end",
> if no "NOTICE" was received. The byte-array is saved and converted to a
> LargeObject just before the execution of the statement. If the statement
> commits, you can free the byte-array and keep the oid for another
> statement. If the statement fails, you have to keep the byte-array for
> another try. You can free the array if another setBytes() is called or the
> statement is reseted.

This could cause havock with multiple threads, and may cause problems
keeping track of open lo's. Each open LO has some memory context allocated
to it, and issuing begin/end internally could cause the LO to be closed
when it's not expected to be closed.

> 5. I tried part of 4, but could not gain access to the NOTICE. I checked
> the source and found a function "IsTransactionBlock()" in
> "backend/access/transam/xact.c", but it is not public.

Not sure about that one.

> As an attachment I send you a patch by which "example.blobtest" works
> again. I looked at test/examples/testlo.c and added the
> transaction.

I think that testlo.c may be behind with the now compulsory need for
transations with lo's.

> PreparedStatement.setBinaryStream(InputStream x) might by somthing like
> public void setBinaryStream(int parameterIndex,
> InputStream x,
> int length)
> throws SQLException
> {
> try
> {
> byte[] b = new byte[length];
> int l = b.read(b, 0, length);
> if(l != length)
> throw new SQLException("foo bar");
> setBytes(parameterIndex, b);
> }
> catch(IOException ioe)
> {
> throw new SQLException("foo bar");
> }
> }

I'll have a look at this. In theory the read should block if there is less
than length bytes in the stream, but I'll need to test this out.

> By the way my version is: postgres-6.5 vanilla

A lot of people have been caught out with the change in how LO's operate.
The compulsory transaction requirement closed a bug where people could
access LO's outside of transactions, and this caused the backend a whole
lot of problems.

Peter

--
Peter T Mount peter(at)retep(dot)org(dot)uk
Main Homepage: http://www.retep.org.uk
PostgreSQL JDBC Faq: http://www.retep.org.uk/postgres
Java PDF Generator: http://www.retep.org.uk/pdf