Updatable cursor doubt

FAST PostgreSQL wrote:

In CVS HEAD

workspace=# begin;
BEGIN
workspace=# declare cu cursor for select * from t1 for read only;
DECLARE CURSOR
workspace=# fetch cu;
a
---
1
(1 row)

workspace=# delete from t1 where current of cu;
DELETE 1
workspace=# commit;
COMMIT

Is this the intended behaviour? If so should we remove the 'READ ONLY'
clause from the allowable syntax?

The documentation does not have 'READ ONLY' as part of the cursor syntax
anymore.

FOR READ ONLY is actually part of the SELECT syntax. It's been accepted
for at least down to version 7.4, probably even longer than that, but it
hasn't been documented. It's accepted for the sake of compatibility with
other DBMSs (and SQL standard?), it doesn't do anything in PostgreSQL.

Now, whether we should make an effort to not allow updating a cursor on
a query with FOR READ ONLY, that's another question. I don't think it's
worth the effort, and it wouldn't really gain us anything. We probably
should mention it in the manual, in the Compatibility section of SELECT
reference page.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On Tue, Sep 4, 2007 at 4:16 AM, in message

<46DD226F.7060602@enterprisedb.com>, "Heikki Linnakangas"
<heikki@enterprisedb.com> wrote:

FOR READ ONLY is actually part of the SELECT syntax.

13.1 <declare cursor>

Function

Define a cursor.

Format

<declare cursor> ::=
DECLARE <cursor name> [ INSENSITIVE ] [ SCROLL ] CURSOR
FOR <cursor specification>

<cursor specification> ::=
<query expression> [ <order by clause> ]
[ <updatability clause> ]

<updatability clause> ::=
FOR { READ ONLY | UPDATE [ OF <column name list> ] }

Heikki,

FOR READ ONLY is actually part of the SELECT syntax. It's been accepted
for at least down to version 7.4, probably even longer than that, but it
hasn't been documented. It's accepted for the sake of compatibility with
other DBMSs (and SQL standard?), it doesn't do anything in PostgreSQL.

Now, whether we should make an effort to not allow updating a cursor on
a query with FOR READ ONLY, that's another question. I don't think it's
worth the effort, and it wouldn't really gain us anything. We probably
should mention it in the manual, in the Compatibility section of SELECT
reference page.

Who are we, MySQL? We ought not to accept the syntax if we're not going
to enforce it.

--
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco

"Josh Berkus" <josh@agliodbs.com> writes:

Who are we, MySQL? We ought not to accept the syntax if we're not going
to enforce it.

I think the thinking is that the syntax doesn't promise anything about
enforcing any restrictions. It's a method for the user to declare what
features he needs. Ie, without that clause (or with a FOR UPDATE?) the
database should signal an error in cases where the cursor won't handle
updates. But with that clause the user is telling us that he's ok with
not being able to update the cursor.

Perhaps a better way to think about this case is "should you raise an
error if someone opens a file in read-only mode when they actually do
have write permission?"

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com