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 Service and other goodies at
  http://donb.photo.net.

Fri, 13 Oct 2000 13:14:03 -0700
Don Baccus <dhogaza@pacifier.com>
Re: AW: ALTER TABLE DROP COLUMN 
> -----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

Sun, 15 Oct 2000 22:56:12 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
RE: AW: ALTER TABLE DROP COLUMN 
> 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, Pennsylvania 19026

Sun, 15 Oct 2000 10:12:13 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: AW: ALTER TABLE DROP COLUMN

 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 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)


Sun, 15 Oct 2000 14:29:31 +0000
KuroiNeko <evpopkov@carrier.kiev.ua>
RE: AW: ALTER TABLE DROP COLUMN
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 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)
Sun, 15 Oct 2000 15:10:08 +0000
KuroiNeko <evpopkov@carrier.kiev.ua>
select oid .... for update ....
"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

Sun, 15 Oct 2000 11:57:32 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: AW: ALTER TABLE DROP COLUMN 

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"


Sun, 15 Oct 2000 10:16:32 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: select oid .... for update ....
> 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 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)


Sun, 15 Oct 2000 19:12:43 +0000
KuroiNeko <evpopkov@carrier.kiev.ua>
Permissions, was select oid
> -----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

Mon, 16 Oct 2000 08:21:11 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
RE: AW: ALTER TABLE DROP COLUMN 


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)


Mon, 16 Oct 2000 09:41:59 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: AW: ALTER TABLE DROP COLUMN
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.

Mon, 16 Oct 2000 18:51:10 +1100
Chris <chris@bitmead.com>
Re: AW: ALTER TABLE DROP COLUMN
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 ?

Mon, 16 Oct 2000 18:57:12 +1100
Chris <chris@bitmead.com>
Re: AW: ALTER TABLE DROP COLUMN


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



Mon, 16 Oct 2000 17:21:09 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: AW: ALTER TABLE DROP COLUMN
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.

Mon, 16 Oct 2000 19:34:34 +1100
Chris <chris@bitmead.com>
Re: AW: ALTER TABLE DROP COLUMN


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


Mon, 16 Oct 2000 17:57:14 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: AW: ALTER TABLE DROP COLUMN
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. :-)

Mon, 16 Oct 2000 20:42:18 +1100
Chris <chris@bitmead.com>
Re: AW: ALTER TABLE DROP COLUMN
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

Mon, 16 Oct 2000 13:01:58 +0300
Hannu Krosing <hannu@tm.ee>
Re: AW: ALTER TABLE DROP COLUMN
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 Service and other goodies at
  http://donb.photo.net.

Mon, 16 Oct 2000 07:50:32 -0700
Don Baccus <dhogaza@pacifier.com>
RE: AW: ALTER TABLE DROP COLUMN 
> > 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 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)


Mon, 16 Oct 2000 15:23:23 +0000
KuroiNeko <evpopkov@carrier.kiev.ua>
Re: AW: ALTER TABLE DROP COLUMN
> 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


Mon, 16 Oct 2000 13:02:17 -0400 (EDT)
merlin <merlin@crimelabs.net>
RE: AW: ALTER TABLE DROP COLUMN 
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

Mon, 16 Oct 2000 10:43:14 -0700
Adam Haberlach <adam@newsnipple.com>
Re: AW: ALTER TABLE DROP COLUMN