Skip site navigation (1) Skip section navigation (2)

DECLARE CURSOR must not contain data-modifying statements in WITH

From: Andres Freund <andres(at)anarazel(dot)de>
To: PG Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: DECLARE CURSOR must not contain data-modifying statements in WITH
Date: 2011-09-21 16:19:43
Message-ID: 201109211819.44027.andres@anarazel.de (view raw or flat)
Thread:
Lists: pgsql-hackers
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


Responses

pgsql-hackers by date

Next:From: Euler Taveira de OliveiraDate: 2011-09-21 16:22:22
Subject: Re: Hot Backup with rsync fails at pg_clog if under load
Previous:From: Heikki LinnakangasDate: 2011-09-21 16:13:20
Subject: Re: Inlining comparators as a performance optimisation

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group