Re: PreparedStatement parameters and mutable objects

Oliver Jowett wrote:
> Barry Lind wrote:
>> 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!
>>
>> 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.

My idea for handling this problem was to extend the use of PGobject to
cover all datatypes (not just special ones as is the case today). And
then store data as PGobject[][] where each column value would be some
subtype of PGobject specific for that datatype. Thus 'PGint' would know
how to handle the v2 and v3 protocol format for ints and have accessor
methods for getting the value as a String or possibly other formats,
etc. I think this would allow the code in most cases to only need one
copy of the data around at a time.

>> 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 :)
>

Yup. There is a lot of work to do here to fully utilize the V3
protocol. I worked with Tom to insure that his protocol rewrite had all
the features needed by jdbc (which I believe it does), we just need to
take advantage of them.

thanks,
--Barry