Re: Why isn't DECLARE CURSOR ... FOR UPDATE supported?

Tom Lane wrote:

>Rod Taylor <pg(at)rbt(dot)ca> writes:
>
>
>>On Thu, 2003-12-18 at 10:20, Tom Lane wrote:
>>
>>
>>>Is there any good reason for this restriction?
>>>
>>>
>
>
>
>>The help implies you can.
>>
>>
>
>
>
>>DECLARE name [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ]
>> CURSOR [ { WITH | WITHOUT } HOLD ] FOR query
>> [ FOR { READ ONLY | UPDATE [ OF column [, ...] ] } ]
>>
>>
>
>Hmm. Actually that is describing the SQL spec's syntax for DECLARE
>CURSOR, in which you can name specific *columns* not tables as being
>updatable through the cursor. Now that I think about it, the error
>check is probably there to catch anyone who writes "FOR UPDATE OF
>column" expecting to get the SQL spec behavior.
>
>I'm not sure whether anyone is planning to try to converge our notion of
>FOR UPDATE with the spec's. If that is going to happen someday, it'd
>probably be best not to introduce directly conflicting behavior into
>DECLARE CURSOR. Oh well...
>
> regards, tom lane
>
>
>
Do I understand from what you are saying that we are pretty close to
being able to perform write operations on cursors? Can we, in the mean
while, lock entire rows for that purpose?

I'm having a deployment of Postgresql where the application is using MFC
CRecordset. It appears, from superficial inspection, that it uses the
same type of cursor, whether it actually intends to update it or not. I
have also not found any convinent way in MFC to tell it which rows one
intends to update. As such, I suspect it is prepared to update them all
(disclaimer - I have not tested it myself, and may speak utter bullshit
here).

The problem is that, at the moment, the ODBC driver is emulating cursors
by using the OID field, and performing seperate queries per row. This
has two significant problems:
A. One cannot create a read-write cursor for views, as views do not have
an OID field.
B. The performance for fetching 30,000 rows is terrible.

I'm looking for a way to solve these issues (especially the second one).
I may have a solution inside the ODBC driver itself (better cursors
emulation - a performance/memory tradeoff), but I would really prefer a
true solution to the problem.

My question is this - how terrible will it be if we did not lock each
individual column, but instead locked entire rows (as Tom suggested in
the begining of this thread)?

Shachar

--
Shachar Shemesh
Open Source integration & consulting
Home page & resume - http://www.shemesh.biz/