Re: Writeable CTEs and empty relations - Mailing list pgsql-hackers

On Fri, Feb 12, 2010 at 8:06 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Marko Tiikkaja <marko.tiikkaja@cs.helsinki.fi> writes:
>>> Here's the patch.  It's the same as the stuff in writeable CTE patches,
>>> but I added regression tests.
>
>> Whoops.  The reference section in docs still had some traces of writeable
>> CTEs.  Updated patch attached.
>
> I spent some time playing with this but concluded that it's not
> committable.  I ran into two significant problems:
>
> 1. In an INSERT statement, it's already possible to attach a WITH to
> the contained statement, ie
>        INSERT INTO foo WITH ... SELECT ...
>        INSERT INTO foo WITH ... VALUES (...)
> and the patch wasn't doing anything nice with the case where one tries
> to put WITH at both places:
>        WITH ... INSERT INTO foo WITH ... VALUES (...)
> (The SELECT case actually works, mostly, but the VALUES one doesn't.)
> I thought about just concat'ing the two WITH lists but this introduces
> various strange corner cases; in particular when one list is marked
> RECURSIVE and the other isn't there's no way to avoid surprising
> behavior.  However, since the option for an inner WITH already does
> everything you would want, we could just forget about adding outer WITH
> for INSERT.  The attached modified patch does that.

That seems reasonable, though we might want to document that somehow
for posterity.

By the way, are we going to support WITH ... TABLE?

> 2. None of the cases play nicely with NEW or OLD references in a rule.
> For example,
>
> regression=#  create temp table x(f1 int);
> CREATE TABLE
> regression=#  create temp table y(f2 int);
> CREATE TABLE
> regression=# create rule r2 as on update to x do instead
> with t as (select old.*) update y set f2 = t.f1 from t;
> CREATE RULE
> regression=# update x set f1 = f1+1;
> ERROR:  bogus local parameter passed to WITH query
> regression=#
>
> I don't see any very nice way to fix this.  The problem is that the
> NEW or OLD reference is treated as though it were a relation of the
> main query (the UPDATE in this case), which is something that's not
> valid to reference in a WITH query.  I'm afraid that it might not
> be possible to fix it without significant changes in the way rules
> work (and consequent compatibility issues).
>
> We could possibly put in some hack to disallow OLD/NEW references in
> the WITH queries, but that got past my threshold of ugliness, so
> I'm not going to commit it without further discussion.

On the face of it it's not obvious to me why you wouldn't just do
that.  If it's not valid to reference them there, then just don't let
it happen (now comes the part where you tell me why it's not that
simple).

...Robert