Re: [HACKERS] DECLARE CURSOR must not contain data-modifying statements in WITH
On Wed, Sep 21, 2011 at 12:19 PM, Andres Freund and...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 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 and...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] DECLARE CURSOR must not contain data-modifying statements in WITH
On Fri, Sep 23, 2011 at 10:53 AM, Andres Freund and...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] 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 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. Thanks, Andres -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
