dbTalk Databases Forums  

[BUGS] wCTE cannot be used to update parent inheritance table

Go Back dbTalk Databases Forums Databases mailing.database.pgsql-bugs [BUGS] wCTE cannot be used to update parent inheritance table

mailing.database.pgsql-bugs mailing.database.pgsql-bugs


Discuss [BUGS] wCTE cannot be used to update parent inheritance table in the mailing.database.pgsql-bugs forum.



Reply
Page 1 of 2 1 2 >
 
Thread Tools Display Modes
  #1  
Old   
Josh Berkus
 
Posts: n/a

Default [BUGS] wCTE cannot be used to update parent inheritance table - 01-25-2012 , 05:47 PM





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

--
Sent via pgsql-bugs mailing list (pgsql-bugs (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Reply With Quote
  #2  
Old   
Tom Lane
 
Posts: n/a

Default Re: [BUGS] wCTE cannot be used to update parent inheritance table - 01-28-2012 , 07:27 PM





Josh Berkus <josh (AT) agliodbs (DOT) com> writes:
Quote:
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

--
Sent via pgsql-bugs mailing list (pgsql-bugs (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Reply With Quote
  #3  
Old   
Josh Berkus
 
Posts: n/a

Default Re: [BUGS] wCTE cannot be used to update parent inheritance table - 01-29-2012 , 02:06 PM


On 1/28/12 5:27 PM, Tom Lane wrote:
Quote:
Josh Berkus <josh (AT) agliodbs (DOT) 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

--
Sent via pgsql-bugs mailing list (pgsql-bugs (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Reply With Quote
  #4  
Old   
Peter Geoghegan
 
Posts: n/a

Default Re: [BUGS] wCTE cannot be used to update parent inheritance table - 01-29-2012 , 02:31 PM


On 29 January 2012 20:06, Josh Berkus <josh (AT) agliodbs (DOT) com> wrote:
Quote:
On 1/28/12 5:27 PM, Tom Lane wrote:
Josh Berkus <josh (AT) agliodbs (DOT) 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?
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

--
Sent via pgsql-bugs mailing list (pgsql-bugs (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Reply With Quote
  #5  
Old   
Josh Berkus
 
Posts: n/a

Default Re: [BUGS] wCTE cannot be used to update parent inheritance table - 01-29-2012 , 02:39 PM


Quote:
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

--
Sent via pgsql-bugs mailing list (pgsql-bugs (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Reply With Quote
  #6  
Old   
Peter Geoghegan
 
Posts: n/a

Default Re: [BUGS] wCTE cannot be used to update parent inheritance table - 01-29-2012 , 02:49 PM


On 29 January 2012 20:39, Josh Berkus <josh (AT) agliodbs (DOT) com> wrote:
Quote:
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

--
Sent via pgsql-bugs mailing list (pgsql-bugs (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Reply With Quote
  #7  
Old   
Tom Lane
 
Posts: n/a

Default Re: [BUGS] wCTE cannot be used to update parent inheritance table - 01-29-2012 , 02:52 PM


Peter Geoghegan <peter (AT) 2ndquadrant (DOT) com> writes:
Quote:
On 29 January 2012 20:39, Josh Berkus <josh (AT) agliodbs (DOT) 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

--
Sent via pgsql-bugs mailing list (pgsql-bugs (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Reply With Quote
  #8  
Old   
Tom Lane
 
Posts: n/a

Default Re: [BUGS] wCTE cannot be used to update parent inheritance table - 01-29-2012 , 02:53 PM


Josh Berkus <josh (AT) agliodbs (DOT) com> writes:
Quote:
On 1/28/12 5:27 PM, Tom Lane wrote:
Josh Berkus <josh (AT) agliodbs (DOT) 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

--
Sent via pgsql-bugs mailing list (pgsql-bugs (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Reply With Quote
  #9  
Old   
Peter Geoghegan
 
Posts: n/a

Default Re: [BUGS] wCTE cannot be used to update parent inheritance table - 01-29-2012 , 03:19 PM


On 29 January 2012 20:52, Tom Lane <tgl (AT) sss (DOT) pgh.pa.us> wrote:
Quote:
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 (AT) gmail (DOT) com> wrote:
Quote:
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

--
Sent via pgsql-bugs mailing list (pgsql-bugs (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Reply With Quote
  #10  
Old   
Peter Geoghegan
 
Posts: n/a

Default Re: [BUGS] wCTE cannot be used to update parent inheritance table - 01-29-2012 , 04:11 PM


On 29 January 2012 21:19, Peter Geoghegan <peter (AT) 2ndquadrant (DOT) com> wrote:
Quote:
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 (AT) commandprompt (DOT) com> wrote:
Quote:
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

--
Sent via pgsql-bugs mailing list (pgsql-bugs (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Reply With Quote
Reply
Page 1 of 2 1 2 >

« Previous Thread | Next Thread »



Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.


Contact Us - dbTalk Databases Forums - Archive - Top