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

wCTE cannot be used to update parent inheritance table

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: wCTE cannot be used to update parent inheritance table
Date: 2012-01-25 23:47:50
Message-ID: 4F2094A6.5090505@agliodbs.com (view raw or flat)
Thread:
Lists: 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

Responses

pgsql-bugs by date

Next:From: Eric BortsDate: 2012-01-26 00:36:01
Subject: Windows x86-64 One-Click Install (9.1.2-1, 9.0.6-1) hangs on "initialising the database cluster" (with work-around)
Previous:From: Tom LaneDate: 2012-01-25 22:13:44
Subject: Re: Segfault in backend CTE code

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