Re: Writeable CTEs and side effects

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Jeff Davis <pgsql(at)j-davis(dot)com>
Cc: Peter Eisentraut <peter_e(at)gmx(dot)net>, Marko Tiikkaja <marko(dot)tiikkaja(at)cs(dot)helsinki(dot)fi>, Robert Haas <robertmhaas(at)gmail(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Writeable CTEs and side effects
Date: 2009-10-09 13:35:25
Message-ID: 6804.1255095325@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Jeff Davis <pgsql(at)j-davis(dot)com> writes:
> On Fri, 2009-10-09 at 02:23 +0300, Peter Eisentraut wrote:
>> I think I'd want "writable subqueries" instead of only "writable CTEs".

> I think the original motivation was that it's more clear that a CTE is
> separated and can only be executed once (if it has side effects).
> Depending on how the query is written, it might be less obvious how many
> times the subquery should be executed, and it might change based on the
> plan.

Right. The behavior would be entirely unpredictable, and usually
undesirable, if the RETURNING query is underneath a join, or an
aggregate, or a LIMIT, yadda yadda. Tying it to WITH provides a
convenient way, from both the user-visible and implementation sides,
of saying "this is an independent query that we will execute once
and then make the RETURNING results available for use in this other
query".

regards, tom lane

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2009-10-09 13:37:34 Re: COPY enhancements
Previous Message Albe Laurenz 2009-10-09 11:47:59 Re: Rejecting weak passwords