Re: Cursors and transactions

Chris Smith wrote:
> Hello,
>
> I've poked in and out of here in the past, but now I'm tackling a pseudo-large
> topic and am hoping that I'll ghet some help. Specifically, I want to port a
> patch I wrote for the 7.3 JDBC drivers over to 7.4. The patch was to
> incrementally send queries for PreparedStatement objects that use
> setBinaryStream. It was a simple patch for 7.3, but is looking less so for
> 7.4.
>

> Looking at 7.4, it looks like it would be much better to shoot for using the
> extended query mechanism to do this, because the V3 wire protocol uses
> byte-counted messages, but I won't know how many bytes are going to be in an
> encoded bytea value until I'm done sending it.

Yes, exactly. This will also reduce the amount of garbage that will be
generated (see some earlier discussion around mutable objects as
parameters).

> So, I've taken on the task of
> converting the 7.4 JDBC drivers to use the extended query mechanism for at
> least some (and possibly all) queries. It looks like the best way to
> accomplish this is to have every Statement object contain a named parsed
> statement in the V3 protocol; and every ResultSet contain a named portal.
> This would then supplant (conditional on protocol version, of course) the
> current code to accomplish similar goals, which seems to translate the
> statement SQL itself to use EXECUTE and DECLARE CURSOR commands.

I'm planning on doing the same at some point but I'm short on time at
the moment. Could you drop me an email if you make any progress on this?

> 2. Starting on the more ambitious approach, I notice a comment to the effect
> of "we only support one cursor per connection". Is that a fundamental
> limitation of PostgreSQL, or something specific to the drivers? If the
> former, it apparently doesn't apply to named portals in the V3 protocol
> extended query mechanism... is that correct?

This is completely unrelated to using backend cursors .. it's to do with
JDBC's support for named cursors that can later be used for a positioned
UPDATE, which postgresql doesn't support at all.

Actually that whole bit of code could be toasted as we don't claim to
support positioned update in our metadata anyway..

-O