Re: Writeable CTEs and empty relations

Marko Tiikkaja <marko(dot)tiikkaja(at)cs(dot)helsinki(dot)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.

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.

Attached is the patch as I had it before giving up (sans documentation
since I'd not gotten to that yet). The main other change from what
you submitted was adding ruleutils.c support.

regards, tom lane