Обсуждение: AW: ALTER TABLE DROP COLUMN
> WAL would provide the framework to do something like that, but I still > say it'd be a bad idea. What you're describing is > irrevocable-once-it-starts DROP COLUMN; there is no way to > roll it back. > We're trying to get rid of statements that act that way, not add more. Yes. > I am not convinced that a 2x penalty for DROP COLUMN is such a huge > problem that we should give up all the normal safety features of SQL > in order to avoid it. Seems to me that DROP COLUMN is only a > big issue during DB development, when you're usually working with > relatively small amounts of test data anyway. Here I don't agree, the statement can also be used for an application version upgrade. Thus seen in SAP/R3 with tables > 30 Gb. My conclusion would be that we need both: 1. a fast system table only solution with physical/logical column id 2. a tool that does the cleanup (e.g. vacuum) Andreas
Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at> writes:
> My conclusion would be that we need both:
> 1. a fast system table only solution with physical/logical column id
> 2. a tool that does the cleanup (e.g. vacuum)
But the peak space usage during cleanup must still be 2X.
regards, tom lane
On Thu, 12 Oct 2000, Tom Lane wrote: > Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at> writes: > > My conclusion would be that we need both: > > 1. a fast system table only solution with physical/logical column id > > 2. a tool that does the cleanup (e.g. vacuum) > > But the peak space usage during cleanup must still be 2X. Is there no way of doing this such that we have N tuple types in the table? So that UPDATE/INSERTs are minus the extra column, while the old ones just have that column marked as deleted? Maybe change the stored value of the deleted field as some internal value that, when vacuum, or any other operation, sees it, it 'ignores' that field? maybe something that when you do an 'alter table drop', it effectively does an UPDATE on that field to set it to the 'drop column' value?
The Hermit Hacker <scrappy@hub.org> writes:
> On Thu, 12 Oct 2000, Tom Lane wrote:
>> Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at> writes:
>>>> My conclusion would be that we need both:
>>>> 1. a fast system table only solution with physical/logical column id
>>>> 2. a tool that does the cleanup (e.g. vacuum)
>>
>> But the peak space usage during cleanup must still be 2X.
> Is there no way of doing this such that we have N tuple types in the
> table? So that UPDATE/INSERTs are minus the extra column, while the old
> ones just have that column marked as deleted?
If we bite the bullet to the extent of supporting a distinction between
physical and logical column numbers, then ISTM there's no strong need
to do any of this other stuff at all. I'd expect that an inserted or
updated tuple would have a NULL in any physical column position that
doesn't have an equivalent logical column, so the space cost is minimal
(zero, in fact, if there are any other NULLs in the tuple). Over time
the space occupied by deleted-column data would gradually go away as
tuples got updated.
I really don't see why we're expending so much discussion on ways to
reformat all the tuples at once. It can't be done cheaply and I see
no real reason to do it at all, so it seems like we have many
more-profitable problems to work on.
regards, tom lane
On Thu, 12 Oct 2000, Tom Lane wrote: > The Hermit Hacker <scrappy@hub.org> writes: > > On Thu, 12 Oct 2000, Tom Lane wrote: > >> Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at> writes: > >>>> My conclusion would be that we need both: > >>>> 1. a fast system table only solution with physical/logical column id > >>>> 2. a tool that does the cleanup (e.g. vacuum) > >> > >> But the peak space usage during cleanup must still be 2X. > > > Is there no way of doing this such that we have N tuple types in the > > table? So that UPDATE/INSERTs are minus the extra column, while the old > > ones just have that column marked as deleted? > > If we bite the bullet to the extent of supporting a distinction between > physical and logical column numbers, then ISTM there's no strong need > to do any of this other stuff at all. what does/would it take to implement this?
Tom Lane wrote: > > Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at> writes: > > My conclusion would be that we need both: > > 1. a fast system table only solution with physical/logical column id > > 2. a tool that does the cleanup (e.g. vacuum) > > But the peak space usage during cleanup must still be 2X. Perhaps he means some kind of off-line cleanup tool, that has only the requirement of being able to continue from where it left off (or crashed) ? It could be useful in some cases (like removing a column on saturday from a terabyte-sized file that is used only on weekdays :) ---------- Hannu
At 04:23 PM 10/12/00 +0200, Zeugswetter Andreas SB wrote: >My conclusion would be that we need both: >1. a fast system table only solution with physical/logical column id >2. a tool that does the cleanup (e.g. vacuum) Oracle provides both styles of "drop column" - the "hide the column's data and make it logically disappear" style, and the "grind through and delete all the data as well as make the column disappear from view". So there's evidence of a need for both styles. If you choose the "hide the data" style, I don't know if you can later recover that space. However, despite the above I think a 2x "grind through and remove the data" DROP COLUMN would be a welcome first addition, and would meet the needs of a very high percentage of the current user base. A future option to just hide the data would most likely be welcome, too. - Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Serviceand other goodies at http://donb.photo.net.
> -----Original Message----- > From: Don Baccus [mailto:dhogaza@pacifier.com] > > At 04:23 PM 10/12/00 +0200, Zeugswetter Andreas SB wrote: > > >My conclusion would be that we need both: > >1. a fast system table only solution with physical/logical column id > >2. a tool that does the cleanup (e.g. vacuum) > > Oracle provides both styles of "drop column" - the "hide the column's > data and make it logically disappear" style, and the "grind through > and delete all the data as well as make the column disappear from > view". So there's evidence of a need for both styles. > > If you choose the "hide the data" style, I don't know if you can later > recover that space. > > However, despite the above I think a 2x "grind through and remove the > data" DROP COLUMN would be a welcome first addition, and would meet the > needs of a very high percentage of the current user base. This style of "DROP COLUMN" would change the attribute numbers whose positons are after the dropped column. Unfortunately we have no mechanism to invalidate/remove objects(or prepared plans) which uses such attribute numbers. And I've seen no proposal/discussion to solve this problem for DROP COLUMN feature. We wound't be able to prevent PostgreSQL from doing the wrong thing silently. When I used Oracle,I saw neither option of DROP COLUMN feature. It seems to tell us that the implementation isn't that easy. It may not be a bad choise to give up DROP COLUMN feature forever. > A future > option to just hide the data would most likely be welcome, too. > My trial implementation using physical/logical attribute numbers isn't so clean as I expected. I'm inclined to restrict my change to fix the TODO * ALTER TABLE ADD COLUMN to inherited table put column in wrong place though it would also introduce a backward compatibility. I could live without DROP COLUMN feature though I couldn't live without ADD COLUMN feature. Comments ? Regards. Hiroshi Inoue
> My trial implementation using physical/logical attribute numbers > isn't so clean as I expected. I'm inclined to restrict my change to > fix the TODO > * ALTER TABLE ADD COLUMN to inherited table put column in wrong place > though it would also introduce a backward compatibility. > I could live without DROP COLUMN feature though I couldn't > live without ADD COLUMN feature. We have a DROP COLUMN option in the FAQ, so I don't see a rush there. Sounds like we need your fix for add column with inheritance, but I suppose the 2x fix for DROP COLUMN could be used to add columns too. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Inoue san, > This style of "DROP COLUMN" would change the attribute > numbers whose positons are after the dropped column. > Unfortunately we have no mechanism to invalidate/remove > objects(or prepared plans) which uses such attribute numbers. 1 create table alpha( id int4, payload text ); 2 insert into alpha( id, payload ) values( 0, 'zero' ); 3 create table t( payload text ); 4 insert into t( payload ) select payload from alpha; 5 drop table alpha; 6 alter table t rename to alpha; Not a big deal, right? Also, drop column isn't really needed that often and requires alot of manual processing, like updating views/rules/procedures etc.On the other hand, when dropping a column (multiple columns) in a table with 10+ columns, statements 3 and 4 above may become quite painfull. It'd be nice if drop column were `expanded' to appropriate queries automatically. Not sure about abovementioned attribute numbers in such case.In general, however, if drop column is the only statement that is likely to affect attribute numbers this way (assuming that add column always adds and never inserts an attribute), then a fairly simple function in plpgsql, shipped with template1 will probably do. At least it should work to drop a single column, because full-featured function will require argument list of variable length. Ed ---Well I tried to be meek And I have tried to be mildBut I spat like a woman And I sulked like a childI have lived behindthe walls That have made me aloneStriven for peace Which I never have known Dire Straits, Brothers In Arms, The Man's Too Strong (Knopfler)
Here's something I don't understand.... If I'm missing an obvious, please feel free to kick me in the right direction.We're given two tables, linked with a foreign key. When insert is run on a master table, everything is OK, when trying to insert into a detail table, a strange query appears in the log (schema and log snippet attached).In fact, there should be no problem, but if a user has no permissions to update the master table (eg, this is a log where he can only insert, but neither delete, nor update), then inserting into detail table fails on permission violation at the query: SELECT oid FROM "alpha" WHERE "id" = $1 FOR UPDATE OF "alpha" TIA Ed ---Well I tried to be meek And I have tried to be mildBut I spat like a woman And I sulked like a childI have lived behindthe walls That have made me aloneStriven for peace Which I never have known Dire Straits, Brothers In Arms, The Man's Too Strong (Knopfler)
"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> My trial implementation using physical/logical attribute numbers
> isn't so clean as I expected. I'm inclined to restrict my change to
> fix the TODO
> * ALTER TABLE ADD COLUMN to inherited table put column in wrong place
> though it would also introduce a backward compatibility.
I'm confused --- how will that make things any simpler or cleaner?
You still need physical/logical column numbering distinction in order
to fix inherited ADD COLUMN, don't you?
regards, tom lane
This is a known problem in 7.0.x (see mailing list archives for more information). Peter E has a patch for 7.1 to remove this problem. Stephan Szabo sszabo@bigpanda.com On Sun, 15 Oct 2000, KuroiNeko wrote: > > Here's something I don't understand.... If I'm missing an obvious, please > feel free to kick me in the right direction. > We're given two tables, linked with a foreign key. When insert is run on a > master table, everything is OK, when trying to insert into a detail table, > a strange query appears in the log (schema and log snippet attached). > In fact, there should be no problem, but if a user has no permissions to > update the master table (eg, this is a log where he can only insert, but > neither delete, nor update), then inserting into detail table fails on > permission violation at the query: > > SELECT oid FROM "alpha" WHERE "id" = $1 FOR UPDATE OF "alpha"
> This is a known problem in 7.0.x (see mailing list archives for more > information). Peter E has a patch for 7.1 to remove this problem. Thanks and sorry for the hassle.While we're on it, if there's any work going on premissions (separating update/delete etc), I'd be glad to offer my help, if needed. Thx -- Well I tried to be meek And I have tried to be mildBut I spat like a woman And I sulked like a childI have lived behind thewalls That have made me aloneStriven for peace Which I never have known Dire Straits, Brothers In Arms, The Man's Too Strong (Knopfler)
> -----Original Message----- > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > > "Hiroshi Inoue" <Inoue@tpf.co.jp> writes: > > My trial implementation using physical/logical attribute numbers > > isn't so clean as I expected. I'm inclined to restrict my change to > > fix the TODO > > * ALTER TABLE ADD COLUMN to inherited table put column in wrong place > > though it would also introduce a backward compatibility. > > I'm confused --- how will that make things any simpler or cleaner? > You still need physical/logical column numbering distinction in order > to fix inherited ADD COLUMN, don't you? > Yes,the implementation would be almost same. I've been busy for some time and wasn't able to follow this thread. Don't people love 2x DROP COLUMN ? I don't object to 2x DROP COLUMN if it could be implemented properly though I don't want to implement it myself. However I would strongly object to 2x ADD COLUMN if such implementations are proposed. Regards. Hiroshi Inoue
KuroiNeko wrote: > Inoue san, > > > This style of "DROP COLUMN" would change the attribute > > numbers whose positons are after the dropped column. > > Unfortunately we have no mechanism to invalidate/remove > > objects(or prepared plans) which uses such attribute numbers. > > 1 create table alpha( id int4, payload text ); > 2 insert into alpha( id, payload ) values( 0, 'zero' ); > 3 create table t( payload text ); > 4 insert into t( payload ) select payload from alpha; > 5 drop table alpha; > 6 alter table t rename to alpha; > > Not a big deal, right? Yes,there's a similar procedure in FAQ. > Also, drop column isn't really needed > that often and requires alot of manual processing, like updating > views/rules/procedures etc. The FAQ doesn't refer to alot of manual processing at all. Certainly it's very difficult to cover all procederes to accomplish "DROP COLUMN". It's one of the reason why I've said "DROP COLUMN" isn't that easy. > > On the other hand, when dropping a column (multiple columns) in a table > with 10+ columns, statements 3 and 4 above may become quite painfull. > It'd be nice if drop column were `expanded' to appropriate queries > automatically. Not sure about abovementioned attribute numbers in such > case. > In general, however, if drop column is the only statement that is likely > to affect attribute numbers this way (assuming that add column always adds > and never inserts an attribute), then a fairly simple function in plpgsql, > shipped with template1 will probably do. plpgsql functions are executed in a transaction. I don't think plpgsql could execute "insert(select into) -> drop -> rename" properly(at least currently). Regards. Hiroshi Inoue > At least it should work to drop a > single column, because full-featured function will require argument list of > variable length. > > Ed > > --- > Well I tried to be meek > And I have tried to be mild > But I spat like a woman > And I sulked like a child > I have lived behind the walls > That have made me alone > Striven for peace > Which I never have known > > Dire Straits, Brothers In Arms, The Man's Too Strong (Knopfler)
KuroiNeko wrote: > 1 create table alpha( id int4, payload text ); <snip> > Not a big deal, right? Yes a big deal. You just lost all your oids.
Hiroshi Inoue wrote: > When I used Oracle,I saw neither option of DROP > COLUMN feature. It seems to tell us that the > implementation isn't > that easy. It may not be a bad choise to give up DROP > COLUMN feature forever. Because it's not easy we shouldn't do it? I don't think so. The perfect solution is lazy updating of tuples but it requires versioning of meta-data and that requires a bit of work. > However I would strongly object to 2x > ADD COLUMN if such implementations are proposed. Not even 2x for ADD COLUMN DEFAULT ?
Chris wrote: > Hiroshi Inoue wrote: > > > When I used Oracle,I saw neither option of DROP > > COLUMN feature. It seems to tell us that the > > implementation isn't > > that easy. It may not be a bad choise to give up DROP > > COLUMN feature forever. > > Because it's not easy we shouldn't do it? I don't think so. The perfect > solution is lazy updating of tuples but it requires versioning of > meta-data and that requires a bit of work. > > > However I would strongly object to 2x > > ADD COLUMN if such implementations are proposed. > > Not even 2x for ADD COLUMN DEFAULT ? Certainly it would need 2x. However is ADD COLUMN DEFAULT really needed ? I would do as follows. ADD COLUMN (without default) UPDATE .. SET new_column = new default ALTER TABLE ALTER COLUMN SET DEFAULT Regards. Hiroshi Inoue
Hiroshi Inoue wrote: > Certainly it would need 2x. > However is ADD COLUMN DEFAULT really needed ? > I would do as follows. > > ADD COLUMN (without default) > UPDATE .. SET new_column = new default > ALTER TABLE ALTER COLUMN SET DEFAULT Well in current postgres that would use 2x. With WAL I presume that would use a lot of log space and probably a lot more processing. But if you can do the above you might as well support the right syntax.
Chris wrote: > Hiroshi Inoue wrote: > > > When I used Oracle,I saw neither option of DROP > > COLUMN feature. It seems to tell us that the > > implementation isn't > > that easy. It may not be a bad choise to give up DROP > > COLUMN feature forever. > > Because it's not easy we shouldn't do it? I don't think so. The perfect > solution is lazy updating of tuples but it requires versioning of > meta-data and that requires a bit of work. > We could easily break the consistency of DB due to careless implementations. Is "DROP COLUMN" valuable to walk on a tightrope ? I would agree if "ADD COLUMN" needs to walk on a tightrope. Regards. Hiroshi Inoue
Hiroshi Inoue wrote: > We could easily break the consistency of DB due to > careless implementations. I'm sure no-one around here would do careless implementations. :-)
Chris wrote: > > Hiroshi Inoue wrote: > > > When I used Oracle,I saw neither option of DROP > > COLUMN feature. It seems to tell us that the > > implementation isn't > > that easy. It may not be a bad choise to give up DROP > > COLUMN feature forever. > > Because it's not easy we shouldn't do it? I don't think so. The perfect > solution is lazy updating of tuples but it requires versioning of > meta-data and that requires a bit of work. I would prefer the logical/physical numbering + typed tuples (or is it the same thing ;) It would give us the additional benefit of being able to move to SQL3-wise correct CREATE TABLE UNDER syntax with most constraints (primary/foreign key, unique, ...) carried on automatically if we store the (single-)inheritance hierarchy in one file. Others (NOT NULL, CHECK, ...) will need additional check for tuple type. This does not solve the problem for multiple inheritance, but then we could cludge most of it by inheriting all from a single root. I suspect it would still be easier than doing it the other way (by constructing UNIONs each time, checking several indexes for uniquenass (or creating a new index type for indexing several separate relations)) --------------------- Hannu
At 10:56 PM 10/15/00 +0900, Hiroshi Inoue wrote: >When I used Oracle,I saw neither option of DROP COLUMN >feature. It seems to tell us that the implementation isn't >that easy. It may not be a bad choise to give up DROP >COLUMN feature forever. Both options are in Oracle now, as proudly documented in their freely accessible on-line documentation. It is very possible they didn't implement it until version 8, i.e. until a couple of years ago. - Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Serviceand other goodies at http://donb.photo.net.
> > Not a big deal, right? > > Yes a big deal. You just lost all your oids. After I hit the wall with oids for the first time, I don't refer to them anymore :) But yes, you're perfectly right, this is one more reason to have DDL completely `automated,' ie no manual substitutions.And here the fact that drop column is rarely needed is a double-bladed sword. With things that you don't do often, you're at risk to forget something essential and hose your data. -- Well I tried to be meek And I have tried to be mildBut I spat like a woman And I sulked like a childI have lived behind thewalls That have made me aloneStriven for peace Which I never have known Dire Straits, Brothers In Arms, The Man's Too Strong (Knopfler)
On Mon, Oct 16, 2000 at 06:51:10PM +1100, Chris wrote: > KuroiNeko wrote: > > > 1 create table alpha( id int4, payload text ); > <snip> > > > Not a big deal, right? > > Yes a big deal. You just lost all your oids. Been there. Done that. Learned to heed the warnings about using oids in any kind of persistant manner. -- Adam Haberlach | ASCII /~\ adam@newsnipple.com | Ribbon \ / Against http://www.newsnipple.com | Campaign X HTML '88 EX500 | / \ E-mail
> Both options are in Oracle now, as proudly documented in their > freely accessible on-line documentation. It is very possible > they didn't implement it until version 8, i.e. until a couple of years > ago. FYI: ALTER TABLE DROP COLUMN was added as of 8 / 8i according to our Oracle DBA. - merlin