drop

From pgsql-hackers-owner+M3040@hub.org Thu Jun  8 00:31:01 2000
Received: from renoir.op.net (root@renoir.op.net [207.29.195.4])
	by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id AAA13157
	for <pgman@candle.pha.pa.us>; Thu, 8 Jun 2000 00:31:00 -0400 (EDT)
Received: from hub.org (root@hub.org [216.126.84.1]) by renoir.op.net (o1/$Revision: 1.5 $) with ESMTP id AAA01089 for <pgman@candle.pha.pa.us>; Thu, 8 Jun 2000 00:17:19 -0400 (EDT)
Received: from hub.org (majordom@localhost [127.0.0.1])
	by hub.org (8.10.1/8.10.1) with SMTP id e5846ib99782;
	Thu, 8 Jun 2000 00:06:44 -0400 (EDT)
Received: from sd.tpf.co.jp (sd.tpf.co.jp [210.161.239.34])
	by hub.org (8.10.1/8.10.1) with ESMTP id e5846Xb99707
	for <pgsql-hackers@postgreSQL.org>; Thu, 8 Jun 2000 00:06:33 -0400 (EDT)
Received: from cadzone ([126.0.1.40] (may be forged))
          by sd.tpf.co.jp (2.5 Build 2640 (Berkeley 8.8.6)/8.8.4) with SMTP
   id NAA01145; Thu, 08 Jun 2000 13:05:42 +0900
From: "Hiroshi Inoue" <Inoue@tpf.co.jp>
To: "Bruce Momjian" <pgman@candle.pha.pa.us>
Cc: "PostgreSQL-development" <pgsql-hackers@postgreSQL.org>
Subject: RE: [HACKERS] DROP COLUMN status
Date: Thu, 8 Jun 2000 13:07:44 +0900
Message-ID: <000d01bfd0ff$194d56c0$2801007e@tpf.co.jp>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook 8.5, Build 4.71.2173.0
In-Reply-To: <200006080309.XAA10305@candle.pha.pa.us>
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
Importance: Normal
X-Mailing-List: pgsql-hackers@postgresql.org
Precedence: bulk
Sender: pgsql-hackers-owner@hub.org
Status: OR

> -----Original Message-----
> From: pgsql-hackers-owner@hub.org [mailto:pgsql-hackers-owner@hub.org]On
> Behalf Of Bruce Momjian
> 
> Can someone comment on where we are with DROP COLUMN?
>

I've already committed my trial implementation 3 months ago.
They are $ifdef'd by _DROP_COLUMN_HACK__.
Please enable the feature and evaluate it.
You could enable the feature without initdb.

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp
 

From Inoue@tpf.co.jp Thu Jun  8 02:03:27 2000
Received: from sd.tpf.co.jp (sd.tpf.co.jp [210.161.239.34])
	by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id CAA14243
	for <pgman@candle.pha.pa.us>; Thu, 8 Jun 2000 02:03:25 -0400 (EDT)
Received: from cadzone ([126.0.1.40] (may be forged))
          by sd.tpf.co.jp (2.5 Build 2640 (Berkeley 8.8.6)/8.8.4) with SMTP
   id PAA01221; Thu, 08 Jun 2000 15:03:23 +0900
From: "Hiroshi Inoue" <Inoue@tpf.co.jp>
To: "Bruce Momjian" <pgman@candle.pha.pa.us>
Cc: "PostgreSQL-development" <pgsql-hackers@postgreSQL.org>
Subject: RE: [HACKERS] DROP COLUMN status
Date: Thu, 8 Jun 2000 15:05:24 +0900
Message-ID: <000f01bfd10f$893798a0$2801007e@tpf.co.jp>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook 8.5, Build 4.71.2173.0
In-Reply-To: <200006080457.AAA13430@candle.pha.pa.us>
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
Importance: Normal
Status: OR

> -----Original Message-----
> From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
> Sent: Thursday, June 08, 2000 1:58 PM
> 
> [ Charset ISO-8859-1 unsupported, converting... ]
> > > -----Original Message-----
> > > From: pgsql-hackers-owner@hub.org 
> [mailto:pgsql-hackers-owner@hub.org]On
> > > Behalf Of Bruce Momjian
> > > 
> > > Can someone comment on where we are with DROP COLUMN?
> > >
> > 
> > I've already committed my trial implementation 3 months ago.
> > They are $ifdef'd by _DROP_COLUMN_HACK__.
> > Please enable the feature and evaluate it.
> > You could enable the feature without initdb.
> 
> OK, can you explain how it works, and add any needed documentation so we
> can enable it.
>

First it's only a trial so I don't implement it completely.
Especially I don't completely drop related objects
(FK_constraint,triggers,views etc). I don't know whether
we could drop them properly or not.

The implementation makes the dropped column invisible by
changing its attnum to -attnum - offset(currently 20) and
attnam to ("*already Dropped%d",attnum). It doesn't touch
the table at all. After dropping a column insert/update
operation regards the column as NULL and other related
stuff simply ignores the column.

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

From tgl@sss.pgh.pa.us Thu Jun  8 10:20:34 2000
Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2])
	by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id KAA29148
	for <pgman@candle.pha.pa.us>; Thu, 8 Jun 2000 10:20:33 -0400 (EDT)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id KAA15725;
	Thu, 8 Jun 2000 10:20:11 -0400 (EDT)
To: "Hiroshi Inoue" <Inoue@tpf.co.jp>
cc: "Bruce Momjian" <pgman@candle.pha.pa.us>,
        "PostgreSQL-development" <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] DROP COLUMN status 
In-reply-to: <000f01bfd10f$893798a0$2801007e@tpf.co.jp> 
References: <000f01bfd10f$893798a0$2801007e@tpf.co.jp>
Comments: In-reply-to "Hiroshi Inoue" <Inoue@tpf.co.jp>
	message dated "Thu, 08 Jun 2000 15:05:24 +0900"
Date: Thu, 08 Jun 2000 10:20:11 -0400
Message-ID: <15722.960474011@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
Status: ORr

"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> The implementation makes the dropped column invisible by
> changing its attnum to -attnum - offset(currently 20) and
> attnam to ("*already Dropped%d",attnum).

Ugh.  No wonder you had to hack so many places in such an ugly fashion.
Why not leave the attnum as-is, and just add a bool saying "column is
dropped" to pg_attribute?  As long as the parser ignores columns marked
that way for field lookup and expansion of *, it seems the rest of the
system wouldn't need to treat dropped columns specially in any way.

			regards, tom lane

From pgsql-hackers-owner+M3094@hub.org Thu Jun  8 15:58:30 2000
Received: from hub.org (root@hub.org [216.126.84.1])
	by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id PAA25109
	for <pgman@candle.pha.pa.us>; Thu, 8 Jun 2000 15:58:28 -0400 (EDT)
Received: from hub.org (majordom@localhost [127.0.0.1])
	by hub.org (8.10.1/8.10.1) with SMTP id e58JrqT91713;
	Thu, 8 Jun 2000 15:53:52 -0400 (EDT)
Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2])
	by hub.org (8.10.1/8.10.1) with ESMTP id e58JqpT91436
	for <pgsql-hackers@postgreSQL.org>; Thu, 8 Jun 2000 15:52:51 -0400 (EDT)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id PAA19690;
	Thu, 8 Jun 2000 15:52:43 -0400 (EDT)
To: Bruce Momjian <pgman@candle.pha.pa.us>
cc: Hiroshi Inoue <Inoue@tpf.co.jp>,
        PostgreSQL-development <pgsql-hackers@postgreSQL.org>
Subject: Re: [HACKERS] DROP COLUMN status 
In-reply-to: <200006081541.LAA01566@candle.pha.pa.us> 
References: <200006081541.LAA01566@candle.pha.pa.us>
Comments: In-reply-to Bruce Momjian <pgman@candle.pha.pa.us>
	message dated "Thu, 08 Jun 2000 11:41:43 -0400"
Date: Thu, 08 Jun 2000 15:52:43 -0400
Message-ID: <19687.960493963@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Mailing-List: pgsql-hackers@postgresql.org
Precedence: bulk
Sender: pgsql-hackers-owner@hub.org
Status: OR

>>>> The implementation makes the dropped column invisible by
>>>> changing its attnum to -attnum - offset(currently 20) and
>>>> attnam to ("*already Dropped%d",attnum).
>> 
>> Ugh.  No wonder you had to hack so many places in such an ugly fashion.
>> Why not leave the attnum as-is, and just add a bool saying "column is
>> dropped" to pg_attribute?  As long as the parser ignores columns marked
>> that way for field lookup and expansion of *, it seems the rest of the
>> system wouldn't need to treat dropped columns specially in any way.

> If we leave it as positive, don't we have to change user applications
> that query pg_attribute so they also know to skip it?

Good point, but I think user applications that query pg_attribute
are likely to have trouble anyway: if they're expecting a consecutive
series of attnums then they're going to lose no matter what.

			regards, tom lane

From hannu@tm.ee Sat Jun 10 01:02:57 2000
Received: from me.tm.ee (ppp15.tele2.ee [212.107.33.15])
	by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id BAA10377
	for <pgman@candle.pha.pa.us>; Sat, 10 Jun 2000 01:02:55 -0400 (EDT)
Received: from tm.ee (IDENT:hannu@localhost.localdomain [127.0.0.1])
	by me.tm.ee (8.9.3/8.9.3) with ESMTP id GAA00940;
	Sat, 10 Jun 2000 06:59:33 +0300
Sender: hannu@me.tm.ee
Message-ID: <3941BD25.96760D2E@tm.ee>
Date: Sat, 10 Jun 2000 06:59:33 +0300
From: Hannu Krosing <hannu@tm.ee>
X-Mailer: Mozilla 4.72 [en] (X11; U; Linux 2.2.12-20 i686)
X-Accept-Language: en
MIME-Version: 1.0
To: Bruce Momjian <pgman@candle.pha.pa.us>
CC: Tom Lane <tgl@sss.pgh.pa.us>, Peter Eisentraut <peter_e@gmx.net>,
        PostgreSQL Development <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] ALTER TABLE DROP COLUMN
References: <200006091249.IAA18730@candle.pha.pa.us>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Status: OR

Bruce Momjian wrote:
> 
> Seems we have 4 DROP COLUMN ideas:
> 
>         Method                                                  Advantage
>         -----------------------------------------------------------------
> 1       invisible column marked by negative attnum              fast
> 2       invisible column marked by is_dropped column            fast
> 3       make copy of table without column                       col removed
> 4       make new tuples in existing table without column        col removed

IIRC there was a fifth idea, a variation of 2 that would work better
with 
inheritance -

5   all columns have is_real_column attribute that is true for all
coluns 
present in that relation, so situations like

create table tab_a(a_i int);
create table tab_b(b_i int) inherits(tab_a);
alter table tab_a add column c_i int;

can be made to work.

It would also require clients to ignore all missing columns that backend
can 
pass to them as nulls (which is usually quite cheap in bandwith usage)
in 
case of "SELECT **" queries.

We could even rename attno to attid to make folks aware that it is not
be 
assumed to be continuous.

> Folks, we had better choose one and get started.
> 
> Number 1 Hiroshi has ifdef'ed out in the code.  Items 1 and 2 have
> problems with backend code and 3rd party code not seeing the dropped
> columns, or having gaps in the attno numbering.

If we want to make ADD COLUMN to work with inheritance wihout having to 
rewrite every single tuple in both parent and inherited tables, we will 
have to accept the fact that there are caps in in attno numbering.

> Number 3 has problems
> with making it an atomic operation, and number 4 is described below.

Nr 4 has still problems with attno numbering _changing_ during drop
which 
could either be better or worse for client software than having gaps -
in both cases client must be prepared to deal with runtime changes in 
attribute definition.

--------------
Hannu

From Inoue@tpf.co.jp Sat Jun 10 01:01:01 2000
Received: from renoir.op.net (root@renoir.op.net [207.29.195.4])
	by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id BAA10355
	for <pgman@candle.pha.pa.us>; Sat, 10 Jun 2000 01:01:00 -0400 (EDT)
Received: from sd.tpf.co.jp (sd.tpf.co.jp [210.161.239.34]) by renoir.op.net (o1/$Revision: 1.5 $) with ESMTP id AAA25467 for <pgman@candle.pha.pa.us>; Sat, 10 Jun 2000 00:41:32 -0400 (EDT)
Received: from mcadnote1 (ppm110.noc.fukui.nsk.ne.jp [210.161.188.29] (may be forged))
          by sd.tpf.co.jp (2.5 Build 2640 (Berkeley 8.8.6)/8.8.4) with SMTP
   id NAA03125; Sat, 10 Jun 2000 13:40:40 +0900
From: "Hiroshi Inoue" <Inoue@tpf.co.jp>
To: "Bruce Momjian" <pgman@candle.pha.pa.us>, "Tom Lane" <tgl@sss.pgh.pa.us>
Cc: "Peter Eisentraut" <peter_e@gmx.net>,
        "PostgreSQL Development" <pgsql-hackers@postgreSQL.org>
Subject: RE: [HACKERS] ALTER TABLE DROP COLUMN
Date: Sat, 10 Jun 2000 13:43:26 +0900
Message-ID: <EKEJJICOHDIEMGPNIFIJEELACBAA.Inoue@tpf.co.jp>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="US-ASCII"
Content-Transfer-Encoding: 7bit
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0)
In-Reply-To: <200006091249.IAA18730@candle.pha.pa.us>
Importance: Normal
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6700
Status: ORr

> -----Original Message-----
> From: pgsql-hackers-owner@hub.org 
> [mailto:pgsql-hackers-owner@hub.org]On Behalf Of Bruce Momjian
> 
> Seems we have 4 DROP COLUMN ideas:
> 
> 	Method                                                  Advantage
> 	-----------------------------------------------------------------
> 1	invisible column marked by negative attnum		fast
> 2	invisible column marked by is_dropped column		fast
> 3	make copy of table without column			col removed
> 4	make new tuples in existing table without column	col removed
> 
> Folks, we had better choose one and get started.  
> 
> Number 1 Hiroshi has ifdef'ed out in the code.  Items 1 and 2 have
> problems with backend code and 3rd party code not seeing the dropped
> columns,

Hmm,doesn't  *not seeing*  mean the column is dropped ?

> or having gaps in the attno numbering. Number 3 has problems
> with making it an atomic operation, and number 4 is described below. 
>

Don't forget another important point.

Currently even DROP TABLE doesn't remove related objects completely.
And I don't think I could remove objects related to the dropping column
completely using 1)2) in ALTER TABLE DROP COLUMN implementation.

Using 3)4) we should not only remove objects as 1)2) but also
change attnum-s in all objects related to the relation. Otherwise
PostgreSQL would do the wrong thing silently.

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp 

From dhogaza@pacifier.com Sat Jun 10 01:01:06 2000
Received: from smtp.pacifier.com (comet.pacifier.com [199.2.117.155])
	by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id BAA10370
	for <pgman@candle.pha.pa.us>; Sat, 10 Jun 2000 01:01:04 -0400 (EDT)
Received: from desktop (dsl-dhogaza.pacifier.net [207.202.226.68])
	by smtp.pacifier.com (8.9.3/8.9.3pop) with SMTP id WAA08521;
	Fri, 9 Jun 2000 22:01:00 -0700 (PDT)
Message-Id: <3.0.1.32.20000609215758.0116d850@mail.pacifier.com>
X-Sender: dhogaza@mail.pacifier.com
X-Mailer: Windows Eudora Pro Version 3.0.1 (32)
Date: Fri, 09 Jun 2000 21:57:58 -0700
To: "Hiroshi Inoue" <Inoue@tpf.co.jp>,
        "Bruce Momjian" <pgman@candle.pha.pa.us>,
        "Tom Lane" <tgl@sss.pgh.pa.us>
From: Don Baccus <dhogaza@pacifier.com>
Subject: RE: [HACKERS] ALTER TABLE DROP COLUMN
Cc: "Peter Eisentraut" <peter_e@gmx.net>,
        "PostgreSQL Development" <pgsql-hackers@postgreSQL.org>
In-Reply-To: <EKEJJICOHDIEMGPNIFIJEELACBAA.Inoue@tpf.co.jp>
References: <200006091249.IAA18730@candle.pha.pa.us>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Status: OR

At 01:43 PM 6/10/00 +0900, Hiroshi Inoue wrote:
>> -----Original Message-----
>> From: pgsql-hackers-owner@hub.org 
>> [mailto:pgsql-hackers-owner@hub.org]On Behalf Of Bruce Momjian
>> 
>> Seems we have 4 DROP COLUMN ideas:
>> 
>> 	Method                                                  Advantage
>> 	-----------------------------------------------------------------
>> 1	invisible column marked by negative attnum		fast
>> 2	invisible column marked by is_dropped column		fast
>> 3	make copy of table without column			col removed
>> 4	make new tuples in existing table without column	col removed
>> 
>> Folks, we had better choose one and get started.  

Oracle gives you the choice between the "cheating" fast method(s) and
the "real" slow (really slow?) real method.

So there's at least real world experience by virtue of example by
the world's most successful database supplier that user control
over "hide the column" and "really delete the column" is valuable.

It really makes a lot of sense to give such a choice.  If one
does so by "hiding", at a later date one would think the choice
of "really deleting" would be a possibility.  I don't know if
Oracle does this...

If not, they might not care.  In today's world, there are bazillions
of dollars for Oracle to scoop up from users who could just as easily
be PG users - all those "we'll fail if don't IPO 'cause we'll never
have any customers" database-backed websites :)



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

From tgl@sss.pgh.pa.us Sat Jun 10 01:31:04 2000
Received: from renoir.op.net (root@renoir.op.net [207.29.195.4])
	by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id BAA10922
	for <pgman@candle.pha.pa.us>; Sat, 10 Jun 2000 01:31:03 -0400 (EDT)
Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by renoir.op.net (o1/$Revision: 1.5 $) with ESMTP id BAA27265 for <pgman@candle.pha.pa.us>; Sat, 10 Jun 2000 01:16:07 -0400 (EDT)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id BAA06206;
	Sat, 10 Jun 2000 01:14:37 -0400 (EDT)
To: Don Baccus <dhogaza@pacifier.com>
cc: "Hiroshi Inoue" <Inoue@tpf.co.jp>,
        "Bruce Momjian" <pgman@candle.pha.pa.us>,
        "Peter Eisentraut" <peter_e@gmx.net>,
        "PostgreSQL Development" <pgsql-hackers@postgreSQL.org>
Subject: Re: [HACKERS] ALTER TABLE DROP COLUMN 
In-reply-to: <3.0.1.32.20000609215758.0116d850@mail.pacifier.com> 
References: <200006091249.IAA18730@candle.pha.pa.us> <3.0.1.32.20000609215758.0116d850@mail.pacifier.com>
Comments: In-reply-to Don Baccus <dhogaza@pacifier.com>
	message dated "Fri, 09 Jun 2000 21:57:58 -0700"
Date: Sat, 10 Jun 2000 01:14:37 -0400
Message-ID: <6203.960614077@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
Status: OR

Don Baccus <dhogaza@pacifier.com> writes:
> Oracle gives you the choice between the "cheating" fast method(s) and
> the "real" slow (really slow?) real method.

> So there's at least real world experience by virtue of example by
> the world's most successful database supplier that user control
> over "hide the column" and "really delete the column" is valuable.

Sure, but you don't need any help from the database to do "really delete
the column".  SELECT INTO... is enough, and it's not even any slower
than the implementations under discussion.

So I'm satisfied if we offer the "hide the column" approach.

Has anyone thought about what happens to table constraints that use the
doomed column?  Triggers, RI rules, yadda yadda?

Has anyone thought about undoing a DELETE COLUMN?  The data is still
there, at least in tuples that have not been updated, so it's not
totally unreasonable.

			regards, tom lane

From dhogaza@pacifier.com Sat Jun 10 09:30:59 2000
Received: from renoir.op.net (root@renoir.op.net [207.29.195.4])
	by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id JAA25987
	for <pgman@candle.pha.pa.us>; Sat, 10 Jun 2000 09:30:58 -0400 (EDT)
Received: from smtp.pacifier.com (comet.pacifier.com [199.2.117.155]) by renoir.op.net (o1/$Revision: 1.5 $) with ESMTP id JAA18716 for <pgman@candle.pha.pa.us>; Sat, 10 Jun 2000 09:15:08 -0400 (EDT)
Received: from desktop (dsl-dhogaza.pacifier.net [207.202.226.68])
	by smtp.pacifier.com (8.9.3/8.9.3pop) with SMTP id GAA15799;
	Sat, 10 Jun 2000 06:14:28 -0700 (PDT)
Message-Id: <3.0.1.32.20000610054306.0115f020@mail.pacifier.com>
X-Sender: dhogaza@mail.pacifier.com
X-Mailer: Windows Eudora Pro Version 3.0.1 (32)
Date: Sat, 10 Jun 2000 05:43:06 -0700
To: Tom Lane <tgl@sss.pgh.pa.us>
From: Don Baccus <dhogaza@pacifier.com>
Subject: Re: [HACKERS] ALTER TABLE DROP COLUMN 
Cc: "Hiroshi Inoue" <Inoue@tpf.co.jp>,
        "Bruce Momjian" <pgman@candle.pha.pa.us>,
        "Peter Eisentraut" <peter_e@gmx.net>,
        "PostgreSQL Development" <pgsql-hackers@postgreSQL.org>
In-Reply-To: <6203.960614077@sss.pgh.pa.us>
References: <3.0.1.32.20000609215758.0116d850@mail.pacifier.com>
	<200006091249.IAA18730@candle.pha.pa.us>
	<3.0.1.32.20000609215758.0116d850@mail.pacifier.com>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Status: OR

At 01:14 AM 6/10/00 -0400, Tom Lane wrote:
>Don Baccus <dhogaza@pacifier.com> writes:
>> Oracle gives you the choice between the "cheating" fast method(s) and
>> the "real" slow (really slow?) real method.
>
>> So there's at least real world experience by virtue of example by
>> the world's most successful database supplier that user control
>> over "hide the column" and "really delete the column" is valuable.
>
>Sure, but you don't need any help from the database to do "really delete
>the column".  SELECT INTO... is enough, and it's not even any slower
>than the implementations under discussion.
>
>So I'm satisfied if we offer the "hide the column" approach.

<shrug> I wouldn't put a "real" drop column at the top of my list
of priorities, but there is something to be said for user convenience.



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

From tgl@sss.pgh.pa.us Sun Jun 11 12:31:03 2000
Received: from renoir.op.net (root@renoir.op.net [207.29.195.4])
	by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id MAA05771
	for <pgman@candle.pha.pa.us>; Sun, 11 Jun 2000 12:31:01 -0400 (EDT)
Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by renoir.op.net (o1/$Revision: 1.5 $) with ESMTP id MAA19315 for <pgman@candle.pha.pa.us>; Sun, 11 Jun 2000 12:24:06 -0400 (EDT)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id MAA09503;
	Sun, 11 Jun 2000 12:22:42 -0400 (EDT)
To: "Hiroshi Inoue" <Inoue@tpf.co.jp>
cc: "Bruce Momjian" <pgman@candle.pha.pa.us>,
        "Peter Eisentraut" <peter_e@gmx.net>,
        "PostgreSQL Development" <pgsql-hackers@postgreSQL.org>
Subject: Re: [HACKERS] ALTER TABLE DROP COLUMN 
In-reply-to: <EKEJJICOHDIEMGPNIFIJEELACBAA.Inoue@tpf.co.jp> 
References: <EKEJJICOHDIEMGPNIFIJEELACBAA.Inoue@tpf.co.jp>
Comments: In-reply-to "Hiroshi Inoue" <Inoue@tpf.co.jp>
	message dated "Sat, 10 Jun 2000 13:43:26 +0900"
Date: Sun, 11 Jun 2000 12:22:42 -0400
Message-ID: <9500.960740562@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
Status: ORr

>> Seems we have 4 DROP COLUMN ideas:
>> Method                                                  Advantage
>> -----------------------------------------------------------------
>> 1	invisible column marked by negative attnum		fast
>> 2	invisible column marked by is_dropped column		fast
>> 3	make copy of table without column			col removed
>> 4	make new tuples in existing table without column	col removed

Bruce and I talked about this by phone yesterday, and we realized that
none of these are very satisfactory.  #1 and #2 both have the flaw that
applications that examine pg_attribute will probably break: they will
see a sequence of attnum values with gaps in it.  And what should the
rel's relnatts field be set to?  #3 and #4 are better on that point,
but they leave us with the problem of renumbering references to columns
after the dropped one in constraints, rules, PL functions, etc.

Furthermore, there is a closely related problem that none of these
approaches give us much help on: recursive ALTER TABLE ADD COLUMN.
Right now, ADD puts the new column at the end of each table it's added
to, which often means that it gets a different column number in child
tables than in parent tables.  That leads to havoc for pg_dump.

I think the only clean solution is to create a clear distinction between
physical and logical column numbers.  Each pg_attribute tuple would need
two attnum fields, and pg_class would need two relnatts fields as well.
A column once created would never change its physical column number, but
its logical column number might change as a consequence of adding or
dropping columns before it.  ADD COLUMN would ensure that a column added
to child tables receives the same logical column number as it has in the
parent table, thus solving the dump/reload problem.  DROP COLUMN would
assign an invalid logical column number to dropped columns.  They could
be numbered zero except that we'd probably still want a unique index on
attrelid+attnum, and the index would complain.  I'd suggest using
Hiroshi's idea: give a dropped column a logical attnum equal to
-(physical_attnum + offset).

With this approach, internal operations on tuples would all use
physical column numbers, but operations that interface to the outside
world would present a view of only the valid logical columns.  For
example, the parser would only allow logical columns to be referenced
by name; "SELECT *" would expand to valid logical columns in logical-
column-number order; COPY would send or receive valid logical columns
in logical-column-number order; etc.

Stored rules and so forth probably should store physical column numbers
so that they need not be modified during column add/drop.

This would require looking at all the places in the backend to determine
whether they should be working with logical or physical column numbers,
but the design is such that most all places would want to be using
physical numbers, so I don't think it'd be too painful.

Although I'd prefer to give the replacement columns two new names
(eg, "attlnum" and "attpnum") to ensure we find all uses, this would
surely break applications that examine pg_attribute.  For compatibility
we'd have to recycle "attnum" and "relnatts" to indicate logical column
number and logical column count, while adding new fields (say "attpnum"
and "relnpatts") for the physical number and count.

Comments?

			regards, tom lane

From pgsql-hackers-owner+M3184@hub.org Mon Jun 12 09:29:17 2000
Received: from hub.org (root@hub.org [216.126.84.1])
	by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id JAA16538
	for <pgman@candle.pha.pa.us>; Mon, 12 Jun 2000 09:29:15 -0400 (EDT)
Received: from hub.org (majordom@localhost [127.0.0.1])
	by hub.org (8.10.1/8.10.1) with SMTP id e5C9RxT92685;
	Mon, 12 Jun 2000 05:27:59 -0400 (EDT)
Received: from clio.trends.ca (root@clio.trends.ca [209.47.148.2])
	by hub.org (8.10.1/8.10.1) with ESMTP id e5C8YWT89945
	for <pgsql-hackers@postgreSQL.org>; Mon, 12 Jun 2000 04:34:32 -0400 (EDT)
Received: from sd.tpf.co.jp (sd.tpf.co.jp [210.161.239.34])
	by clio.trends.ca (8.9.3+Sun/8.9.3) with ESMTP id VAA17711
	for <pgsql-hackers@postgreSQL.org>; Sun, 11 Jun 2000 21:40:28 -0400 (EDT)
Received: from cadzone ([126.0.1.40] (may be forged))
          by sd.tpf.co.jp (2.5 Build 2640 (Berkeley 8.8.6)/8.8.4) with SMTP
   id KAA03734; Mon, 12 Jun 2000 10:38:42 +0900
From: "Hiroshi Inoue" <Inoue@tpf.co.jp>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Cc: "Bruce Momjian" <pgman@candle.pha.pa.us>,
        "Peter Eisentraut" <peter_e@gmx.net>,
        "PostgreSQL Development" <pgsql-hackers@postgresql.org>
Subject: RE: [HACKERS] ALTER TABLE DROP COLUMN 
Date: Mon, 12 Jun 2000 10:40:47 +0900
Message-ID: <000b01bfd40f$3b3091e0$2801007e@tpf.co.jp>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-2022-jp"
Content-Transfer-Encoding: 7bit
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook 8.5, Build 4.71.2173.0
In-Reply-To: <9500.960740562@sss.pgh.pa.us>
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
Importance: Normal
X-Mailing-List: pgsql-hackers@postgresql.org
Precedence: bulk
Sender: pgsql-hackers-owner@hub.org
Status: OR

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> 
> >> Seems we have 4 DROP COLUMN ideas:
> >> Method                                                  Advantage
> >> -----------------------------------------------------------------
> >> 1	invisible column marked by negative attnum		fast
> >> 2	invisible column marked by is_dropped column		fast
> >> 3	make copy of table without column			col removed
> >> 4	make new tuples in existing table without column	col removed
>

Hmm,I've received no pg-ML mails for more than 1 day.
What's happened with pgsql ML ? 
 
> Bruce and I talked about this by phone yesterday, and we realized that
> none of these are very satisfactory.  #1 and #2 both have the flaw that
> applications that examine pg_attribute will probably break: they will
> see a sequence of attnum values with gaps in it.  And what should the
> rel's relnatts field be set to?  #3 and #4 are better on that point,
> but they leave us with the problem of renumbering references to columns
> after the dropped one in constraints, rules, PL functions, etc.
> 
> Furthermore, there is a closely related problem that none of these
> approaches give us much help on: recursive ALTER TABLE ADD COLUMN.
> Right now, ADD puts the new column at the end of each table it's added
> to, which often means that it gets a different column number in child
> tables than in parent tables.  That leads to havoc for pg_dump.
>

Inheritance is one of the reason why I didn't take #2. I don't understand 
marking is_dropped is needed or not when pg_attribute is overhauled
for inheritance.
I myself have never wanted to use current inheritance functionality
mainly because of this big flaw. Judging from the recent discussion
about oo(though I don't understand details),the change seems to be
needed in order to make inheritance functionality really useful. 
  
> I think the only clean solution is to create a clear distinction between
> physical and logical column numbers.  Each pg_attribute tuple would need
> two attnum fields, and pg_class would need two relnatts fields as well.
> A column once created would never change its physical column number, but

I don't understand inheritance well. In the near future wouldn't the
implementation require e.g. attid which is common to all children
of a parent and is never changed ? If so,we would need the third 
attid field which is irrevalent to physical/logical position. If not,
physical column number would be sufficient . 
 
> its logical column number might change as a consequence of adding or
> dropping columns before it.  ADD COLUMN would ensure that a column added
> to child tables receives the same logical column number as it has in the
> parent table, thus solving the dump/reload problem.  DROP COLUMN would
> assign an invalid logical column number to dropped columns.  They could
> be numbered zero except that we'd probably still want a unique index on
> attrelid+attnum, and the index would complain.  I'd suggest using
> Hiroshi's idea: give a dropped column a logical attnum equal to
> -(physical_attnum + offset).
> 
> With this approach, internal operations on tuples would all use
> physical column numbers, but operations that interface to the outside
> world would present a view of only the valid logical columns.  For
> example, the parser would only allow logical columns to be referenced
> by name; "SELECT *" would expand to valid logical columns in logical-
> column-number order; COPY would send or receive valid logical columns
> in logical-column-number order; etc.
> 
> Stored rules and so forth probably should store physical column numbers
> so that they need not be modified during column add/drop.
> 
> This would require looking at all the places in the backend to determine
> whether they should be working with logical or physical column numbers,
> but the design is such that most all places would want to be using
> physical numbers, so I don't think it'd be too painful.
> 
> Although I'd prefer to give the replacement columns two new names
> (eg, "attlnum" and "attpnum") to ensure we find all uses, this would
> surely break applications that examine pg_attribute.  For compatibility
> we'd have to recycle "attnum" and "relnatts" to indicate logical column
> number and logical column count, while adding new fields (say "attpnum"
> and "relnpatts") for the physical number and count.
>

I agree with you that we would add attpnum and change the meaing of
attnum as logical column number for backward compatibility.

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

From pgsql-hackers-owner+M3050@postgresql.org Thu Jan 11 21:49:43 2001
Received: from mail.postgresql.org (webmail.postgresql.org [216.126.85.28])
	by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id VAA20277
	for <pgman@candle.pha.pa.us>; Thu, 11 Jan 2001 21:49:42 -0500 (EST)
Received: from mail.postgresql.org (webmail.postgresql.org [216.126.85.28])
	by mail.postgresql.org (8.11.1/8.11.1) with SMTP id f0C2lhp74989;
	Thu, 11 Jan 2001 21:47:43 -0500 (EST)
	(envelope-from pgsql-hackers-owner+M3050@postgresql.org)
Received: from dynworks.com (adsl-63-206-168-198.dsl.sktn01.pacbell.net [63.206.168.198])
	by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f0C2lNp74855
	for <pgsql-hackers@postgresql.org>; Thu, 11 Jan 2001 21:47:23 -0500 (EST)
	(envelope-from jdavis@dynworks.com)
Received: from localhost.localdomain (localhost.localdomain [127.0.0.1])
	by dynworks.com (Postfix) with ESMTP id CC44F31FAB
	for <pgsql-hackers@postgresql.org>; Thu, 11 Jan 2001 18:48:36 -0800 (PST)
Date: Thu, 11 Jan 2001 18:48:36 PST
From: Jeff Davis <jdavis@dynworks.com>
To: pgsql-hackers@postgresql.org
Subject: [HACKERS] alter table drop column
Reply-To: jdavis@dynworks.com
X-Mailer: Spruce 0.6.5 for X11 w/smtpio 0.7.9
MIME-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: 8bit
Message-Id: <20010112024836.CC44F31FAB@dynworks.com>
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
Status: OR


I read the transcript of the alter table drop column discussion (old
discussion) at http://www.postgresql.org/docs/pgsql/doc/TODO.detail/drop,
and I have something to add:

People mentioned such ideas as a hidden column and a really deleted column,
and it occurred to me that perhaps "vacuum" would be a good option to use.
When a delete was issued, the column would be hidden (by a negative/invalid
logical column number, it appears was the consensus). Upon issuing a
vacuum, it could perform a complete deletion. This method would allow users
to know that the process may take a while (I think the agreed method for a
complete delete was to "select into..." the right columns and leave out the
deleted ones, then delete the old table).

Furthermore, I liked the idea of some kind of "undelete", as long as it was
just hidden. This could apply to anything that is cleaned out with a vacuum
(before it is cleaned out), although I am not sure how feasible this is,
and it isn't particularly important to me.

Regards,
	Jeff

-- 
Jeff Davis
Dynamic Works
jdavis@dynworks.com
http://dynworks.com


From owner-pgsql-hackers@hub.org Sat Feb 26 01:07:45 2000
Received: from hub.org (hub.org [216.126.84.1])
	by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id BAA17776
	for <pgman@candle.pha.pa.us>; Sat, 26 Feb 2000 01:07:43 -0500 (EST)
Received: from localhost (majordom@localhost)
	by hub.org (8.9.3/8.9.3) with SMTP id BAA06232;
	Sat, 26 Feb 2000 01:03:53 -0500 (EST)
	(envelope-from owner-pgsql-hackers)
Received: by hub.org (bulk_mailer v1.5); Sat, 26 Feb 2000 01:03:26 -0500
Received: (from majordom@localhost)
	by hub.org (8.9.3/8.9.3) id BAA05808
	for pgsql-hackers-outgoing; Sat, 26 Feb 2000 01:02:28 -0500 (EST)
	(envelope-from owner-pgsql-hackers@postgreSQL.org)
Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2])
	by hub.org (8.9.3/8.9.3) with ESMTP id BAA05426
	for <pgsql-hackers@postgreSQL.org>; Sat, 26 Feb 2000 01:01:46 -0500 (EST)
	(envelope-from tgl@sss.pgh.pa.us)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id BAA14228;
	Sat, 26 Feb 2000 01:01:34 -0500 (EST)
To: Bruce Momjian <pgman@candle.pha.pa.us>
cc: Peter Eisentraut <peter_e@gmx.net>,
        PostgreSQL Development <pgsql-hackers@postgreSQL.org>
Subject: Re: [HACKERS] ALTER TABLE DROP COLUMN 
In-reply-to: <200002260412.XAA14752@candle.pha.pa.us> 
References: <200002260412.XAA14752@candle.pha.pa.us>
Comments: In-reply-to Bruce Momjian <pgman@candle.pha.pa.us>
	message dated "Fri, 25 Feb 2000 23:12:26 -0500"
Date: Sat, 26 Feb 2000 01:01:33 -0500
Message-ID: <14225.951544893@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
Sender: owner-pgsql-hackers@postgreSQL.org
Status: ORr

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> You can exclusively lock the table, then do a heap_getnext() scan over
> the entire table, remove the dropped column, do a heap_insert(), then a
> heap_delete() on the current tuple, making sure to skip over the tuples
> inserted by the current transaction.  When completed, remove the column
> from pg_attribute, mark the transaction as committed (if desired), and
> run vacuum over the table to remove the deleted rows.

Hmm, that would work --- the new tuples commit at the same instant that
the schema updates commit, so it should be correct.  You have the 2x
disk usage problem, but there's no way around that without losing
rollback ability.

A potentially tricky bit will be persuading the tuple-reading and tuple-
writing subroutines to pay attention to different versions of the tuple
structure for the same table.  I haven't looked to see if this will be
difficult or not.  If you can pass the TupleDesc explicitly then it
shouldn't be a problem.

I'd suggest that the cleanup vacuum *not* be an automatic part of
the operation; just recommend that people do it ASAP after dropping
a column.  Consider needing to drop several columns...

			regards, tom lane

************

From pgsql-hackers-owner+M18768=candle.pha.pa.us=pgman@postgresql.org Wed Feb 13 03:52:00 2002
Return-path: <pgsql-hackers-owner+M18768=candle.pha.pa.us=pgman@postgresql.org>
Received: from server1.pgsql.org (www.postgresql.org [64.49.215.9])
	by candle.pha.pa.us (8.11.6/8.10.1) with SMTP id g1D8pxP21056
	for <pgman@candle.pha.pa.us>; Wed, 13 Feb 2002 03:52:00 -0500 (EST)
Received: (qmail 97959 invoked by alias); 13 Feb 2002 08:51:46 -0000
Received: from unknown (HELO postgresql.org) (64.49.215.8)
  by www.postgresql.org with SMTP; 13 Feb 2002 08:51:46 -0000
Received: from sd.tpf.co.jp (sd.tpf.co.jp [210.161.239.34])
	by postgresql.org (8.11.3/8.11.4) with SMTP id g1D8mRE97432
	for <pgsql-hackers@postgresql.org>; Wed, 13 Feb 2002 03:48:28 -0500 (EST)
	(envelope-from Inoue@tpf.co.jp)
Received: (qmail 26891 invoked from network); 13 Feb 2002 08:48:27 -0000
Received: from unknown (HELO viscomail.tpf.co.jp) (100.0.0.108)
  by sd2.tpf-fw-c.co.jp with SMTP; 13 Feb 2002 08:48:27 -0000
Received: from tpf.co.jp (3dgateway1 [126.0.1.60])
	by viscomail.tpf.co.jp (8.8.8+Sun/8.8.8) with ESMTP id RAA01019;
	Wed, 13 Feb 2002 17:48:20 +0900 (JST)
Message-ID: <3C6A2861.6E71A124@tpf.co.jp>
Date: Wed, 13 Feb 2002 17:48:33 +0900
From: Hiroshi Inoue <Inoue@tpf.co.jp>
X-Mailer: Mozilla 4.73 [ja] (Windows NT 5.0; U)
X-Accept-Language: ja
MIME-Version: 1.0
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
cc: Tom Lane <tgl@sss.pgh.pa.us>,
   Kovacs Zoltan <kovacsz@pc10.radnoti-szeged.sulinet.hu>,
   pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] alter table drop column status
References: <GNELIHDDFBOCMGBFGEFOMEFPCBAA.chriskl@familyhealth.com.au>
Content-Type: text/plain; charset=iso-2022-jp
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
Status: OR

Christopher Kings-Lynne wrote:
> 
> > No there was an unapplied hack which uses logical/physical
> > attribute numbers. I have synchronized it with cvs for a
> > year or so but stop it now. Though it had some flaws It
> > solved the following TODOs.
> >
> > * Add ALTER TABLE DROP COLUMN feature
> > * ALTER TABLE ADD COLUMN to inherited table put column in wrong place
> > * Prevent column dropping if column is used by foreign key
> 
> This seems fantastic - why can't this be committed?  Surely if it's
> committed then the flaws will fairly quickly be ironed out?  Even if it has
> flaws, then if we say 'this function is not yet stable' at least people can
> start testing it and reporting the problems?
> 
> > I gave up to apply the hack mainly because it may introduce
> > the maintenance headache.
> 
> Is it a maintenance headache just for you to keep it up to date, or how
> would it be a maintenance headache if it were committed?

Probably(oops I don't remember well now sorry) the main
reason why I didn't insist to apply the patch was that
it wasn't so clean as I had expected.
My trial implementation uses logical(for clients) and
physical (for backend internal) attribute numbers but
there were many places where I wasn't able to judge which
to use immediately. I'm pretty suspicious if a developer
could be careful about the choise when he is implementing
an irrevant feature. (Un)fortunately the numbers have
the same values mostly and he could hardly notice the
mistake even if he chose the wrong attribute numbers.
I'm not sure if I myself chose the right attribute numbers 
everywhere in my implementation.
In addtion (probably) there were some pretty essential
flaws. I intended to manage the backend internal
object references without the logical attribute
numbers but I found it difficult in some cases
(probably the handling of virtual(not existent 
in any real table) tuples).

Sorry it was more than 1 year ago when I implemented
it and I can't remember well what I'd thougth then.
Though I'd kept my local branch up to date for
about a year, it's about half a year since I touched
the stuff last. 

regards,
Hiroshi Inoue

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

From chriskl@familyhealth.com.au Thu Apr 11 12:00:22 2002
Return-path: <chriskl@familyhealth.com.au>
Received: from houston.familyhealth.com.au (root@i231-006.nv.iinet.net.au [203.59.231.6])
	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id g3BG0KS02910
	for <pgman@candle.pha.pa.us>; Thu, 11 Apr 2002 12:00:20 -0400 (EDT)
Received: from localhost (chriskl@localhost)
	by houston.familyhealth.com.au (8.11.6/8.11.6) with ESMTP id g3BG0GJ70765;
	Fri, 12 Apr 2002 00:00:16 +0800 (WST)
	(envelope-from chriskl@familyhealth.com.au)
Date: Fri, 12 Apr 2002 00:00:16 +0800 (WST)
From: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
To: Bruce Momjian <pgman@candle.pha.pa.us>
cc: Hiroshi Inoue <Inoue@tpf.co.jp>, Tom Lane <tgl@sss.pgh.pa.us>,
   <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] RFC: Restructuring pg_aggregate
In-Reply-To: <200204110419.g3B4J8v29682@candle.pha.pa.us>
Message-ID: <20020411233659.O69846-100000@houston.familyhealth.com.au>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
Status: OR

> Actually, what we need to do to reclaim space is to enable table
> recreation without the column, now that we have relfilenode for file
> renaming.  It isn't hard to do, but no one has focused on it.  I want to
> focus on it, but have not had the time, obviously, and would be very
> excited to assist someone else.
>
> Hiroshi's fine idea of marking certain columns as unused would not have
> reclaimed the missing space, just as my idea of physical/logical column
> distinction would not reclaim the space either.  Again, my
> physical/logical idea is more for fixing other problems and
> optimization, not DROP COLUMN.

Hmmm.  Personally, I think that a DROP COLUMN that cannot reclaim space is
kinda useless - you may as well just use a view!!!

So how would this occur?:

1. Lock target table for writing (allow reads)
2. Begin a table scan on target table, writing
   a new file with a particular filenode
3. Delete the attribute row from pg_attribute
4. Point the table in the catalog to the new filenode
5. Release locks
6. Commit transaction
7. Delete orhpan filenode

i. Upon postmaster startup, remove any orphaned filenodes

The real problem here is the fact that there are now missing attnos in
pg_attribute.  Either that's handled or we renumber the attnos - which is
also quite hard?

This, of course, suffers from the double size data problem - but I believe
that it does not matter - we just need to document it.

Interestingly enough, Oracle support

ALTER TABLE foo SET UNUSED col;

Which invalidates the attribute entry, and:

ALTER TABLE foo DROP col CHECKPOINT 1000;

Which actually reclaims the space.  The optional CHECKPOINT [n] clause
tells Oracle to do a checkpoint every [n] rows.

"Checkpointing cuts down the amount of undo logs accumulated during the
drop column operation to avoid running out of rollback segment space.
However, if this statement is interrupted after a checkpoint has been
applied, the table remains in an unusable state. While the table is
unusable, the only operations allowed on it are DROP TABLE, TRUNCATE
TABLE, and ALTER TABLE DROP COLUMNS CONTINUE (described below). "

Chris



From pgsql-hackers-owner+M21180@postgresql.org Thu Apr 11 12:02:54 2002
Return-path: <pgsql-hackers-owner+M21180@postgresql.org>
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id g3BG2sS03611
	for <pgman@candle.pha.pa.us>; Thu, 11 Apr 2002 12:02:54 -0400 (EDT)
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with SMTP
	id 6446B478F0A; Thu, 11 Apr 2002 12:01:19 -0400 (EDT)
Received: from houston.familyhealth.com.au (i231-006.nv.iinet.net.au [203.59.231.6])
	by postgresql.org (Postfix) with ESMTP id B6271478E4C
	for <pgsql-hackers@postgresql.org>; Thu, 11 Apr 2002 12:00:24 -0400 (EDT)
Received: from localhost (chriskl@localhost)
	by houston.familyhealth.com.au (8.11.6/8.11.6) with ESMTP id g3BG0GJ70765;
	Fri, 12 Apr 2002 00:00:16 +0800 (WST)
	(envelope-from chriskl@familyhealth.com.au)
Date: Fri, 12 Apr 2002 00:00:16 +0800 (WST)
From: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
To: Bruce Momjian <pgman@candle.pha.pa.us>
cc: Hiroshi Inoue <Inoue@tpf.co.jp>, Tom Lane <tgl@sss.pgh.pa.us>,
   <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] RFC: Restructuring pg_aggregate
In-Reply-To: <200204110419.g3B4J8v29682@candle.pha.pa.us>
Message-ID: <20020411233659.O69846-100000@houston.familyhealth.com.au>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
Status: ORr

> Actually, what we need to do to reclaim space is to enable table
> recreation without the column, now that we have relfilenode for file
> renaming.  It isn't hard to do, but no one has focused on it.  I want to
> focus on it, but have not had the time, obviously, and would be very
> excited to assist someone else.
>
> Hiroshi's fine idea of marking certain columns as unused would not have
> reclaimed the missing space, just as my idea of physical/logical column
> distinction would not reclaim the space either.  Again, my
> physical/logical idea is more for fixing other problems and
> optimization, not DROP COLUMN.

Hmmm.  Personally, I think that a DROP COLUMN that cannot reclaim space is
kinda useless - you may as well just use a view!!!

So how would this occur?:

1. Lock target table for writing (allow reads)
2. Begin a table scan on target table, writing
   a new file with a particular filenode
3. Delete the attribute row from pg_attribute
4. Point the table in the catalog to the new filenode
5. Release locks
6. Commit transaction
7. Delete orhpan filenode

i. Upon postmaster startup, remove any orphaned filenodes

The real problem here is the fact that there are now missing attnos in
pg_attribute.  Either that's handled or we renumber the attnos - which is
also quite hard?

This, of course, suffers from the double size data problem - but I believe
that it does not matter - we just need to document it.

Interestingly enough, Oracle support

ALTER TABLE foo SET UNUSED col;

Which invalidates the attribute entry, and:

ALTER TABLE foo DROP col CHECKPOINT 1000;

Which actually reclaims the space.  The optional CHECKPOINT [n] clause
tells Oracle to do a checkpoint every [n] rows.

"Checkpointing cuts down the amount of undo logs accumulated during the
drop column operation to avoid running out of rollback segment space.
However, if this statement is interrupted after a checkpoint has been
applied, the table remains in an unusable state. While the table is
unusable, the only operations allowed on it are DROP TABLE, TRUNCATE
TABLE, and ALTER TABLE DROP COLUMNS CONTINUE (described below). "

Chris



---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

From tgl@sss.pgh.pa.us Thu Apr 11 12:22:44 2002
Return-path: <tgl@sss.pgh.pa.us>
Received: from sss.pgh.pa.us (root@[192.204.191.242])
	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id g3BGMhS05541
	for <pgman@candle.pha.pa.us>; Thu, 11 Apr 2002 12:22:43 -0400 (EDT)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id g3BGMaF01827;
	Thu, 11 Apr 2002 12:22:36 -0400 (EDT)
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
cc: Bruce Momjian <pgman@candle.pha.pa.us>, Hiroshi Inoue <Inoue@tpf.co.jp>,
   pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] RFC: Restructuring pg_aggregate 
In-Reply-To: <20020411233659.O69846-100000@houston.familyhealth.com.au> 
References: <20020411233659.O69846-100000@houston.familyhealth.com.au>
Comments: In-reply-to Christopher Kings-Lynne <chriskl@familyhealth.com.au>
	message dated "Fri, 12 Apr 2002 00:00:16 +0800"
Date: Thu, 11 Apr 2002 12:22:35 -0400
Message-ID: <1824.1018542155@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
Status: ORr

Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> The real problem here is the fact that there are now missing attnos in
> pg_attribute.  Either that's handled or we renumber the attnos - which is
> also quite hard?

Updating pg_attribute per se is not so hard --- just store new copies of
all the rows for the table.  However, propagating the changes into other
places could be quite painful (I'm thinking of column numbers in stored
constraints, rules, etc).

It seems to me that reducing the column to NULLs already gets you the
majority of the space savings.  I don't think there is a case to be made
that getting back that last bit is worth the pain involved, either in
implementation effort or direct runtime costs (do you really want a DROP
COLUMN to force an immediate rewrite of the whole table?)

			regards, tom lane

From pgsql-hackers-owner+M21186@postgresql.org Thu Apr 11 13:03:13 2002
Return-path: <pgsql-hackers-owner+M21186@postgresql.org>
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id g3BH3DS08942
	for <pgman@candle.pha.pa.us>; Thu, 11 Apr 2002 13:03:13 -0400 (EDT)
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with SMTP
	id 517ED479415; Thu, 11 Apr 2002 12:29:32 -0400 (EDT)
Received: from sss.pgh.pa.us (unknown [192.204.191.242])
	by postgresql.org (Postfix) with ESMTP id B87BC479327
	for <pgsql-hackers@postgresql.org>; Thu, 11 Apr 2002 12:22:51 -0400 (EDT)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id g3BGMaF01827;
	Thu, 11 Apr 2002 12:22:36 -0400 (EDT)
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
cc: Bruce Momjian <pgman@candle.pha.pa.us>, Hiroshi Inoue <Inoue@tpf.co.jp>,
   pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] RFC: Restructuring pg_aggregate 
In-Reply-To: <20020411233659.O69846-100000@houston.familyhealth.com.au> 
References: <20020411233659.O69846-100000@houston.familyhealth.com.au>
Comments: In-reply-to Christopher Kings-Lynne <chriskl@familyhealth.com.au>
	message dated "Fri, 12 Apr 2002 00:00:16 +0800"
Date: Thu, 11 Apr 2002 12:22:35 -0400
Message-ID: <1824.1018542155@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
Status: OR

Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> The real problem here is the fact that there are now missing attnos in
> pg_attribute.  Either that's handled or we renumber the attnos - which is
> also quite hard?

Updating pg_attribute per se is not so hard --- just store new copies of
all the rows for the table.  However, propagating the changes into other
places could be quite painful (I'm thinking of column numbers in stored
constraints, rules, etc).

It seems to me that reducing the column to NULLs already gets you the
majority of the space savings.  I don't think there is a case to be made
that getting back that last bit is worth the pain involved, either in
implementation effort or direct runtime costs (do you really want a DROP
COLUMN to force an immediate rewrite of the whole table?)

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

From pgsql-hackers-owner+M21187@postgresql.org Thu Apr 11 13:25:05 2002
Return-path: <pgsql-hackers-owner+M21187@postgresql.org>
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id g3BHP4S10960
	for <pgman@candle.pha.pa.us>; Thu, 11 Apr 2002 13:25:05 -0400 (EDT)
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with SMTP
	id 2BC27479442; Thu, 11 Apr 2002 12:30:28 -0400 (EDT)
Received: from candle.pha.pa.us (216-55-132-35.dsl.san-diego.abac.net [216.55.132.35])
	by postgresql.org (Postfix) with ESMTP id 265E5479340
	for <pgsql-hackers@postgresql.org>; Thu, 11 Apr 2002 12:23:30 -0400 (EDT)
Received: (from pgman@localhost)
	by candle.pha.pa.us (8.11.6/8.10.1) id g3BGNS405576;
	Thu, 11 Apr 2002 12:23:28 -0400 (EDT)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-ID: <200204111623.g3BGNS405576@candle.pha.pa.us>
Subject: Re: [HACKERS] RFC: Restructuring pg_aggregate
In-Reply-To: <20020411233659.O69846-100000@houston.familyhealth.com.au>
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Date: Thu, 11 Apr 2002 12:23:28 -0400 (EDT)
cc: Hiroshi Inoue <Inoue@tpf.co.jp>, Tom Lane <tgl@sss.pgh.pa.us>,
   pgsql-hackers@postgresql.org
X-Mailer: ELM [version 2.4ME+ PL97 (25)]
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset=US-ASCII
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
Status: OR

Christopher Kings-Lynne wrote:
> > Actually, what we need to do to reclaim space is to enable table
> > recreation without the column, now that we have relfilenode for file
> > renaming.  It isn't hard to do, but no one has focused on it.  I want to
> > focus on it, but have not had the time, obviously, and would be very
> > excited to assist someone else.
> >
> > Hiroshi's fine idea of marking certain columns as unused would not have
> > reclaimed the missing space, just as my idea of physical/logical column
> > distinction would not reclaim the space either.  Again, my
> > physical/logical idea is more for fixing other problems and
> > optimization, not DROP COLUMN.
> 
> Hmmm.  Personally, I think that a DROP COLUMN that cannot reclaim space is
> kinda useless - you may as well just use a view!!!

Yep, kind of a problem.  It is a tradeoff between double diskspace/speed
and removing column from disk.  I guess that's why Oracle has both.

> 
> So how would this occur?:
> 
> 1. Lock target table for writing (allow reads)
> 2. Begin a table scan on target table, writing
>    a new file with a particular filenode
> 3. Delete the attribute row from pg_attribute
> 4. Point the table in the catalog to the new filenode
> 5. Release locks
> 6. Commit transaction
> 7. Delete orhpan filenode

Yep, something like that.  CLUSTER is a good start.  DROP COLUMN just
deals with the attno too.  You would have to renumber them to fill the
gap.

> i. Upon postmaster startup, remove any orphaned filenodes

Actually, we don't have a good solution for finding orphaned filenodes
right now.  I do have some code that tries to do this as part of VACUUM
but it was not 100% perfect, so it was rejected.  I am willing to open
the discussion to see if a perfect solution can be found.


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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

From pgsql-hackers-owner+M21190@postgresql.org Thu Apr 11 13:40:34 2002
Return-path: <pgsql-hackers-owner+M21190@postgresql.org>
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id g3BHeXS12137
	for <pgman@candle.pha.pa.us>; Thu, 11 Apr 2002 13:40:33 -0400 (EDT)
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with SMTP
	id 2BD6C479604; Thu, 11 Apr 2002 12:35:51 -0400 (EDT)
Received: from candle.pha.pa.us (216-55-132-35.dsl.san-diego.abac.net [216.55.132.35])
	by postgresql.org (Postfix) with ESMTP id 2DF9D47946A
	for <pgsql-hackers@postgresql.org>; Thu, 11 Apr 2002 12:31:25 -0400 (EDT)
Received: (from pgman@localhost)
	by candle.pha.pa.us (8.11.6/8.10.1) id g3BGVM806114;
	Thu, 11 Apr 2002 12:31:22 -0400 (EDT)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-ID: <200204111631.g3BGVM806114@candle.pha.pa.us>
Subject: Re: [HACKERS] RFC: Restructuring pg_aggregate
In-Reply-To: <1824.1018542155@sss.pgh.pa.us>
To: Tom Lane <tgl@sss.pgh.pa.us>
Date: Thu, 11 Apr 2002 12:31:22 -0400 (EDT)
cc: Christopher Kings-Lynne <chriskl@familyhealth.com.au>,
   Hiroshi Inoue <Inoue@tpf.co.jp>, pgsql-hackers@postgresql.org
X-Mailer: ELM [version 2.4ME+ PL97 (25)]
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset=US-ASCII
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
Status: OR

Tom Lane wrote:
> Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> > The real problem here is the fact that there are now missing attnos in
> > pg_attribute.  Either that's handled or we renumber the attnos - which is
> > also quite hard?
> 
> Updating pg_attribute per se is not so hard --- just store new copies of
> all the rows for the table.  However, propagating the changes into other
> places could be quite painful (I'm thinking of column numbers in stored
> constraints, rules, etc).
> 
> It seems to me that reducing the column to NULLs already gets you the
> majority of the space savings.  I don't think there is a case to be made
> that getting back that last bit is worth the pain involved, either in
> implementation effort or direct runtime costs (do you really want a DROP
> COLUMN to force an immediate rewrite of the whole table?)

That is an excellent point about having to fix all the places that refer
to attno.  In fact, we have been moving away from attname references to
attno references for a while, so it only gets worse.  Tom is also
correct that setting it to NULL removes the problem of disk space usage
quite easily.

That only leaves the problem of having gaps in the pg_attribute for that
relation, and as I remember, that was the problem for Hiroshi's DROP
COLUMN change, but at this point, after years of delay with no great
solution on the horizon, we may as well just get this working.

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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

From Inoue@tpf.co.jp Thu Apr 11 19:55:08 2002
Return-path: <Inoue@tpf.co.jp>
Received: from sd.tpf.co.jp (IDENT:qmailr@sd.tpf.co.jp [210.161.239.34])
	by candle.pha.pa.us (8.11.6/8.10.1) with SMTP id g3BNt6S19759
	for <pgman@candle.pha.pa.us>; Thu, 11 Apr 2002 19:55:06 -0400 (EDT)
Received: (qmail 31013 invoked from network); 11 Apr 2002 23:55:06 -0000
Received: from unknown (HELO viscomail.tpf.co.jp) (100.0.0.108)
  by sd2.tpf-fw-c.co.jp with SMTP; 11 Apr 2002 23:55:06 -0000
Received: from tpf.co.jp (3dgateway1 [126.0.1.60])
	by viscomail.tpf.co.jp (8.8.8+Sun/8.8.8) with ESMTP id IAA22335;
	Fri, 12 Apr 2002 08:55:05 +0900 (JST)
Message-ID: <3CB62298.88565A54@tpf.co.jp>
Date: Fri, 12 Apr 2002 08:56:08 +0900
From: Hiroshi Inoue <Inoue@tpf.co.jp>
X-Mailer: Mozilla 4.73 [ja] (Windows NT 5.0; U)
X-Accept-Language: ja
MIME-Version: 1.0
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
cc: Bruce Momjian <pgman@candle.pha.pa.us>, Tom Lane <tgl@sss.pgh.pa.us>,
   pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] RFC: Restructuring pg_aggregate
References: <20020411233659.O69846-100000@houston.familyhealth.com.au>
Content-Type: text/plain; charset=iso-2022-jp
Content-Transfer-Encoding: 7bit
Status: OR

Christopher Kings-Lynne wrote:
> 
> Hmmm.  Personally, I think that a DROP COLUMN that cannot reclaim space is
> kinda useless - you may as well just use a view!!!
> 
> So how would this occur?:
> 
> 1. Lock target table for writing (allow reads)
> 2. Begin a table scan on target table, writing
>    a new file with a particular filenode
> 3. Delete the attribute row from pg_attribute
> 4. Point the table in the catalog to the new filenode
> 5. Release locks
> 6. Commit transaction
> 7. Delete orhpan filenode
> 
> i. Upon postmaster startup, remove any orphaned filenodes
> 
> The real problem here is the fact that there are now missing attnos in
> pg_attribute.  Either that's handled or we renumber the attnos - which is
> also quite hard?

The attnos should be renumbered and it's easy.
But the above seems only 20% of the total implementation.
If the attnos are renumbered, all objects which refer to 
the numbers must be invalidated or re-compiled ...
For example the parameters of foreign key constraints
triggers are consist of relname and colnames currently.
There has been a proposal that change to use relid or
column numbers instead. Certainly it makes RENAME happy
but DROP COLUMN unhappy. If there's a foreign key a_rel/1/3
and the second column of the relation is dropped the
parameter must be changed to be a_rel/1/2. If neither
foreign key stuff nor DROP COLUMN take the other into
account, the consistency is easily broken. 

regards,
Hiroshi Inoue

From pgsql-hackers-owner+M21205@postgresql.org Thu Apr 11 19:56:20 2002
Return-path: <pgsql-hackers-owner+M21205@postgresql.org>
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id g3BNuJS19855
	for <pgman@candle.pha.pa.us>; Thu, 11 Apr 2002 19:56:19 -0400 (EDT)
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with SMTP
	id 2B38A47656E; Thu, 11 Apr 2002 19:55:57 -0400 (EDT)
Received: from sd.tpf.co.jp (sd.tpf.co.jp [210.161.239.34])
	by postgresql.org (Postfix) with SMTP id 6C92E475C96
	for <pgsql-hackers@postgresql.org>; Thu, 11 Apr 2002 19:55:04 -0400 (EDT)
Received: (qmail 31013 invoked from network); 11 Apr 2002 23:55:06 -0000
Received: from unknown (HELO viscomail.tpf.co.jp) (100.0.0.108)
  by sd2.tpf-fw-c.co.jp with SMTP; 11 Apr 2002 23:55:06 -0000
Received: from tpf.co.jp (3dgateway1 [126.0.1.60])
	by viscomail.tpf.co.jp (8.8.8+Sun/8.8.8) with ESMTP id IAA22335;
	Fri, 12 Apr 2002 08:55:05 +0900 (JST)
Message-ID: <3CB62298.88565A54@tpf.co.jp>
Date: Fri, 12 Apr 2002 08:56:08 +0900
From: Hiroshi Inoue <Inoue@tpf.co.jp>
X-Mailer: Mozilla 4.73 [ja] (Windows NT 5.0; U)
X-Accept-Language: ja
MIME-Version: 1.0
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
cc: Bruce Momjian <pgman@candle.pha.pa.us>, Tom Lane <tgl@sss.pgh.pa.us>,
   pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] RFC: Restructuring pg_aggregate
References: <20020411233659.O69846-100000@houston.familyhealth.com.au>
Content-Type: text/plain; charset=iso-2022-jp
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
Status: ORr

Christopher Kings-Lynne wrote:
> 
> Hmmm.  Personally, I think that a DROP COLUMN that cannot reclaim space is
> kinda useless - you may as well just use a view!!!
> 
> So how would this occur?:
> 
> 1. Lock target table for writing (allow reads)
> 2. Begin a table scan on target table, writing
>    a new file with a particular filenode
> 3. Delete the attribute row from pg_attribute
> 4. Point the table in the catalog to the new filenode
> 5. Release locks
> 6. Commit transaction
> 7. Delete orhpan filenode
> 
> i. Upon postmaster startup, remove any orphaned filenodes
> 
> The real problem here is the fact that there are now missing attnos in
> pg_attribute.  Either that's handled or we renumber the attnos - which is
> also quite hard?

The attnos should be renumbered and it's easy.
But the above seems only 20% of the total implementation.
If the attnos are renumbered, all objects which refer to 
the numbers must be invalidated or re-compiled ...
For example the parameters of foreign key constraints
triggers are consist of relname and colnames currently.
There has been a proposal that change to use relid or
column numbers instead. Certainly it makes RENAME happy
but DROP COLUMN unhappy. If there's a foreign key a_rel/1/3
and the second column of the relation is dropped the
parameter must be changed to be a_rel/1/2. If neither
foreign key stuff nor DROP COLUMN take the other into
account, the consistency is easily broken. 

regards,
Hiroshi Inoue

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

From pgsql-hackers-owner+M21209@postgresql.org Thu Apr 11 22:27:40 2002
Return-path: <pgsql-hackers-owner+M21209@postgresql.org>
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id g3C2ReS27660
	for <pgman@candle.pha.pa.us>; Thu, 11 Apr 2002 22:27:40 -0400 (EDT)
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with SMTP
	id A89AF4766D0; Thu, 11 Apr 2002 22:27:35 -0400 (EDT)
Received: from candle.pha.pa.us (216-55-132-35.dsl.san-diego.abac.net [216.55.132.35])
	by postgresql.org (Postfix) with ESMTP id 4CE13475EB9
	for <pgsql-hackers@postgresql.org>; Thu, 11 Apr 2002 22:26:25 -0400 (EDT)
Received: (from pgman@localhost)
	by candle.pha.pa.us (8.11.6/8.10.1) id g3C2Q5I27551;
	Thu, 11 Apr 2002 22:26:05 -0400 (EDT)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-ID: <200204120226.g3C2Q5I27551@candle.pha.pa.us>
Subject: Re: [HACKERS] RFC: Restructuring pg_aggregate
In-Reply-To: <3CB62298.88565A54@tpf.co.jp>
To: Hiroshi Inoue <Inoue@tpf.co.jp>
Date: Thu, 11 Apr 2002 22:26:05 -0400 (EDT)
cc: Christopher Kings-Lynne <chriskl@familyhealth.com.au>,
   Tom Lane <tgl@sss.pgh.pa.us>, pgsql-hackers@postgresql.org
X-Mailer: ELM [version 2.4ME+ PL97 (25)]
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset=US-ASCII
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
Status: OR

Hiroshi Inoue wrote:
> Christopher Kings-Lynne wrote:
> > 
> > Hmmm.  Personally, I think that a DROP COLUMN that cannot reclaim space is
> > kinda useless - you may as well just use a view!!!
> > 
> > So how would this occur?:
> > 
> > 1. Lock target table for writing (allow reads)
> > 2. Begin a table scan on target table, writing
> >    a new file with a particular filenode
> > 3. Delete the attribute row from pg_attribute
> > 4. Point the table in the catalog to the new filenode
> > 5. Release locks
> > 6. Commit transaction
> > 7. Delete orhpan filenode
> > 
> > i. Upon postmaster startup, remove any orphaned filenodes
> > 
> > The real problem here is the fact that there are now missing attnos in
> > pg_attribute.  Either that's handled or we renumber the attnos - which is
> > also quite hard?
> 
> The attnos should be renumbered and it's easy.
> But the above seems only 20% of the total implementation.
> If the attnos are renumbered, all objects which refer to 
> the numbers must be invalidated or re-compiled ...
> For example the parameters of foreign key constraints
> triggers are consist of relname and colnames currently.
> There has been a proposal that change to use relid or
> column numbers instead. Certainly it makes RENAME happy
> but DROP COLUMN unhappy. If there's a foreign key a_rel/1/3
> and the second column of the relation is dropped the
> parameter must be changed to be a_rel/1/2. If neither
> foreign key stuff nor DROP COLUMN take the other into
> account, the consistency is easily broken. 

I think that is why Tom was suggesting making all the column values NULL
and removing the pg_attribute row for the column.  With a NULL value, it
doesn't take up any room in the tuple, and with the pg_attribute column
gone, no one will see that row.  The only problem is the gap in attno
numbering.  How big a problem is that?

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

From pgsql-hackers-owner+M21211@postgresql.org Thu Apr 11 22:55:44 2002
Return-path: <pgsql-hackers-owner+M21211@postgresql.org>
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id g3C2tiS29394
	for <pgman@candle.pha.pa.us>; Thu, 11 Apr 2002 22:55:44 -0400 (EDT)
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with SMTP
	id B86AF476739; Thu, 11 Apr 2002 22:55:39 -0400 (EDT)
Received: from sss.pgh.pa.us (unknown [192.204.191.242])
	by postgresql.org (Postfix) with ESMTP id 56D8747593C
	for <pgsql-hackers@postgresql.org>; Thu, 11 Apr 2002 22:54:26 -0400 (EDT)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id g3C2s1F24007;
	Thu, 11 Apr 2002 22:54:01 -0400 (EDT)
To: Bruce Momjian <pgman@candle.pha.pa.us>
cc: Hiroshi Inoue <Inoue@tpf.co.jp>,
   Christopher Kings-Lynne <chriskl@familyhealth.com.au>,
   pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] RFC: Restructuring pg_aggregate 
In-Reply-To: <200204120226.g3C2Q5I27551@candle.pha.pa.us> 
References: <200204120226.g3C2Q5I27551@candle.pha.pa.us>
Comments: In-reply-to Bruce Momjian <pgman@candle.pha.pa.us>
	message dated "Thu, 11 Apr 2002 22:26:05 -0400"
Date: Thu, 11 Apr 2002 22:54:01 -0400
Message-ID: <24004.1018580041@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
Status: OR

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I think that is why Tom was suggesting making all the column values NULL
> and removing the pg_attribute row for the column.

That was not my suggestion.

> With a NULL value, it
> doesn't take up any room in the tuple, and with the pg_attribute column
> gone, no one will see that row.  The only problem is the gap in attno
> numbering.  How big a problem is that?

You can't do it that way unless you're intending to rewrite all rows of
the relation before committing the ALTER; which would be the worst of
both worlds.  The pg_attribute row *must* be retained to show the
datatype of the former column, so that we can correctly skip over it
in tuples where the column isn't yet nulled out.

Hiroshi did this by renumbering the attnum; I propose leaving attnum
alone and instead adding an attisdropped flag.  That would avoid
creating a gap in the column numbers, but either way is likely to affect
some applications that inspect pg_attribute.

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

From pgsql-hackers-owner+M21214@postgresql.org Fri Apr 12 00:09:26 2002
Return-path: <pgsql-hackers-owner+M21214@postgresql.org>
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id g3C49PS05093
	for <pgman@candle.pha.pa.us>; Fri, 12 Apr 2002 00:09:25 -0400 (EDT)
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with SMTP
	id B1BE6476810; Fri, 12 Apr 2002 00:09:20 -0400 (EDT)
Received: from sd.tpf.co.jp (sd.tpf.co.jp [210.161.239.34])
	by postgresql.org (Postfix) with SMTP id A8E07476444
	for <pgsql-hackers@postgresql.org>; Fri, 12 Apr 2002 00:08:22 -0400 (EDT)
Received: (qmail 25808 invoked from network); 12 Apr 2002 04:08:26 -0000
Received: from unknown (HELO viscomail.tpf.co.jp) (100.0.0.108)
  by sd2.tpf-fw-c.co.jp with SMTP; 12 Apr 2002 04:08:26 -0000
Received: from tpf.co.jp (3dgateway1 [126.0.1.60])
	by viscomail.tpf.co.jp (8.8.8+Sun/8.8.8) with ESMTP id NAA22497;
	Fri, 12 Apr 2002 13:08:24 +0900 (JST)
Message-ID: <3CB65DF7.8FCFC024@tpf.co.jp>
Date: Fri, 12 Apr 2002 13:09:28 +0900
From: Hiroshi Inoue <Inoue@tpf.co.jp>
X-Mailer: Mozilla 4.73 [ja] (Windows NT 5.0; U)
X-Accept-Language: ja
MIME-Version: 1.0
To: Bruce Momjian <pgman@candle.pha.pa.us>
cc: Christopher Kings-Lynne <chriskl@familyhealth.com.au>,
   Tom Lane <tgl@sss.pgh.pa.us>, pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] RFC: Restructuring pg_aggregate
References: <200204120226.g3C2Q5I27551@candle.pha.pa.us>
Content-Type: text/plain; charset=iso-2022-jp
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
Status: OR

Bruce Momjian wrote:
> 
> Hiroshi Inoue wrote:
> > Christopher Kings-Lynne wrote:
> > >
> I think that is why Tom was suggesting making all the column values NULL
> and removing the pg_attribute row for the column.  With a NULL value, it
> doesn't take up any room in the tuple, and with the pg_attribute column
> gone, no one will see that row.  The only problem is the gap in attno
> numbering.  How big a problem is that?

There's no problem with applications which don't inquire
of system catalogs(pg_attribute). Unfortunately we have 
been very tolerant of users' access on system tables and
there would be pretty many applications which inquire of
pg_attribute.

regards,
Hiroshi Inoue

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

From pgsql-hackers-owner+M21221@postgresql.org Fri Apr 12 05:11:00 2002
Return-path: <pgsql-hackers-owner+M21221@postgresql.org>
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id g3C9AxS28516
	for <pgman@candle.pha.pa.us>; Fri, 12 Apr 2002 05:11:00 -0400 (EDT)
Received: from postgresql.org (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with SMTP
	id 28FF0476B9D; Fri, 12 Apr 2002 04:35:54 -0400 (EDT)
Received: from tele-post-20.mail.demon.net (tele-post-20.mail.demon.net [194.217.242.20])
	by postgresql.org (Postfix) with ESMTP id BFDE74769AC
	for <pgsql-hackers@postgresql.org>; Fri, 12 Apr 2002 04:30:29 -0400 (EDT)
Received: from mailgate.vale-housing.co.uk ([193.195.77.162] helo=dogbert.vale-housing.co.uk)
	by tele-post-20.mail.demon.net with esmtp (Exim 3.35 #1)
	id 16vwRc-0006GP-0K; Fri, 12 Apr 2002 08:30:08 +0000
Received: by dogbert.vale-housing.co.uk with Internet Mail Service (5.5.2650.21)
	id <2H2ZS6HB>; Fri, 12 Apr 2002 09:35:53 +0100
Message-ID: <FED2B709E3270E4B903EB0175A49BCB1293387@dogbert.vale-housing.co.uk>
From: Dave Page <dpage@vale-housing.co.uk>
To: "'Tom Lane'" <tgl@sss.pgh.pa.us>, Bruce Momjian <pgman@candle.pha.pa.us>
cc: Hiroshi Inoue <Inoue@tpf.co.jp>,
   Christopher Kings-Lynne <chriskl@familyhealth.com.au>,
   pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] RFC: Restructuring pg_aggregate 
Date: Fri, 12 Apr 2002 09:35:52 +0100
MIME-Version: 1.0
X-Mailer: Internet Mail Service (5.5.2650.21)
Content-Type: text/plain
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
Status: OR



> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us] 
> Sent: 12 April 2002 03:54
> To: Bruce Momjian
> Cc: Hiroshi Inoue; Christopher Kings-Lynne; 
> pgsql-hackers@postgresql.org
> Subject: Re: RFC: Restructuring pg_aggregate 
> 
> 
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I think that is why Tom was suggesting making all the column values 
> > NULL and removing the pg_attribute row for the column.
> 
> That was not my suggestion.
> 
> > With a NULL value, it
> > doesn't take up any room in the tuple, and with the pg_attribute 
> > column gone, no one will see that row.  The only problem is 
> the gap in 
> > attno numbering.  How big a problem is that?
> 
> You can't do it that way unless you're intending to rewrite 
> all rows of the relation before committing the ALTER; which 
> would be the worst of both worlds.  The pg_attribute row 
> *must* be retained to show the datatype of the former column, 
> so that we can correctly skip over it in tuples where the 
> column isn't yet nulled out.
> 
> Hiroshi did this by renumbering the attnum; I propose leaving 
> attnum alone and instead adding an attisdropped flag.  That 
> would avoid creating a gap in the column numbers, but either 
> way is likely to affect some applications that inspect pg_attribute.

Applications like pgAdmin that inspect pg_attribute are being seriously
hacked to incorporate schema support anyway for 7.3. Personnally I'd be glad
to spend some time re-coding to allow for this, just to not have to answer
the numerous 'how do I drop a column' emails I get reguarly.

Regards, Dave.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

From chriskl@familyhealth.com.au Sat Apr 13 02:25:23 2002
Return-path: <chriskl@familyhealth.com.au>
Received: from mail.iinet.net.au (symphony-01.iinet.net.au [203.59.3.33])
	by candle.pha.pa.us (8.11.6/8.10.1) with SMTP id g3D6PLS06807
	for <pgman@candle.pha.pa.us>; Sat, 13 Apr 2002 02:25:22 -0400 (EDT)
Received: (qmail 7569 invoked by uid 666); 13 Apr 2002 06:25:20 -0000
Received: from unknown (HELO SOL) (203.59.103.193)
  by mail.iinet.net.au with SMTP; 13 Apr 2002 06:25:20 -0000
Message-ID: <001701c1e2b2$e7b10a40$0200a8c0@SOL>
From: "Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
cc: "Bruce Momjian" <pgman@candle.pha.pa.us>,
   "Hiroshi Inoue" <Inoue@tpf.co.jp>, <pgsql-hackers@postgresql.org>
References: <20020411233659.O69846-100000@houston.familyhealth.com.au> <1824.1018542155@sss.pgh.pa.us>
Subject: Re: [HACKERS] RFC: Restructuring pg_aggregate 
Date: Sat, 13 Apr 2002 14:17:34 +0800
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 5.50.4522.1200
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200
Status: OR

> Updating pg_attribute per se is not so hard --- just store new copies of
> all the rows for the table.  However, propagating the changes into other
> places could be quite painful (I'm thinking of column numbers in stored
> constraints, rules, etc).
>
> It seems to me that reducing the column to NULLs already gets you the
> majority of the space savings.  I don't think there is a case to be made
> that getting back that last bit is worth the pain involved, either in
> implementation effort or direct runtime costs (do you really want a DROP
> COLUMN to force an immediate rewrite of the whole table?)

OK, sounds fair.  However, is there a more aggressive way of reclaiming the
space?  The problem with updating all the rows to null for that column is
that the on-disk size is doubled anyway, right?  So, could a VACUUM FULL
process do the nulling for us?  Vacuum works outside of normal transaction
constraints anyway...?

Also, it seems to me that at some point we are forced to break client
compatibility.  Either we add attisdropped field to pg_attribute, or we use
Hiroshi's (-1 * attnum - offset) idea.  Both Tom and Hiroshi have good
reasons for each of these - would it be possible for you guys to post with
your reasons for and against both the techniques.  I just want to get to an
implementation specification we all agree on that can be written up and then
the coding can proceed.  Maybe we should add it to the 'Postgres Major
Projects' page - and remove those old ones that have already been
implemented.

Chris



From Inoue@tpf.co.jp Sun Apr 14 23:47:08 2002
Return-path: <Inoue@tpf.co.jp>
Received: from sd.tpf.co.jp (IDENT:qmailr@sd.tpf.co.jp [210.161.239.34])
	by candle.pha.pa.us (8.11.6/8.10.1) with SMTP id g3F3l6S23155
	for <pgman@candle.pha.pa.us>; Sun, 14 Apr 2002 23:47:07 -0400 (EDT)
Received: (qmail 9638 invoked from network); 15 Apr 2002 03:47:06 -0000
Received: from unknown (HELO viscomail.tpf.co.jp) (100.0.0.108)
  by sd2.tpf-fw-c.co.jp with SMTP; 15 Apr 2002 03:47:06 -0000
Received: from tpf.co.jp (3dgateway1 [126.0.1.60])
	by viscomail.tpf.co.jp (8.8.8+Sun/8.8.8) with ESMTP id MAA24068;
	Mon, 15 Apr 2002 12:47:04 +0900 (JST)
Message-ID: <3CBA4D7A.9E61DECA@tpf.co.jp>
Date: Mon, 15 Apr 2002 12:48:10 +0900
From: Hiroshi Inoue <Inoue@tpf.co.jp>
X-Mailer: Mozilla 4.73 [ja] (Windows NT 5.0; U)
X-Accept-Language: ja
MIME-Version: 1.0
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
cc: Tom Lane <tgl@sss.pgh.pa.us>, Bruce Momjian <pgman@candle.pha.pa.us>,
   pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] RFC: Restructuring pg_aggregate
References: <20020411233659.O69846-100000@houston.familyhealth.com.au> <1824.1018542155@sss.pgh.pa.us> <001701c1e2b2$e7b10a40$0200a8c0@SOL>
Content-Type: text/plain; charset=iso-2022-jp
Content-Transfer-Encoding: 7bit
Status: OR

Christopher Kings-Lynne wrote:
> 
> Also, it seems to me that at some point we are forced to break client
> compatibility.

It's not a users' consensus at all. I'm suspicious if
DROP COLUMN is such a significant feature to break
client compatibility at our ease.

> Either we add attisdropped field to pg_attribute, or we use
> Hiroshi's (-1 * attnum - offset) idea.  Both Tom and Hiroshi have good
> reasons for each of these - would it be possible for you guys to post with
> your reasons for and against both the techniques. 

I don't object to adding attisdropped field. What
I meant to say is that the differene is very small.

regards,
Hiroshi Inoue