Re: DECLARE CURSOR must not contain data-modifying statements in WITH

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