Thread

Re: [BUGS] Running queries on inherited tables

Tom Lane <tgl@sss.pgh.pa.us> — 1999-09-12T17:07:37Z

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

Re: [BUGS] Running queries on inherited tables

Michael Richards <miker@scifair.acadiau.ca> — 1999-09-12T18:41:48Z

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

Re: [BUGS] Running queries on inherited tables

Tom Lane <tgl@sss.pgh.pa.us> — 1999-09-13T00:00:10Z

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

Re: [BUGS] Running queries on inherited tables

Michael Richards <miker@scifair.acadiau.ca> — 1999-09-13T14:49:46Z

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

Re: [SQL] Re: [BUGS] Running queries on inherited tables

Herouth Maoz <herouth@oumail.openu.ac.il> — 1999-09-21T14:57:56Z

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