Thread: Updatable cursor doubt

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.

Rgds,
Arul Shaji

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
<cursorspecification> 
        <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