wCTE cannot be used to update parent inheritance table - Mailing list pgsql-bugs

SEVERITY: normal

TYPE: SQL feature

VERSION TESTED: 9.1.2

PLATFORM: Ubuntu Linux, installed from apt-get

REPRODUCEABLE: 100%

SUMMARY: if you attempt to UPDATE or DELETE FROM a parent table in an
inheritance relationship using a wCTE, you get the following error message:

    ERROR:  could not find plan for CTE

This does not happen with INSERTs, child tables or UPDATE ONLY.

STEPS TO REPRODUCE:

create table parent ( id int, val text );

create table child1 ( constraint child1_part check ( id between 1 and 5
) ) inherits ( parent );

create table child2 ( constraint child2_part check ( id between 6 and 10
) ) inherits ( parent );

create table other_table ( whichtab text, totals int );

postgres=# insert into child1 values ( 1, 'one' ),( 2, 'two' );
INSERT 0 2
postgres=# insert into child2 values ( 6, 'six' ),( 7, 'seven' );
INSERT 0 2
postgres=# with wcte as ( select sum(id) as totalid from parent ) insert
into other_table select 'parent', totalid from wcte;
INSERT 0 1
postgres=# with wcte as ( select sum(id) as totalid from child1 ) insert
into other_table select 'parent', totalid from wcte;
INSERT 0 1
postgres=# with wcte as ( select whichtab from other_table ) update
child1 set val = whichtab from other_table;
UPDATE 2
postgres=# with wcte as ( select whichtab from other_table ) update
parent set val = whichtab from other_table;
UPDATE 4
postgres=# with wcte as ( select whichtab from other_table ) update
child1 set val = whichtab from wcte;
UPDATE 2


postgres=# with wcte as ( select whichtab from other_table ) update
parent set val = whichtab from wcte;
ERROR:  could not find plan for CTE "wcte"


postgres=# with wcte as ( select whichtab from other_table ) update only
parent set val = whichtab from wcte;
UPDATE 0
postgres=# update parent set val = 'parent';
UPDATE 4
postgres=# with wcte as ( select whichtab from other_table ) insert into
parent select 11, whichtab from other_table;
INSERT 0 2


postgres=# with wcte as ( select whichtab from other_table ) delete from
parent using wcte where val = whichtab;
ERROR:  could not find plan for CTE "wcte"

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com