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

Re: BUG #6051: wCTE query fail with wrong error text on a table with rules

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Merlin Moncure <mmoncure(at)gmail(dot)com>
Cc: "Jehan-Guillaume (ioguix) de Rorthais" <jgdr(at)dalibo(dot)com>, pgsql-bugs(at)postgresql(dot)org, Marko Tiikkaja <marko(dot)tiikkaja(at)cs(dot)helsinki(dot)fi>
Subject: Re: BUG #6051: wCTE query fail with wrong error text on a table with rules
Date: 2011-06-04 21:24:22
Message-ID: 4184.1307222662@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
> On Fri, Jun 3, 2011 at 10:42 AM, Jehan-Guillaume (ioguix) de Rorthais
> <jgdr(at)dalibo(dot)com> wrote:
>> test=# CREATE RULE test_ins AS ON INSERT TO test DO INSTEAD INSERT INTO
>> test2 VALUES (NEW.i);
>> CREATE RULE
>> test=# WITH t1 AS (
>> DELETE FROM ONLY test RETURNING *
>> )
>> INSERT INTO test SELECT * FROM t1;
>> ERROR:  could not find CTE "t1"

> IIRC the fact that rules don't play nice with wCTE was brought up
> several times during the implementation discussions.  I'm not saying
> the error message is great, but you can pretty much add this to the
> giant pile of reasons not to use rules at all (particularly in 9.1
> with the view triggers).

There are definitely cases that don't work, but I had thought we at
least threw an intelligible "not implemented" error for all of them.
This one seems to be an oversight: specifically, rewriteRuleAction()
isn't considering the possibility that the rewritten rule action will
need to make use of CTEs from the original query.

We could paste a copy of the original's cteList into the rule action,
but there are still issues:

* If there's more than one rule action, we could end up executing
multiple copies of the same CTE query; which breaks the expectation
of single evaluation for a CTE.

* If there are CTEs attached to the rule action, as well as to the
original query, and there is a conflict of CTE names between them,
we can't handle that AFAICS.  (The planner expects to look up entries
in a cteList by name...)

* Maybe some other things that aren't obvious yet.

I don't particularly mind throwing a not-implemented error for the first
case (ie, just say multiple rule actions don't mix with CTE queries);
but the second case seems seriously annoying, since there's no way for
someone to write a CTE-containing rule action without risking a
conflict.  Ideas anybody?

			regards, tom lane

In response to

Responses

pgsql-bugs by date

Next:From: ReginaDate: 2011-06-05 19:22:07
Subject: BUG #6053: Can't do DISTINCT on citext column
Previous:From: Tom LaneDate: 2011-06-04 19:50:00
Subject: Re: BUG #6049: Can't load dumped view with VALUES and ORDER BY

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