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(at)anarazel(dot)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