On Fri, Jun 3, 2011 at 10:42 AM, Jehan-Guillaume (ioguix) de Rorthais
<jgdr@dalibo.com> wrote:
>
> The following bug has been logged online:
>
> Bug reference: =A0 =A0 =A06051
> Logged by: =A0 =A0 =A0 =A0 =A0Jehan-Guillaume (ioguix) de Rorthais
> Email address: =A0 =A0 =A0jgdr@dalibo.com
> PostgreSQL version: 9.1beta1
> Operating system: =A0 Linux x86_64
> Description: =A0 =A0 =A0 =A0wCTE query fail with wrong error text on a ta=
ble with
> rules
> Details:
>
> While testing wCTE, I tried to empty a parent table, feeding its child us=
ing
> rules with this kind of query:
>
> =A0WITH t1 AS (
> =A0 =A0DELETE FROM ONLY test RETURNING *
> =A0)
> =A0INSERT INTO test SELECT * FROM t1;
>
> It works perfectly on a table without rules, but fail with what seems lik=
e a
> non related error message if there is a rule on INSERT on this table:
>
> =A0test=3D# WITH t1 AS (
> =A0 =A0DELETE FROM ONLY test RETURNING *
> =A0)
> =A0INSERT INTO test SELECT * FROM t1;
> =A0ERROR: =A0could 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:
>
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D>
> $ createdb test
>
> $ psql test
> psql (9.1beta1)
> Type "help" for help.
>
> test=3D# SELECT version();
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0version
>
> -------------------------------------------------------------------------=
---
> --------------------------------
> =A0PostgreSQL 9.1beta1 on x86_64-unknown-linux-gnu, compiled by gcc (GCC)
> 4.6.0 20110513 (prerelease), 64-bit
> (1 row)
>
> test=3D# CREATE table test AS select i from generate_series(1,3) as t(i);
> SELECT 3
> test=3D# SELECT * FROM test;
> =A0i
> ---
> =A01
> =A02
> =A03
> (3 rows)
>
> test=3D# WITH t1 AS (
> test(# DELETE FROM ONLY test RETURNING *
> test(# )
> test-# INSERT INTO test SELECT * FROM t1;
> INSERT 0 3
> test=3D# SELECT * FROM test;
> =A0i
> ---
> =A01
> =A02
> =A03
> (3 rows)
>
> test=3D# CREATE TABLE test2 () inherits (test);
> CREATE TABLE
> test=3D# CREATE RULE test_ins AS ON INSERT TO test DO INSTEAD INSERT INTO
> test2 VALUES (NEW.i);
> CREATE RULE
> test=3D# WITH t1 AS (
> DELETE FROM ONLY test RETURNING *
> )
> INSERT INTO test SELECT * FROM t1;
> ERROR: =A0could not find CTE "t1"
> <=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
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