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 CTEFixed, 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 message:
ERROR: could not find plan for CTEFixed, 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.
--
Peter Geoghegan 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? 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)
(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.
--
Peter Geoghegan 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. 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.
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?
--
Peter Geoghegan 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> wrote:
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.
--
Peter Geoghegan 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. Please
take it to -hackers.
I suppose you're right, since the first discussion occurred there and
didn't really go anywhere.
--
Peter Geoghegan http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services