Обсуждение: Why isn't DECLARE CURSOR ... FOR UPDATE supported?

Is there any good reason for this restriction?

regression=# begin;
BEGIN
regression=# declare c cursor for select * from tenk1 for update;
ERROR:  DECLARE CURSOR ... FOR UPDATE is not supported
DETAIL:  Cursors must be READ ONLY.

While I have not tried it, I think that simply removing this error check
in PerformCursorOpen() would allow the system to behave in a reasonable
way, ie, locking each row the first time it is fetched through the
cursor.

A recent conversation on pgsql-bugs led me to think of this as an easy
way to get the effect of "LIMIT after FOR UPDATE" --- that is, you
declare the cursor as above and then FETCH just one row, or however many
you need.  With the current implementation in which LIMIT acts before
FOR UPDATE, it's possible the "SELECT ... LIMIT 1 FOR UPDATE" will
return no row, even though lockable rows exist in the table.
        regards, tom lane

On Thu, 2003-12-18 at 10:20, Tom Lane wrote:
> Is there any good reason for this restriction?
> 
> regression=# begin;
> BEGIN
> regression=# declare c cursor for select * from tenk1 for update;
> ERROR:  DECLARE CURSOR ... FOR UPDATE is not supported
> DETAIL:  Cursors must be READ ONLY.
> 
> While I have not tried it, I think that simply removing this error check
> in PerformCursorOpen() would allow the system to behave in a reasonable
> way, ie, locking each row the first time it is fetched through the
> cursor.

The help implies you can.

Command:     DECLARE
Description: define a cursor
Syntax:
DECLARE name [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ]   CURSOR [ { WITH | WITHOUT } HOLD ] FOR query   [ FOR { READ
ONLY| UPDATE [ OF column [, ...] ] } ]
 

Rod Taylor <pg@rbt.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

On Thu, 18 Dec 2003, Tom Lane wrote:

> Rod Taylor <pg@rbt.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...

I was going to look at it for 7.5. However, we don't have column locks
:-(.

Thanks,

Gavin

> -----Original Message-----
> From: Tom Lane
> 
> Is there any good reason for this restriction?
> 
> regression=# begin;
> BEGIN
> regression=# declare c cursor for select * from tenk1 for update;
> ERROR:  DECLARE CURSOR ... FOR UPDATE is not supported
> DETAIL:  Cursors must be READ ONLY.

Because we haven't supported updatable cursors yet.

regards,
Hiroshi Inoue

Tom Lane wrote:

>Rod Taylor <pg@rbt.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/

Shachar Shemesh <psql@shemesh.biz> writes:
> Do I understand from what you are saying that we are pretty close to 
> being able to perform write operations on cursors?

No, I didn't say that.
        regards, tom lane