Rod Taylor <[EMAIL PROTECTED]> writes: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?
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
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/
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
