Обсуждение: DECLARE CURSOR must not contain data-modifying statements in WITH
Hi all,
Whats the reason for disallowing cursors on wCTEs? I am not sure I can follow
the comment:
/* * We also disallow data-modifying WITH in a cursor. (This could be * allowed, but the semantics of when the updates
occurmight 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.
Thanks,
Andres
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