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

On Fri, Jun 3, 2011 at 10:42 AM, Jehan-Guillaume (ioguix) de Rorthais
<jgdr(at)dalibo(dot)com> wrote:
>
> The following bug has been logged online:
>
> Bug reference:      6051
> Logged by:          Jehan-Guillaume (ioguix) de Rorthais
> Email address:      jgdr(at)dalibo(dot)com
> PostgreSQL version: 9.1beta1
> Operating system:   Linux x86_64
> Description:        wCTE query fail with wrong error text on a table with
> rules
> Details:
>
> While testing wCTE, I tried to empty a parent table, feeding its child using
> rules with this kind of query:
>
>  WITH t1 AS (
>    DELETE FROM ONLY test RETURNING *
>  )
>  INSERT INTO test SELECT * FROM t1;
>
> It works perfectly on a table without rules, but fail with what seems like a
> non related error message if there is a rule on INSERT on this table:
>
>  test=# WITH t1 AS (
>    DELETE FROM ONLY test RETURNING *
>  )
>  INSERT INTO test SELECT * FROM t1;
>  ERROR:  could not find CTE "t1"
>
> I was expecting this query either to work or raise a comprehensive error
> message.
>
> Here is a simple script to reproduce this behaviour:
>
> ======================>
> $ createdb test
>
> $ psql test
> psql (9.1beta1)
> Type "help" for help.
>
> test=# SELECT version();
>                                                  version
>
> ----------------------------------------------------------------------------
> --------------------------------
>  PostgreSQL 9.1beta1 on x86_64-unknown-linux-gnu, compiled by gcc (GCC)
> 4.6.0 20110513 (prerelease), 64-bit
> (1 row)
>
> test=# CREATE table test AS select i from generate_series(1,3) as t(i);
> SELECT 3
> test=# SELECT * FROM test;
>  i
> ---
>  1
>  2
>  3
> (3 rows)
>
> test=# WITH t1 AS (
> test(# DELETE FROM ONLY test RETURNING *
> test(# )
> test-# INSERT INTO test SELECT * FROM t1;
> INSERT 0 3
> test=# SELECT * FROM test;
>  i
> ---
>  1
>  2
>  3
> (3 rows)
>
> test=# CREATE TABLE test2 () inherits (test);
> CREATE TABLE
> 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).

merlin