Re: Modify the DECLARE CURSOR command tag depending on the scrollable flag - Mailing list pgsql-hackers

<div class="moz-cite-prefix">2013-11-27 19:16 keltezéssel, Tom Lane írta:<br /></div><blockquote
cite="mid:19730.1385576177@sss.pgh.pa.us"type="cite"><pre wrap="">Boszormenyi Zoltan <a class="moz-txt-link-rfc2396E"
href="mailto:zb@cybertec.at"><zb@cybertec.at></a>writes:
 
</pre><blockquote type="cite"><pre wrap="">2013-11-23 22:01 keltezéssel, Tom Lane írta:
</pre><blockquote type="cite"><pre wrap="">Boszormenyi Zoltan <a class="moz-txt-link-rfc2396E"
href="mailto:zb@cybertec.at"><zb@cybertec.at></a>writes:
 
</pre><blockquote type="cite"><pre wrap="">Attached is the patch that modified the command tag returned by
the DECLARE CURSOR command. It returns "DECLARE SCROLL CURSOR"
or "DECLARE NO SCROLL CURSOR" depending on the cursor's
scrollable flag that can be determined internally even if neither is
asked explicitly.
</pre></blockquote></blockquote></blockquote><pre wrap="">
</pre><blockquote type="cite"><blockquote type="cite"><pre wrap="">This does not strike me as an acceptable change.  It
willbreak any code
 
that's expecting the existing command tag, for little or no benefit
to most applications.  Even if backwards compatibility were of no concern,
I'm not convinced it's a good thing to expose the backend's internal
choices about query plans used for cursors, which is what this is
basically doing.
</pre></blockquote></blockquote><pre wrap="">
</pre><blockquote type="cite"><pre wrap="">I saw code in the backend allowing a cursor to be scrollable, although
it was not declared as such. How about ripping that out?
</pre></blockquote><pre wrap="">
That also fails the unnecessary-backwards-compatibility-break test.</pre></blockquote><br /> If you read the rest of
themail, it turns out it wasn't a serious question.<br /><br /> Getting the SCROLL / NO SCROLL flags from the
preprocessoris no problem.<br /> The only problem is when it's unspecified.<br /><br /> Treating it as NO SCROLL (or
addingit to the DECLARE command behind<br /> the application's back) would break apps that want to scroll backward.<br
/>(Not ABSOLUTE.)<br /><br /> On the other hand, what problems would one face when adding SCROLL<br /> implicitly if
it'sunspecified? It's not a workable solution either, see below.<br /><br /> As the documentation suggests, an
applicationthat wants to use<br /> UPDATE/DELETE WHERE CURRENT OF ..., it's highly recommended<br /> that the cursor is
fora FOR UPDATE query. Watch this scenario:<br /><br /> zozo=> begin;<br /> BEGIN<br /> zozo=> declare mycur
cursorfor select * from t1 for update;<br /> DECLARE CURSOR<br /> zozo=> fetch from mycur;<br />  id | t <br />
----+---<br/>   1 | a<br /> (1 row)<br /><br /> zozo=> fetch from mycur;<br />  id | t <br /> ----+---<br />   2 |
b<br/> (1 row)<br /><br /> zozo=> update t1 set t=t||'_x' where current of mycur;<br /> UPDATE 1<br /> zozo=>
fetchfrom mycur;<br />  id | t <br /> ----+---<br />   3 | c<br /> (1 row)<br /><br /> zozo=> delete from t1 where
currentof mycur;<br /> DELETE 1<br /> zozo=> move absolute 0 in mycur;<br /> MOVE 0<br /> zozo=> fetch from
mycur;<br/>  id | t <br /> ----+---<br />   1 | a<br /> (1 row)<br /><br /> zozo=> fetch from mycur;<br />  id | t
<br/> ----+---<br /> (0 rows)<br /><br /> Although the server complains about MOVE BACKWARD, it's not<br /> complaining
aboutMOVE ABSOLUTE, despite it's clearly moving<br /> backward. The cursor position is tracked in the backend in a
long<br/> variable and it's not overflowed. This is also legacy behaviour,<br /> changing it would break backward
compatibility.<br/><br /> The other problem I see is with the documentation: it says that<br /> the INSENSITIVE keyword
isjust a placeholder, all cursors are insensitive.<br /> It's clearly false. Moving back to the start, previously
existingrows<br /> won't show up again. It's not strictly a sensitive cursor, either,<br /> because the row with id=2
wouldshow up with the new value of "t".<br /> This neither sensitive nor insensitive behaviour is what the SQL<br />
standardcalls an "asensitive" cursor. It would worth a doc change.<br /> This is what's written in 9.3:<br /><br />
"<br/> If the cursor's query includes <tt class="LITERAL">FOR UPDATE</tt> or <tt class="LITERAL">FOR SHARE</tt>, then
returnedrows are locked at the time they are first fetched, in the same way as for a regular <a
href="http://www.postgresql.org/docs/9.3/interactive/sql-select.html">SELECT</a>command with these options. In
addition,the returned rows will be the most up-to-date versions; therefore these options provide the equivalent of what
theSQL standard calls a <span class="QUOTE">"sensitive cursor"</span>. (Specifying <tt class="LITERAL">INSENSITIVE</tt>
togetherwith <tt class="LITERAL">FOR UPDATE</tt> or <tt class="LITERAL">FOR SHARE</tt> is an error.)<br /> "<br /> ( <a
class="moz-txt-link-freetext"
href="http://www.postgresql.org/docs/9.3/interactive/sql-declare.html">http://www.postgresql.org/docs/9.3/interactive/sql-declare.html</a>
)<br/><br /> However, if the cursor is declared without FOR UPDATE, both<br /> the explicit SCROLL keyword (or
implicit,if the query is simple),<br /> scrolling backward and DML with WHERE CURRENT OF are allowed.<br /> In this
case,the cursor is really insensitive, FETCH statements<br /> after MOVE ABSOLUTE 0 return all rows with their original
data.<br/><br /> This is just to show that adding SCROLL behind the application's<br /> back is also pointless. If the
query(which can also be a prepared<br /> statement in ECPG) contains FOR UPDATE, adding SCROLL to the<br /> DECLARE
statementwould make it fail.<br /><br /> If you consider all these:<br /><br /> - certain combinations of query and
DECLAREstmt flags fail;<br /> - adding NO SCROLL is breaking backward compatibility;<br /> - the readahead code has to
reallyknow whether the cursor is<br />   scrollable so it can behave just like the server;<br /><br /> then returning
theSCROLL / NO SCROLL flag in the command tag is<br /> not a bad solution in my view. In fact, this was the only
workable<br/> solution I could come up with to make it work reliably when neither<br /> SCROLL nor NO SCROLL is
specifiedby the application.<br /><br /> Best regards,<br /> Zoltán Böszörményi<br /><br /><pre class="moz-signature"
cols="90">--
 
----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: <a class="moz-txt-link-freetext" href="http://www.postgresql-support.de">http://www.postgresql-support.de</a>
<aclass="moz-txt-link-freetext" href="http://www.postgresql.at/">http://www.postgresql.at/</a>
 
</pre>