Thread: Cursors and transactions

Cursors and transactions

From
"Chris Smith"
Date:
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.  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.

So, with that background and introduction down, here's my first two questions.

1. I'd like to get *something* working fairly quickly.  Is it possible to
force the 7.4 server to communicate using the V2 protocol, so I can quickly
port my old patch and use it while I work on something more ambitious?

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?

--
www.designacourse.com
The Easiest Way to Train Anyone... Anywhere.

Chris Smith - Lead Software Developer/Technical Trainer
MindIQ Corporation


Re: Cursors and transactions

From
Tom Lane
Date:
"Chris Smith" <cdsmith@twu.net> writes:
> 1. I'd like to get *something* working fairly quickly.  Is it possible to
> force the 7.4 server to communicate using the V2 protocol, so I can quickly
> port my old patch and use it while I work on something more ambitious?

Yes, 7.4 still supports V2 protocol (and even V1 protocol, I believe,
though there may be bit rot setting in there since it's probably gone
untested for years...)

> 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?

There's no such restriction on the backend side, so I assume this is
something JDBC-specific.  The comment could be obsolete, too.

            regards, tom lane

Re: Cursors and transactions

From
"Chris Smith"
Date:
Oliver Jowett wrote:
> 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?

Will do.  Since I'm not familiar with the drivers or their direction, I plan
to post a few first-cut attempts for comments before I go and spend eternity
writing something that no one else wants.

> 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..

Okay.  I will ignore it for now, then.  It doesn't seem to be used in the same
context as the portals that I'm planning to work with anyway.

--
www.designacourse.com
The Easiest Way to Train Anyone... Anywhere.

Chris Smith - Lead Software Developer/Technical Trainer
MindIQ Corporation


Re: Cursors and transactions

From
Oliver Jowett
Date:
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

Re: Cursors and transactions

From
"Chris Smith"
Date:
Sorry; forgot to copy the group.  Resending...

Tom Lane wrote:
> There's no such restriction on the backend side, so I assume this is
> something JDBC-specific.  The comment could be obsolete, too.
>

Okay, thanks.  I just wanted to be absolutely sure that trying to use
multiple named portals in V3 wouldn't be a dead end.

Incidentally, how similar is the JDBC driver from CVS head to the one
distributed with 7.4.1?  Is something like the work I'm proposing
already done there?  If not, is it likely that such a patch for 7.4.1
could be easily ported to CVS head?  After all, though I just need
the code to work ASAP for a 7.4.1 system, I'd also like my work to do
as much good as possible.

--
www.designacourse.com
The Easiest Way to Train Anyone... Anywhere.

Chris Smith - Lead Software Developer/Technical Trainer
MindIQ Corporation

Re: Cursors and transactions

From
Kris Jurka
Date:

On Thu, 12 Feb 2004, Chris Smith wrote:

> 1. I'd like to get *something* working fairly quickly.  Is it possible to
> force the 7.4 server to communicate using the V2 protocol, so I can quickly
> port my old patch and use it while I work on something more ambitious?

By using the compatible URL parameter you can make the JDBC driver act
like older versions, which includes the connection protocol.  Try for
example compatible=7.3

Kris Jurka