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
On Sun, 12 Sep 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? Sure. I'll wade into the code and see if I can swim. I think I'll first try to implement ALTER TABLE class_name DELETE COLUMN col_name Which version do you suggest I work with to come up with patches? > > 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 D'oh. Now that I think about it you'd need 2n the amount of space anyway... That brings up an intersting point... Does the database do a rollback if it runs out of space on the device? Considering that a vacuum is the only way to reclaim space that should mean that all queries following should fail unless they are selects. -Michael
Michael Richards <miker@scifair.acadiau.ca> writes:
> On Sun, 12 Sep 1999, Tom Lane wrote:
>> That would be a good route to a reimplementation, actually. Want to
>> have a go at it?
> Sure. I'll wade into the code and see if I can swim. I think I'll first
> try to implement ALTER TABLE class_name DELETE COLUMN col_name
> Which version do you suggest I work with to come up with patches?
You should definitely start from current sources, not from REL6_5,
in order to minimize the pain of integrating changes. The tricky call
here is how often to update your copy of current --- rebuilding every
day is just a time sink, but if you let your copy get too far out of
date then you have problems merging what you've done. One possibility
is to keep an eye on the cvs-committers digest, and update(+ merge
changes) whenever someone commits changes in the same files that you've
got changes to.
> D'oh. Now that I think about it you'd need 2n the amount of space
> anyway... That brings up an intersting point... Does the database do a
> rollback if it runs out of space on the device?
I think it rolls back OK if we fail to acquire a new page for a user
table. Failing to write a pg_log page might be disastrous though.
Vadim would understand that better than I do.
regards, tom lane
Tom Lane wrote: > > > anyway... That brings up an intersting point... Does the database do a > > rollback if it runs out of space on the device? > > I think it rolls back OK if we fail to acquire a new page for a user > table. Failing to write a pg_log page might be disastrous though. > Vadim would understand that better than I do. There should be no problem with this too. Vadim