Thread: Running queries on inherited tables
Hi. I was fooling with inheritance today. From the page at: http://www.postgresql.org/docs/user/inherit.htm It says: Here the * after cities indicates that the query should be run over cities and all classes below cities in the inheritance hierarchy. Many of the commands that we have already discussed -- SELECT, UPDATE and DELETE -- support this * notation, as do others, like ALTER TABLE. So here's what I tried using the example data... miker=> CREATE TABLE cities ( miker-> name text, miker-> population float, miker-> altitude int -- (in ft) miker-> ); CREATE miker=> miker=> CREATE TABLE capitals ( miker-> state char(2) miker-> ) INHERITS (cities); CREATE miker=> insert into cities (name,altitude) VALUES ('Wolfville',69); INSERT 160729 1 miker=> insert into capitals (name,altitude,state) VALUES ('Halifax',455,'NS'); INSERT 160730 1 miker=> select * from cities*; name |population|altitude ---------+----------+-------- Wolfville| | 69 Halifax | | 455 (2 rows) miker=> update cities* set population=222; ERROR: parser: parse error at or near "*" I've tried a number of variations on the cities* thing but can only make it for for select. Is this a bug? More playing followed... If I alter table on cities and add a column, is it not expected that the additional col should appear in the tables which inherit from cities? miker=> alter table cities add column niceplace bool; ADD miker=> select * from cities; name |population|altitude|niceplace ---------+----------+--------+--------- Wolfville| | 69| (1 row) miker=> select * from cities*; name |population|altitude|niceplace ---------+----------+--------+--------- Wolfville| | 69| Halifax | | 455|t (2 rows) miker=> select niceplace from capitals; ERROR: attribute 'niceplace' not found miker=> \d capitals; Table = capitals +-----------------------------+----------------------------------+-------+ | Field | Type | Length| +-----------------------------+----------------------------------+-------+ | name | text | var | | population | float8 | 8 | | altitude | int4 | 4 | | state | char() | 2 | +-----------------------------+----------------------------------+-------+ Something is positively b0rked here.... Halifax is showing up as having niceplace=true, yet according to the next select, it doesn't have a column of that name... I'm running 6.5.1. If this is not an error on my part, any people can't reproduce it, I'll submit a bug report... -Michael
Michael Richards <miker@scifair.acadiau.ca> writes: > I was fooling with inheritance today. From the page at: > http://www.postgresql.org/docs/user/inherit.htm > Here the * after cities indicates that the query should be run over cities > and all classes below cities in the inheritance hierarchy. Many of the > commands that we have > already discussed -- SELECT, UPDATE and DELETE -- support this * notation, > as do others, like ALTER TABLE. The doc is wrong here --- UPDATE and DELETE don't support *. They should. > More playing followed... If I alter table on cities and add a column, is > it not expected that the additional col should appear in the tables which > inherit from cities? You have to say "alter table cities*", I believe, otherwise only cities is changed. Which is pretty broken --- if inheritance means anything, then it ought to mean that the alteration is *inherently* applied to all the child tables too, and you shouldn't have the option. In general, however, beware that alteration of inheritance structures is pretty thoroughly broken --- see various complaints in the pghackers archives (mostly from Chris Bitmead, I think). ALTER TABLE really needs a reimplementation from the ground up, but I dunno when anyone will get around to it. regards, tom lane
On Sun, 12 Sep 1999, Tom Lane wrote: > You have to say "alter table cities*", I believe, otherwise only cities > is changed. Which is pretty broken --- if inheritance means anything, > then it ought to mean that the alteration is *inherently* applied to all > the child tables too, and you shouldn't have the option. In general, Would this be a simple change in parsing the statement to see if it has any children and translate the statement accordingly? > (mostly from Chris Bitmead, I think). ALTER TABLE really needs a > reimplementation from the ground up, but I dunno when anyone will get Considering how often Alter table is used, would it be reasonable to rip out all the alter table code and just have it do a select into;drop;rename that would be nice in that dropping/adding columns would be easy, inheritance would (should) be preserved and it's simple. Of course I wouldn't want to do this on a 5Gb table... -Michael
Michael Richards <miker@scifair.acadiau.ca> writes:
> On Sun, 12 Sep 1999, Tom Lane wrote:
>> You have to say "alter table cities*", I believe, otherwise only cities
>> is changed. Which is pretty broken --- if inheritance means anything,
>> then it ought to mean that the alteration is *inherently* applied to all
>> the child tables too, and you shouldn't have the option.
> Would this be a simple change in parsing the statement to see if it has
> any children and translate the statement accordingly?
Yes, I think it would be a reasonably localized change, assuming that
no one objected. (I suppose somewhere out there is someone who thinks
the current behavior is a good idea ;-).)
>> (mostly from Chris Bitmead, I think). ALTER TABLE really needs a
>> reimplementation from the ground up, but I dunno when anyone will get
> Considering how often Alter table is used, would it be reasonable to rip
> out all the alter table code and just have it do a select into;drop;rename
That would be a good route to a reimplementation, actually. Want to
have a go at it?
> Of course I wouldn't want to do this on a 5Gb table...
There's probably not much choice. The current implementation avoids
touching the data at all, but that is precisely the source of most of
its bugs and limitations. I think most of the cases that we currently
can't handle would involve changing all the tuples, and at that point
select-into-a-new-table is probably really the preferred technique
compared to trying to do it in-place. (In-place, you'd have to do a
VACUUM to get back the extra 5Gb after the transformation is done,
since you surely don't want to overwrite the old tuples before commit.)
regards, tom lane
At 02:00 +0200 on 13/09/1999, Tom Lane wrote: > > Considering how often Alter table is used, would it be reasonable to rip > > out all the alter table code and just have it do a select into;drop;rename > > That would be a good route to a reimplementation, actually. Want to > have a go at it? OIDs of all rows will be changed, won't they? Some people rely on OIDs in their applications. Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma