The following bug has been logged online:
Bug reference: 6051
Logged by: Jehan-Guillaume (ioguix) de Rorthais
Email address: jgdr@dalibo.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"
<======================
