Thread: Cursors and transactions
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
"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
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
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
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
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