Re: wCTE behaviour

On 11 November 2010 16:50, Marko Tiikkaja <marko(dot)tiikkaja(at)cs(dot)helsinki(dot)fi>wrote:

> On 2010-11-11 6:41 PM +0200, David Fetter wrote:
>
>> On Thu, Nov 11, 2010 at 04:15:34AM +0200, Marko Tiikkaja wrote:
>>
>>> The discussion around wCTE during the last week or so has brought to
>>> my attention that we don't actually have a consensus on how exactly
>>> wCTEs should behave. The question seems to be whether or not a
>>> statement should see the modifications of statements ran before it.
>>> While I think making the modifications visible would be a lot more
>>> intuitive, it's not clear how we'd optimize the execution in the
>>> future without changing the behaviour (triggers are a big concern).
>>>
>>
>> +1 for letting writeable CTEs see the results of previous CTEs, just
>> as current non-writeable ones do. A lot of the useful cases for this
>> feature depend on this visibility.
>>
>
> Just to be clear, the main point is whether they see the data modifications
> or not. The simplest case to point out this behaviour is:
>
> WITH t AS (DELETE FROM foo)
> SELECT * FROM foo;
>
> And the big question is: what state of "foo" should the SELECT statement
> see?
>
>
I would expect that select to return nothing. And if the user wished to
reference what was deleted, they could use RETURNING anyway. </probable
ignorance>

WITH t AS (UPDATE foo SET col = true)
SELECT * FROM foo WHERE col = false;

... Wouldn't this be more practical to have foo's UPDATEs applied prior to
SELECT? Otherwise what would the usecase be?

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935