Re: [HACKERS] Options for protocol level cursors
James William Pye [EMAIL PROTECTED] 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Options for protocol level cursors
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 versions of 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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Options for protocol level cursors
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Options for protocol level cursors
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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] 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? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Options for protocol level cursors
James William Pye [EMAIL PROTECTED] 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Options for protocol level cursors
On Jun 12, 2008, at 10:08 AM, Tom Lane wrote: James William Pye [EMAIL PROTECTED] 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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Options for protocol level cursors
James William Pye [EMAIL PROTECTED] writes: On Jun 12, 2008, at 10:08 AM, Tom Lane wrote: James William Pye [EMAIL PROTECTED] 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Options for protocol level cursors
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 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? 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 :( ] -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Options for protocol level cursors
James William Pye [EMAIL PROTECTED] 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Options for protocol level cursors
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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers