Re: "ORDER BY" clause prevents "UPDATE WHERE CURRENT OF"

"Robert Haas" <robertmhaas(at)gmail(dot)com> writes:

> I found the following behavior surprising. Is there a reason for it?
> This is 8.3.5. ...Robert
>
> rhaas=# DECLARE c CURSOR FOR SELECT id FROM test_table ORDER BY foo FOR UPDATE;
> DECLARE CURSOR

Skimming the code we don't support WHERE CURRENT OF on a query which involves
a Sort above the table specified. The way CURRENT OF works depends on the
nature of the plan and depends on there being an active scan of the specified
table. Since sort reads all its inputs in advance that information is lost by
the time the results are output.

If you had an index it would work. That this is tied to the nature of the plan
does seem kind of unfortunate. I'm not sure the alternatives are very
appealing though -- they would involve a lot of code to track a lot more
information for queries that might never need it.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's RemoteDBA services!