Re: PreparedStatement parameters and mutable objects

Barry Lind wrote:
> Oliver,
>
> I think we are free to do it either way.  I suspect that most jdbc
> drivers delay this work until the actual call to the server.

Good!

> I am curious why you are choosing to optimize this particular aspect? It
> seems the bigger win is on query results where the common code path does
> the following:  read from socket, put value into a new byte[], convert
> byte[] to a String, convert String to required object/type. There are
> three copies in different forms of the same data being generated for
> each value.  Ugh!

In our case, we don't pull much data from the DB in normal operation.
The normal case is for each transaction to query the DB to check the
state hasn't shifted unexpectedly (this is a tiny ResultSet -- one row
with one int8 column), then push out a large batch of
inserts/updates/deletes where the bulk of the data is bytearrays set via
PreparedStatement.setBytes(). We only have large ResultSets when pulling
data out of the database at startup, and at that point we're not so
worried about garbage as we're not doing latency-sensitive processing yet.

> I have long wanted to fix this, but it always ends up being too big a
> project for the time I have to work on it.  Have you given any thought
> to this bigger problem?

It's somewhere on my list of things to do.. but that list currently
fills a whiteboard. I've given a bit of thought to it but didn't get
much beyond looking at the current code. It seems like we should be
doing something like maintaining the original bytearray as a single
object (or perhaps one per row), and doing conversions from ranges of
that array on demand .. would need to maintain some metadata to easily
locate column and row boundaries .. using the V3 binary tuple format
might make things easier.

> Also, your email below indicates to me that you are trying to do this
> with the old V2 protocol methods of executing queries.  The new V3
> protocol provides a much, much better interface to the driver to do this
> more efficiently.  Have you looked at using the V3 protocol features to
> execute the prepared statements?

Yes, I'm looking at using V3 (also probably for COPY support). There's a
fair amount of reorganization needed to do this properly, though; the
main part would be abstracting the conversion of parameters to wire
representations, so we can support different representations for V2 and
V3 easily, and possibly something similar for query execution methods
(to support reusing queries via V3's Parse message).

There's also a whole slew of other changes that could be made to use
V3's features, especially around cursors and not materializing all the
rows in a resultset at once; it looks like we can use a named portal and
set a row limit, then use FETCH or another Execute to navigate around
the rows within that portal, for any row-returning command -- much
cleaner than the current "transform-to-DECLARE" approach. But I'm not
sure if I want to take that on at the moment :)

-O