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

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

From: "Jehan-Guillaume (ioguix) de Rorthais" <jgdr(at)dalibo(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #6051: wCTE query fail with wrong error text on a table with rules
Date: 2011-06-03 15:42:13
Message-ID: 201106031542.p53FgDh5073991@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
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"
<======================

Responses

pgsql-bugs by date

Next:From: Robert HaasDate: 2011-06-03 16:44:45
Subject: Re: BUG #6041: Unlogged table was created bad in slave node
Previous:From: Tom LaneDate: 2011-06-03 14:59:00
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