Re: Writeable CTEs and empty relations

On Wed, Feb 10, 2010 at 5:05 AM, Marko Tiikkaja
<marko(dot)tiikkaja(at)cs(dot)helsinki(dot)fi> wrote:
> On 2010-02-10 03:20 +0200, Tom Lane wrote:
>> Marko Tiikkaja <marko(dot)tiikkaja(at)cs(dot)helsinki(dot)fi> writes:
>>> On 2010-02-10 02:19 +0200, Tom Lane wrote:
>>>> You still haven't explained why it's a good idea to change the snapshot
>>>> after the executor has started.  Right at the moment I'm prepared to
>>>> reject the patch on that ground alone.
>>
>>> The patch only touches the snapshot's curcid.  That's needed to allow
>>> the queries see the tuples of the DML WITHs ran before that particular
>>> query.
>>
>> ... and they will also see, for example, tuples inserted by triggers
>> fired by those queries.  I thought the plan for this feature was to
>> store and re-read the RETURNING data, not to go back and re-read the
>> underlying tables.
>
> I originally suggested this approach about four months ago.  During this
> whole time you haven't expressed any concerns about this, but suddenly
> it's a reason to reject the patch?
>
> Anyway, if we want to avoid modifying the snapshot, we can't bump the
> CID between queries.  I haven't thought this through, but it seems like
> this could work.  However, none of the WITH queries can see the previous
> statement's tuples, which means that someone may be suprised when they
> try to modify the same tuples twice just to discover that only the first
> modification took place.  I don't see this as a huge problem though.

I think that we agreed previously that running all the DML queries to
completion before the main query, bumping the CID after each one, and
saving the outputs, was the only workable approach.

http://archives.postgresql.org/pgsql-hackers/2009-10/msg00566.php
http://archives.postgresql.org/pgsql-hackers/2009-10/msg00614.php

If the executor has buried in it the assumption that the snapshot
can't change after startup, then does that mean that we need to start
up and shut down the executor for each subquery?

http://archives.postgresql.org/pgsql-hackers/2009-11/msg01964.php

...Robert