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

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

From: Merlin Moncure <mmoncure(at)gmail(dot)com>
To: "Jehan-Guillaume (ioguix) de Rorthais" <jgdr(at)dalibo(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #6051: wCTE query fail with wrong error text on a table with rules
Date: 2011-06-03 19:57:00
Message-ID: BANLkTim4L2q_4DC4xc1J5YuEprbBFfZ3mg@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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

In response to

Responses

pgsql-bugs by date

Next:From: Alvaro HerreraDate: 2011-06-03 20:03:03
Subject: Re: BUG #6050: Dump and restore of view after a schema change: can't restore the view
Previous:From: Tom LaneDate: 2011-06-03 18:21:25
Subject: Re: BUG #6050: Dump and restore of view after a schema change: can't restore the view

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