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

Re: wCTE: why not finish sub-updates at the end, not the beginning?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: David Fetter <david(at)fetter(dot)org>
Cc: Marko Tiikkaja <marko(dot)tiikkaja(at)cs(dot)helsinki(dot)fi>, pgsql-hackers(at)postgreSQL(dot)org
Subject: Re: wCTE: why not finish sub-updates at the end, not the beginning?
Date: 2011-02-26 03:57:24
Message-ID: 3451.1298692644@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
David Fetter <david(at)fetter(dot)org> writes:
> Sorry that was unclear.  Let's imagine there's a DELETE ... RETURNING
> in one WITH, and an UPDATE in another that depends on that one.  Is
> that still allowed?

Yeah it is, although I just noticed that there's a bug in the new
implementation:

with t1 as (insert into x select ... returning *),
     t2 as (insert into y select * from t1 returning *)
select 1;

This should result in the same rows inserted into both x and y, but in
git HEAD it fails to insert anything into y.  The reason is that the
ExecutorEnd scan first processes the ModifyTable node for x, and cycles
it to completion, discarding the results --- but we needed the CteScan
in t2 to see those rows.  There's a related case in the regression
tests, but it works because the outer query does fetch from both WITH
clauses, so there's no need to do anything at ExecutorEnd time.

The first solution that comes to mind is to pay attention to the
interdependencies of the CTEs, and perform the cleanup in an appropriate
order (here, the ModifyTable for y needs to be cycled first).  I'm not
sure if there's a nicer way.  We'll eventually want some interdependency
tracking for CTEs anyway, if we're ever to support mutual recursion,
so it'd not be completely single-purpose code.

			regards, tom lane

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2011-02-26 04:55:43
Subject: Re: wCTE: why not finish sub-updates at the end, not the beginning?
Previous:From: David FetterDate: 2011-02-26 03:16:36
Subject: Re: wCTE: why not finish sub-updates at the end, not the beginning?

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