Thread: wCTE cannot be used to update parent inheritance table
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
Josh Berkus <josh@agliodbs.com> writes:
> 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
Fixed, thanks for the report.
regards, tom lane
On 1/28/12 5:27 PM, Tom Lane wrote: > Josh Berkus <josh@agliodbs.com> writes: >> 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 > > Fixed, thanks for the report. Should we add a regression test for this? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
On 29 January 2012 20:06, Josh Berkus <josh@agliodbs.com> wrote: > On 1/28/12 5:27 PM, Tom Lane wrote: >> Josh Berkus <josh@agliodbs.com> writes: >>> SUMMARY: if you attempt to UPDATE or DELETE FROM a parent table in an >>> inheritance relationship using a wCTE, you get the following error mess= age: >>> =A0 =A0 =A0ERROR: =A0could not find plan for CTE >> >> Fixed, thanks for the report. > > Should we add a regression test for this? This is the kind of thing that could go unnoticed for a long time, simply because it is not highlighted any more prominently than a routine error message like an integrity constraint violation. I continue to maintain that we should have a new severity level for this sort of thing. --=20 Peter Geoghegan =A0 =A0 =A0 http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training and Services
> This is the kind of thing that could go unnoticed for a long time, > simply because it is not highlighted any more prominently than a > routine error message like an integrity constraint violation. I > continue to maintain that we should have a new severity level for this > sort of thing. Huh? I don't follow you at all Peter. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
On 29 January 2012 20:39, Josh Berkus <josh@agliodbs.com> wrote: > >> This is the kind of thing that could go unnoticed for a long time, >> simply because it is not highlighted any more prominently than a >> routine error message like an integrity constraint violation. I >> continue to maintain that we should have a new severity level for this >> sort of thing. > > Huh? =A0I don't follow you at all Peter. I mean that we should change code like this: elog(ERROR, "could not find plan for CTE \"%s\"", rte->ctename) to this: elog(INTERNAL_ERROR, "could not find plan for CTE \"%s\"", rte->ctename) (which would necessitate creating a new severity level, INTERNAL_ERROR). So that DBAs could find these kinds of problems systematically. This is an error message that we expect no one to see. --=20 Peter Geoghegan =A0 =A0 =A0 http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training and Services
Peter Geoghegan <peter@2ndquadrant.com> writes:
> On 29 January 2012 20:39, Josh Berkus <josh@agliodbs.com> wrote:
>> Huh? I don't follow you at all Peter.
> I mean that we should change code like this:
> elog(ERROR, "could not find plan for CTE \"%s\"", rte->ctename)
> to this:
> elog(INTERNAL_ERROR, "could not find plan for CTE \"%s\"", rte->ctename)
Seems like a lot of make-work. The fact that it's got an XX000 SQLSTATE
is already sufficient confirmation that the problem is an internal one,
if the DBA isn't sure about that already.
regards, tom lane
Josh Berkus <josh@agliodbs.com> writes:
> On 1/28/12 5:27 PM, Tom Lane wrote:
>> Josh Berkus <josh@agliodbs.com> writes:
>>> 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
>> Fixed, thanks for the report.
> Should we add a regression test for this?
We did. You could trouble to look at the commit before asking such
questions.
regards, tom lane
On 29 January 2012 20:52, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Seems like a lot of make-work. =A0The fact that it's got an XX000 SQLSTATE > is already sufficient confirmation that the problem is an internal one, > if the DBA isn't sure about that already. I'm not worried about the DBA not being able to figure that out - it seems like they'd stand a pretty good chance of figuring it out quickly once they were aware of the problem. Rather, I share Robert's concern: On 23 November 2011 02:49, Robert Haas <robertmhaas@gmail.com> wrote: > There is no sort of systematic labeling of error messages in the log > to enable the DBA to figure out that the first error message is likely > nothing more serious than an integrity constraint doing its bit to > preserve data integrity, while the second is likely a sign of > impending disaster. Is it really that much of a problem to create a new severity level for this stuff? --=20 Peter Geoghegan =A0 =A0 =A0 http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training and Services
On 29 January 2012 21:19, Peter Geoghegan <peter@2ndquadrant.com> wrote: > Is it really that much of a problem to create a new severity level for > this stuff? I should probably have quoted this refinement, which was part of the discussion that I originally quoted Robert from: On 24 November 2011 16:55, Alvaro Herrera <alvherre@commandprompt.com> wrot= e: > > Excerpts from Robert Haas's message of jue nov 24 13:14:38 -0300 2011: > >> What I think we want to distinguish between is things that are >> PEBKAC/GIGO, and everything else. In other words, if a particular >> error message can be caused by typing something stupid, unexpected, >> erroneous, or whatever into psql, it's just an error. But if no >> input, however misguided, should ever cause that symptom, then it's, I >> don't know what the terminology should be, say, a "severe error". > > +1 I'm strongly in favour of this. --=20 Peter Geoghegan =A0 =A0 =A0 http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training and Services
>>> What I think we want to distinguish between is things that are >>> PEBKAC/GIGO, and everything else. In other words, if a particular >>> error message can be caused by typing something stupid, unexpected, >>> erroneous, or whatever into psql, it's just an error. But if no >>> input, however misguided, should ever cause that symptom, then it's, I >>> don't know what the terminology should be, say, a "severe error". >> >> +1 > > I'm strongly in favour of this. This is *so* not a discussion to have on the pgsql-bugs list. Please take it to -hackers. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
On 29 January 2012 23:47, Josh Berkus <josh@agliodbs.com> wrote: > This is *so* not a discussion to have on the pgsql-bugs list. =A0Please > take it to -hackers. I suppose you're right, since the first discussion occurred there and didn't really go anywhere. --=20 Peter Geoghegan =A0 =A0 =A0 http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training and Services