DECLARE CURSOR must not contain data-modifying statements in WITH

On Wed, Sep 21, 2011 at 12:19 PM, Andres Freund <andres@anarazel.de> wrote:

       /*
        * We also disallow data-modifying WITH in a cursor.  (This could be
        * allowed, but the semantics of when the updates occur might be
        * surprising.)
        */
       if (result->hasModifyingCTE)
               ereport(ERROR,
                               (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
                                errmsg("DECLARE CURSOR must not contain data-modifying
statements in WITH")));

Given that cursors are about the only sensible way to return larger amounts of
data, that behaviour reduces the usefulness of wCTEs a bit.

Whats the exact cause of concern here? I personally don't think there is a
problem documenting that you should fetch the cursor fully before relying on
the updated tables to be in a sensible state. But that may be just me.

Well, it looks like right now you can't even using a simple INSERT ..
RETURNING there:

rhaas=# create table wuzzle (a int);
CREATE TABLE
rhaas=# declare w cursor for insert into wuzzle select g from
generate_series(1, 10) g returning g;
ERROR: syntax error at or near "insert"
LINE 1: declare w cursor for insert into wuzzle select g from genera...
^

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Friday 23 Sep 2011 15:42:48 Robert Haas wrote:

On Wed, Sep 21, 2011 at 12:19 PM, Andres Freund <andres@anarazel.de> wrote:

/*
* We also disallow data-modifying WITH in a cursor. (This could
be * allowed, but the semantics of when the updates occur might be *
surprising.)
*/
if (result->hasModifyingCTE)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("DECLARE CURSOR must not contain
data-modifying statements in WITH")));

Given that cursors are about the only sensible way to return larger
amounts of data, that behaviour reduces the usefulness of wCTEs a bit.

Whats the exact cause of concern here? I personally don't think there is
a problem documenting that you should fetch the cursor fully before
relying on the updated tables to be in a sensible state. But that may be
just me.

Well, it looks like right now you can't even using a simple INSERT ..
RETURNING there:

rhaas=# create table wuzzle (a int);
CREATE TABLE
rhaas=# declare w cursor for insert into wuzzle select g from
generate_series(1, 10) g returning g;
ERROR: syntax error at or near "insert"
LINE 1: declare w cursor for insert into wuzzle select g from genera...

One could argue that its a easier to implement it using a wCTE because the
query will be simply materialize the query upfront.
That makes handling the case where somebody fetches 3 tuples from a query
updating 10 easier.

Thats a bit harder for the normal cursor case because there is no tuplestore
around to do that (except the WITH HOLD case where that is only used on
commit...).

I find it an acceptable way to enforce using a CTE to do cursors on DML because
it makes it more clear that they will be fully executed on start...

Andres

On Fri, Sep 23, 2011 at 10:53 AM, Andres Freund <andres@anarazel.de> wrote:

One could argue that its a easier to implement it using a wCTE because the
query will be simply materialize the query upfront.
That makes handling the case where somebody fetches 3 tuples from a query
updating 10 easier.

Thats a bit harder for the normal cursor case because there is no tuplestore
around to do that (except the WITH HOLD case where that is only used on
commit...).

I find it an acceptable way to enforce using a CTE to do cursors on DML because
it makes it more clear that they will be fully executed on start...

Hmm, maybe. But if that's true, why does the comment read the way it
does? If the updates all occur at the beginning, that wouldn't be
"surprising", would it?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company