Re: wCTE behaviour

<div class="gmail_quote">On 11 November 2010 16:50, Marko Tiikkaja <span dir="ltr"><<a
href="mailto:marko.tiikkaja@cs.helsinki.fi">marko.tiikkaja@cs.helsinki.fi</a>></span>wrote:<br /><blockquote
class="gmail_quote"style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left:
1ex;"><divclass="im">On 2010-11-11 6:41 PM +0200, David Fetter wrote:<br /></div><blockquote class="gmail_quote"
style="margin:0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;"><div class="im"> On
Thu,Nov 11, 2010 at 04:15:34AM +0200, Marko Tiikkaja wrote:<br /></div><div class="im"><blockquote class="gmail_quote"
style="margin:0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;"> The discussion around
wCTEduring the last week or so has brought to<br /> my attention that we don't actually have a consensus on how
exactly<br/> wCTEs should behave.  The question seems to be whether or not a<br /> statement should see the
modificationsof statements ran before it.<br /> While I think making the modifications visible would be a lot more<br
/>intuitive, it's not clear how we'd optimize the execution in the<br /> future without changing the behaviour
(triggersare a big concern).<br /></blockquote><br /> +1 for letting writeable CTEs see the results of previous CTEs,
just<br/> as current non-writeable ones do.  A lot of the useful cases for this<br /> feature depend on this
visibility.<br/></div></blockquote><br /> Just to be clear, the main point is whether they see the data modifications
ornot.  The simplest case to point out this behaviour is:<br /><br /> WITH t AS (DELETE FROM foo)<br /> SELECT * FROM
foo;<br/><br /> And the big question is: what state of "foo" should the SELECT statement see?<br /><br
/></blockquote></div><br/>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><br /><br />WITH t AS (UPDATE foo SET col =
true)<br/> SELECT * FROM foo WHERE col = false;<br /><br />... Wouldn't this be more practical to have foo's UPDATEs
appliedprior to SELECT?  Otherwise what would the usecase be?<br clear="all" /><br />-- <br />Thom Brown<br />Twitter:
@darkixion<br/> IRC (freenode): dark_ixion<br />Registered Linux user: #516935<br />