Обсуждение: Options for protocol level cursors
Is there anyway to bind a cursor with SCROLL and WITH HOLD at the protocol level? Or perhaps configuring it so after binding it? I know you can use DECLARE, but I believe that this inhibits the driver from being able to select the transfer format for individual columns; it's all binary or it's all text. Also, I don't think I can DECLARE against a prepared statement, save restating the query, no?
James William Pye <pgsql@jwp.name> writes:
> Is there anyway to bind a cursor with SCROLL and WITH HOLD at the
> protocol level?
No, and for at least the first of those I don't see the point,
since the protocol doesn't offer any behavior other than forward
fetch.
regards, tom lane
On Jun 12, 2008, at 10:08 AM, Tom Lane wrote: > James William Pye <pgsql@jwp.name> writes: >> Is there anyway to bind a cursor with SCROLL and WITH HOLD at the >> protocol level? > > No, and for at least the first of those I don't see the point, > since the protocol doesn't offer any behavior other than forward > fetch. That protocol level inability doesn't keep you from using MOVE on cursor's name. And yes, it yield the appropriate errors when you try to MOVE backwards on a protocol declared cursor.
James William Pye <pgsql@jwp.name> writes:
> On Jun 12, 2008, at 10:08 AM, Tom Lane wrote:
>> James William Pye <pgsql@jwp.name> writes:
>>> Is there anyway to bind a cursor with SCROLL and WITH HOLD at the
>>> protocol level?
>>
>> No, and for at least the first of those I don't see the point,
>> since the protocol doesn't offer any behavior other than forward
>> fetch.
> That protocol level inability doesn't keep you from using MOVE on
> cursor's name.
Sure, but if you're willing to use a SQL-level operation on the portal
then you could perfectly well declare the cursor at SQL level too.
regards, tom lane
On Jun 12, 2008, at 3:59 PM, Tom Lane wrote: > Sure, but if you're willing to use a SQL-level operation on the portal > then you could perfectly well declare the cursor at SQL level too. Indeed, but like I said in my initial e-mail:: I know you can use DECLARE, but I believe that this inhibits the driver from being able to select the transfer formatfor individual columns; it's all binary or it's all text. Also, I don't think I can DECLARE against a preparedstatement, save restating the query, no? Also, the latter has other problems wrt statement parameters. I guess you could prepare(protocol level) the DECLARE, but that seems like a gross workaround as it defeats the purpose of prepared statements by forcing you to create a new statement for each cursor that you plan to open. Of course, unless you can close the existing one before opening the next one. [ I really do hope that I'm missing something, btw :( ]
James William Pye <pgsql@jwp.name> writes:
> Indeed, but like I said in my initial e-mail::
> I know you can use DECLARE, but I believe that this inhibits the
> driver from being able to select the transfer format for individual
> columns; it's all binary or it's all text.
Huh? I don't see why... you might have such a limitation in a
particular driver, but not in the protocol.
> Also, the latter has other problems wrt statement parameters. I guess
> you
> could prepare(protocol level) the DECLARE, but that seems like a gross
> workaround as it defeats the purpose of prepared statements by forcing
> you
> to create a new statement for each cursor that you plan to open.
Well, using a query for a cursor is grounds for replanning anyway,
because you might want a fast-start plan in such a case. And it's
*definitely* grounds for replanning if you are asking for SCROLL
capability --- the plan stored for a regular prepared statement
very likely can't support that.
regards, tom lane
On Jun 12, 2008, at 4:45 PM, Tom Lane wrote: > Huh? I don't see why... you might have such a limitation in a > particular driver, but not in the protocol. Oh? I know when you bind a prepared statement you have the ability state the formats of each column, but I'm not aware of the protocol's capacity to reconfigure the formats of an already existing cursor; ie, a DECLARE'd cursor. I know you can use the Describe message to learn about the cursor's column types and formats.... Got a link to the part of the protocol docs describing this feature? >> Also, the latter has other problems wrt statement parameters. I guess >> you >> could prepare(protocol level) the DECLARE, but that seems like a >> gross >> workaround as it defeats the purpose of prepared statements by >> forcing >> you >> to create a new statement for each cursor that you plan to open. > > Well, using a query for a cursor is grounds for replanning anyway, > because you might want a fast-start plan in such a case. And it's > *definitely* grounds for replanning if you are asking for SCROLL > capability --- the plan stored for a regular prepared statement > very likely can't support that. Ah, that is good to know. Thanks.
James William Pye <pgsql@jwp.name> writes:
> On Jun 12, 2008, at 4:45 PM, Tom Lane wrote:
>> Huh? I don't see why... you might have such a limitation in a
>> particular driver, but not in the protocol.
> Oh? I know when you bind a prepared statement you have the ability
> state the formats of each column, but I'm not aware of the protocol's
> capacity to reconfigure the formats of an already existing cursor; ie,
> a DECLARE'd cursor. I know you can use the Describe message to learn
> about the cursor's column types and formats....
You'd do it while Binding a FETCH command.
regards, tom lane
On Jun 13, 2008, at 9:24 AM, Tom Lane wrote: > You'd do it while Binding a FETCH command. Indeed, that is true. It seems quite unfortunate that drivers have to jump through such hoops to provide a convenient programmer's interface to held and/or scrollable cursors; bearing in mind all that has been discussed about the--well, *my*--desire of equivalent capabilities wrt the usual protocol level Prepare, Describe, Bind, and Execute sequence. Well, perhaps it is better to say that it is *ideal* to be able to merely use the protocol mechanisms to achieve the desired effect, rather than using them to use the SQL command yielding the same or similar(Binding FETCH for different formats) effect. =\ [Obviously, I was looking to propose... ;] My thoughts for creating a HOLD and/or SCROLL cursor on Bind would be to add YA GUC stating the cursor options for Bind cursors. Something along the lines of "default_bind_options=HOLD,SCROLL". Of course the actual default would be an empty string so as to preserve the existing functionality by default. I imagine there's a big fat "No" waiting for me for at least the following reasons[in no particular order]: 1. It's already possible to achieve the desired result and the proposed feature is, of course, not going to work with past versions. [Just put in the extra work to support past versionsof PG.] 2. I'm the only one asking/looking for it. (I'm so lonely ;) It is, of course, ideal to be able to state these options in the Bind message, but I don't see how that would be a possibility without a new protocol version or doing something dangerous like embedding the options in the cursor's name. "ain't happenin'". And, yeah, despite the first reason, I think I would prefer to use a new GUC. Certainly, with some bitterness. =( In any case, thanks for the discussion, Tom.
On Fri, 13 Jun 2008, James William Pye wrote: > My thoughts for creating a HOLD and/or SCROLL cursor on Bind would be to > add YA GUC stating the cursor options for Bind cursors. Something along > the lines of "default_bind_options=HOLD,SCROLL". > > 2. I'm the only one asking/looking for it. (I'm so lonely ;) > The JDBC driver would also like this ability, but a GUC is a pretty ugly hack. Also, since you still have to go to the SQL level to issue the MOVE or FETCH BACKWARD, you're still not all the way there to a full protocol solution. Kris Jurka
On Jun 13, 2008, at 4:40 PM, Kris Jurka wrote: > The JDBC driver would also like this ability, but a GUC is a pretty > ugly hack. I completely agree that it is an ugly hack. :) > Also, since you still have to go to the SQL level to issue the MOVE > or FETCH BACKWARD, you're still not all the way there to a full > protocol solution. Completely true. However, this is, of course, only pertinent to SCROLL cursors.