Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id UAA37073; Sun, 12 Sep 1999 20:03:42 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id UAA05823; Sun, 12 Sep 1999 20:00:11 -0400 (EDT) To: Michael Richards cc: pgsql-sql@postgreSQL.org, pgsql-bugs@postgreSQL.org Subject: Re: [BUGS] Running queries on inherited tables In-reply-to: Your message of Sun, 12 Sep 1999 15:41:48 -0300 (ADT) Date: Sun, 12 Sep 1999 20:00:10 -0400 Message-ID: <5821.937180810@sss.pgh.pa.us> From: Tom Lane Michael Richards 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