pg.ddx.io pgsql-hackers@postgresql.org mailing list archive
help / color / mirror / Atom feedUpdatable cursor doubt
5+ messages / 5 participants
[nested] [flat]
* Updatable cursor doubt
@ 2007-09-04 01:44 FAST PostgreSQL <fastpgs@fast.fujitsu.com.au>
2007-09-04 09:16 ` Re: Updatable cursor doubt Heikki Linnakangas <heikki@enterprisedb.com>
0 siblings, 1 reply; 5+ messages in thread
From: FAST PostgreSQL @ 2007-09-04 01:44 UTC (permalink / raw)
To: pgsql-hackers
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Updatable cursor doubt
2007-09-04 01:44 Updatable cursor doubt FAST PostgreSQL <fastpgs@fast.fujitsu.com.au>
@ 2007-09-04 09:16 ` Heikki Linnakangas <heikki@enterprisedb.com>
2007-09-04 13:38 ` Re: Updatable cursor doubt Kevin Grittner <Kevin.Grittner@wicourts.gov>
2007-09-05 00:37 ` Re: Updatable cursor doubt Josh Berkus <josh@agliodbs.com>
0 siblings, 2 replies; 5+ messages in thread
From: Heikki Linnakangas @ 2007-09-04 09:16 UTC (permalink / raw)
To: FAST PostgreSQL <fastpgs@fast.fujitsu.com.au>; +Cc: pgsql-hackers
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Updatable cursor doubt
2007-09-04 01:44 Updatable cursor doubt FAST PostgreSQL <fastpgs@fast.fujitsu.com.au>
2007-09-04 09:16 ` Re: Updatable cursor doubt Heikki Linnakangas <heikki@enterprisedb.com>
@ 2007-09-04 13:38 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
1 sibling, 0 replies; 5+ messages in thread
From: Kevin Grittner @ 2007-09-04 13:38 UTC (permalink / raw)
To: Heikki Linnakangas <heikki@enterprisedb.com>; FAST PostgreSQL <fastpgs@fast.fujitsu.com.au>; +Cc: pgsql-hackers
>>> 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> ] }
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Updatable cursor doubt
2007-09-04 01:44 Updatable cursor doubt FAST PostgreSQL <fastpgs@fast.fujitsu.com.au>
2007-09-04 09:16 ` Re: Updatable cursor doubt Heikki Linnakangas <heikki@enterprisedb.com>
@ 2007-09-05 00:37 ` Josh Berkus <josh@agliodbs.com>
2007-09-05 01:12 ` Re: Updatable cursor doubt Gregory Stark <stark@enterprisedb.com>
1 sibling, 1 reply; 5+ messages in thread
From: Josh Berkus @ 2007-09-05 00:37 UTC (permalink / raw)
To: pgsql-hackers; +Cc: Heikki Linnakangas <heikki@enterprisedb.com>; FAST PostgreSQL <fastpgs@fast.fujitsu.com.au>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Updatable cursor doubt
2007-09-04 01:44 Updatable cursor doubt FAST PostgreSQL <fastpgs@fast.fujitsu.com.au>
2007-09-04 09:16 ` Re: Updatable cursor doubt Heikki Linnakangas <heikki@enterprisedb.com>
2007-09-05 00:37 ` Re: Updatable cursor doubt Josh Berkus <josh@agliodbs.com>
@ 2007-09-05 01:12 ` Gregory Stark <stark@enterprisedb.com>
0 siblings, 0 replies; 5+ messages in thread
From: Gregory Stark @ 2007-09-05 01:12 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: pgsql-hackers; Heikki Linnakangas <heikki@enterprisedb.com>; FAST PostgreSQL <fastpgs@fast.fujitsu.com.au>
"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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2007-09-05 01:12 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-09-04 01:44 Updatable cursor doubt FAST PostgreSQL <fastpgs@fast.fujitsu.com.au>
2007-09-04 09:16 ` Heikki Linnakangas <heikki@enterprisedb.com>
2007-09-04 13:38 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2007-09-05 00:37 ` Josh Berkus <josh@agliodbs.com>
2007-09-05 01:12 ` Gregory Stark <stark@enterprisedb.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox