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

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