CVSROOT:	/cvsroot
Module name:	pgsql-server
Changes by:	tgl@svr1.postgresql.org	04/05/05 01:48:48

Modified files:
	doc/src/sgml/ref: alter_table.sgml 
	src/backend/bootstrap: bootparse.y 
	src/backend/catalog: dependency.c heap.c index.c 
	src/backend/commands: cluster.c indexcmds.c tablecmds.c 
	src/backend/nodes: copyfuncs.c equalfuncs.c 
	src/backend/parser: analyze.c gram.y 
	src/backend/tcop: utility.c 
	src/backend/utils/adt: ruleutils.c 
	src/include/catalog: dependency.h heap.h index.h 
	src/include/commands: cluster.h defrem.h tablecmds.h 
	src/include/nodes: nodes.h parsenodes.h 
	src/include/parser: analyze.h 
	src/include/utils: builtins.h 
	src/test/regress/expected: alter_table.out foreign_key.out 
	                           inherit.out 
	src/test/regress/sql: alter_table.sql foreign_key.sql 
	                      inherit.sql 

Log message:
	ALTER TABLE rewrite.  New cool stuff:
	
	* ALTER ... ADD COLUMN with defaults and NOT NULL constraints works per SQL
	spec.  A default is implemented by rewriting the table with the new value
	stored in each row.
	
	* ALTER COLUMN TYPE.  You can change a column's datatype to anything you
	want, so long as you can specify how to convert the old value.  Rewrites
	the table.  (Possible future improvement: optimize no-op conversions such
	as varchar(N) to varchar(N+1).)
	
	* Multiple ALTER actions in a single ALTER TABLE command.  You can perform
	any number of column additions, type changes, and constraint additions with
	only one pass over the table contents.
	
	Basic documentation provided in ALTER TABLE ref page, but some more docs
	work is needed.
	
	Original patch from Rod Taylor, additional work from Tom Lane.


Wed,  5 May 2004 01:48:48 -0300 (ADT)
tgl@svr1.postgresql.org (Tom Lane)
pgsql-server/ oc/src/sgml/ref/alter_table.sgml ...
> 	* ALTER COLUMN TYPE.  You can change a column's datatype to anything you
> 	want, so long as you can specify how to convert the old value.  Rewrites
> 	the table.  (Possible future improvement: optimize no-op conversions such
> 	as varchar(N) to varchar(N+1).)

That seriously makes me a happy little camper!  Tell the world!

Chris


Wed, 05 May 2004 13:27:09 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: pgsql-server/ oc/src/sgml/ref/alter_table.sgml ...

TODO items completed:

        o -ALTER TABLE ADD COLUMN does not honor DEFAULT and non-CHECK CONSTRAINT
        o -ALTER TABLE ADD COLUMN column DEFAULT should fill existing
          rows with DEFAULT value
        o -Allow ALTER TABLE to modify column lengths and change to binary
          compatible types

Seems we didn't have ALTER COLUMN TYPE on the TODO list.

Do we still want this TODO?

        o Allow columns to be reordered using ALTER ... POSITION i col1 [,col2];
          have SELECT * and INSERT honor such ordering

I don't think so.  As I remember it was part of doing logical attribute
numbers as a way to add ALTER COLUMN TYPE, but because we now use table
recreate to implement this, it is unlikely we will ever add logical
attribute numbers (adds too much complexity to the code).

---------------------------------------------------------------------------

Tom Lane wrote:
> CVSROOT:	/cvsroot
> Module name:	pgsql-server
> Changes by:	tgl@svr1.postgresql.org	04/05/05 01:48:48
> 
> Modified files:
> 	doc/src/sgml/ref: alter_table.sgml 
> 	src/backend/bootstrap: bootparse.y 
> 	src/backend/catalog: dependency.c heap.c index.c 
> 	src/backend/commands: cluster.c indexcmds.c tablecmds.c 
> 	src/backend/nodes: copyfuncs.c equalfuncs.c 
> 	src/backend/parser: analyze.c gram.y 
> 	src/backend/tcop: utility.c 
> 	src/backend/utils/adt: ruleutils.c 
> 	src/include/catalog: dependency.h heap.h index.h 
> 	src/include/commands: cluster.h defrem.h tablecmds.h 
> 	src/include/nodes: nodes.h parsenodes.h 
> 	src/include/parser: analyze.h 
> 	src/include/utils: builtins.h 
> 	src/test/regress/expected: alter_table.out foreign_key.out 
> 	                           inherit.out 
> 	src/test/regress/sql: alter_table.sql foreign_key.sql 
> 	                      inherit.sql 
> 
> Log message:
> 	ALTER TABLE rewrite.  New cool stuff:
> 	
> 	* ALTER ... ADD COLUMN with defaults and NOT NULL constraints works per SQL
> 	spec.  A default is implemented by rewriting the table with the new value
> 	stored in each row.
> 	
> 	* ALTER COLUMN TYPE.  You can change a column's datatype to anything you
> 	want, so long as you can specify how to convert the old value.  Rewrites
> 	the table.  (Possible future improvement: optimize no-op conversions such
> 	as varchar(N) to varchar(N+1).)
> 	
> 	* Multiple ALTER actions in a single ALTER TABLE command.  You can perform
> 	any number of column additions, type changes, and constraint additions with
> 	only one pass over the table contents.
> 	
> 	Basic documentation provided in ALTER TABLE ref page, but some more docs
> 	work is needed.
> 	
> 	Original patch from Rod Taylor, additional work from Tom Lane.
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Wed, 5 May 2004 10:36:36 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
ALTER TABLE TODO items
* Bruce Momjian (pgman@candle.pha.pa.us) wrote:
> Do we still want this TODO?
> 
>         o Allow columns to be reordered using ALTER ... POSITION i col1 [,col2];
>           have SELECT * and INSERT honor such ordering
> 
> I don't think so.  As I remember it was part of doing logical attribute
> numbers as a way to add ALTER COLUMN TYPE, but because we now use table
> recreate to implement this, it is unlikely we will ever add logical
> attribute numbers (adds too much complexity to the code).

Does using table recreate break views built against the table?  I recall
someone saying the old way did, it would be very nice if that could be
fixed or at least added to the TODO.  I like the idea of being able to
alter the positions of the columns too, but that's not as big of an
issue as breaking views.

	Stephen
Wed, 5 May 2004 11:30:36 -0400
Stephen Frost <sfrost@snowman.net>
Re: ALTER TABLE TODO items
Stephen Frost wrote:
> > I don't think so.  As I remember it was part of doing logical attribute
> > numbers as a way to add ALTER COLUMN TYPE, but because we now use table
> > recreate to implement this, it is unlikely we will ever add logical
> > attribute numbers (adds too much complexity to the code).
> 
> Does using table recreate break views built against the table?  I recall
> someone saying the old way did, it would be very nice if that could be
> fixed or at least added to the TODO.  I like the idea of being able to
> alter the positions of the columns too, but that's not as big of an
> issue as breaking views.

Yea, I think it will break views. You have to recreate them.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Wed, 5 May 2004 11:38:34 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: ALTER TABLE TODO items
Bruce Momjian wrote:
> Do we still want this TODO?
> 
>         o Allow columns to be reordered using ALTER ... POSITION i col1 [,col2];
>           have SELECT * and INSERT honor such ordering
> 
> I don't think so.  As I remember it was part of doing logical attribute
> numbers as a way to add ALTER COLUMN TYPE, but because we now use table
> recreate to implement this, it is unlikely we will ever add logical
> attribute numbers (adds too much complexity to the code).

Well, I manage a DB that is up and running 24/24 7/7 since 3 years now,
the only off working time was during the engine update.

At the beginning with few hundred record on each table, in order to add
a column in the desidered position I was performing the recreation table
adventure with the pain to reconstruct all views depending on it ( at
that time postgres didn't even had any dependencies information ), and
all foreign key refering the table.

Now with milion of record this is not feseable. What we do now is add,
in the development DB, the column at the end of the table, this just to
have the table in production and in the development environment with the
same definition.

I think that have a way to reorder the column inside a table definition
could save us some pains.


Regards
Gaetano Mendola









Wed, 05 May 2004 18:38:31 +0200
Gaetano Mendola <mendola@bigfoot.com>
Re: ALTER TABLE TODO items
Stephen Frost <sfrost@snowman.net> writes:
> Does using table recreate break views built against the table?

Right now it just rejects the ALTER attempt:

regression=# create table t1 (f1 int);
CREATE TABLE
regression=# create view v1 as select * from t1;
CREATE VIEW
regression=# alter table t1 alter f1 type bigint;
ERROR:  cannot alter type of a column used by a view or rule
DETAIL:  rule _RETURN on view v1 depends on column "f1"
regression=#

Improving this per the previous discussion probably ought to be
mentioned in the TODO list.

			regards, tom lane

Wed, 05 May 2004 12:52:15 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: ALTER TABLE TODO items 
Tom Lane wrote:
> Stephen Frost <sfrost@snowman.net> writes:
> > Does using table recreate break views built against the table?
> 
> Right now it just rejects the ALTER attempt:
> 
> regression=# create table t1 (f1 int);
> CREATE TABLE
> regression=# create view v1 as select * from t1;
> CREATE VIEW
> regression=# alter table t1 alter f1 type bigint;
> ERROR:  cannot alter type of a column used by a view or rule
> DETAIL:  rule _RETURN on view v1 depends on column "f1"
> regression=#
> 
> Improving this per the previous discussion probably ought to be
> mentioned in the TODO list.

Add to TODO?

	Allow views to be auto-recreated based on table changes

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Wed, 5 May 2004 12:53:41 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: ALTER TABLE TODO items
On Wed, 2004-05-05 at 10:36, Bruce Momjian wrote:
> 
> TODO items completed:
> 
>         o -ALTER TABLE ADD COLUMN does not honor DEFAULT and non-CHECK CONSTRAINT
>         o -ALTER TABLE ADD COLUMN column DEFAULT should fill existing
>           rows with DEFAULT value
>         o -Allow ALTER TABLE to modify column lengths and change to binary
>           compatible types
> 
> Seems we didn't have ALTER COLUMN TYPE on the TODO list.
> 
> Do we still want this TODO?
> 
>         o Allow columns to be reordered using ALTER ... POSITION i col1 [,col2];
>           have SELECT * and INSERT honor such ordering
> 
> I don't think so.  As I remember it was part of doing logical attribute
> numbers as a way to add ALTER COLUMN TYPE, but because we now use table
> recreate to implement this, it is unlikely we will ever add logical
> attribute numbers (adds too much complexity to the code).
> 

I think we should leave since it is still functionality that people will
want. Furthermore I am not sure we are done with ALTER COLUMN TYPE
completely. Granted I've not yet had time to take a thorough look at the
implementation so I could be off here, but as I understand it the
current code seems a little problematic on large tables; recreating the
entire table is likely to cause excessive i/o and disk space issues
compared to a potentially much nicer add column/update column/drop
column routine. 

Hmm... upon further thought, if the above implementation stands up, istm
that its machinations could also be used to implement the reordering
functionality... ie. rewrite the table and fix up any dependencies as
needed.  

way to back track on myself eh? 

Robert Treat
-- 
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL


05 May 2004 13:04:31 -0400
Robert Treat <xzilla@users.sourceforge.net>
Re: ALTER TABLE TODO items
Robert Treat <xzilla@users.sourceforge.net> writes:
> On Wed, 2004-05-05 at 10:36, Bruce Momjian wrote:
>> Do we still want this TODO?
>> 
>> o Allow columns to be reordered using ALTER ... POSITION i col1 [,col2];

> I think we should leave since it is still functionality that people will
> want.

It's not that no one would want it, it's that the code impact (and risk
of bugs) associated with separate logical and physical column numbers
seems very disproportionate to the value.  The main argument for it
AFAIR was to support column type substitution via drop col/add col/
reorder col.  Now that we have a better way I think the value of such a
feature wouldn't be worth the work/risk.

> recreating the entire table is likely to cause excessive i/o and disk
> space issues compared to a potentially much nicer add column/update
> column/drop column routine.

How you figure that?  The UPDATE step will in itself require 2X disk
space --- and after that you'll need a VACUUM FULL to get it back.
The implementation Rod came up with is much nicer.

> Hmm... upon further thought, if the above implementation stands up, istm
> that its machinations could also be used to implement the reordering
> functionality... ie. rewrite the table and fix up any dependencies as
> needed.  

True.  In fact, this example that I put into the regression tests may be
food for thought:

create table another (f1 int, f2 text);
insert into another values(1, 'one');
insert into another values(2, 'two');
insert into another values(3, 'three');

select * from another;
 f1 |  f2   
----+-------
  1 | one
  2 | two
  3 | three
(3 rows)

alter table another
  alter f1 type text using f2 || ' more',
  alter f2 type bigint using f1 * 10;

select * from another;
     f1     | f2 
------------+----
 one more   | 10
 two more   | 20
 three more | 30
(3 rows)


			regards, tom lane

Wed, 05 May 2004 13:47:21 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: ALTER TABLE TODO items 
Tom Lane wrote:
> select * from another;
>  f1 |  f2   
> ----+-------
>   1 | one
>   2 | two
>   3 | three
> (3 rows)
> 
> alter table another
>   alter f1 type text using f2 || ' more',
>   alter f2 type bigint using f1 * 10;
> 
> select * from another;
>      f1     | f2 
> ------------+----
>  one more   | 10
>  two more   | 20
>  three more | 30
> (3 rows)

Wow, you can reference different column as part of the alter column.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Wed, 5 May 2004 13:51:23 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: ALTER TABLE TODO items
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Wow, you can reference different column as part of the alter column.

Yeah, the USING is actually any arbitrary expression over the old table
row.  (Getting that to work was a tad tricky...)  So you can view this
as a full-table UPDATE operation that folds in possible column type changes.

			regards, tom lane

Wed, 05 May 2004 13:59:04 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: ALTER TABLE TODO items 
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> 
>>Wow, you can reference different column as part of the alter column.
> 
> 
> Yeah, the USING is actually any arbitrary expression over the old table
> row.  (Getting that to work was a tad tricky...)  So you can view this
> as a full-table UPDATE operation that folds in possible column type changes.

Does that mean I'll want to disable triggers while I do this? Actually, 
if the structure's changing I presume I'll want to drop/recreate my 
triggers anyway (even if they get reparsed like view definitions).

-- 
   Richard Huxton
   Archonet Ltd

Wed, 05 May 2004 20:45:39 +0100
Richard Huxton <dev@archonet.com>
Re: ALTER TABLE TODO items
Richard Huxton <dev@archonet.com> writes:
> Does that mean I'll want to disable triggers while I do this?

Hrm.  Right now the code does not fire triggers at all, but that seems
wrong.  However, I doubt that very many triggers could cope with update
events in which the old and new rows have different rowtypes :-(.
Any thoughts what to do about that?

			regards, tom lane

Wed, 05 May 2004 15:50:02 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: ALTER TABLE TODO items 
> Yeah, the USING is actually any arbitrary expression over the old table
> row.  (Getting that to work was a tad tricky...)  So you can view this
> as a full-table UPDATE operation that folds in possible column type changes.

All I can say is three cheers for Tom and Rod on this one!!!!

Chris


Thu, 06 May 2004 09:45:03 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: ALTER TABLE TODO items
Tom Lane wrote:
> Richard Huxton <dev@archonet.com> writes:
> > Does that mean I'll want to disable triggers while I do this?
> 
> Hrm.  Right now the code does not fire triggers at all, but that seems
> wrong.  However, I doubt that very many triggers could cope with update
> events in which the old and new rows have different rowtypes :-(.
> Any thoughts what to do about that?

If triggers exist, I think we should just throw a warning that triggers
will not be fired.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Wed, 5 May 2004 23:00:29 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: ALTER TABLE TODO items
Bruce Momjian wrote:
> Tom Lane wrote:
> 
>>Richard Huxton <dev@archonet.com> writes:
>>
>>>Does that mean I'll want to disable triggers while I do this?
>>
>>Hrm.  Right now the code does not fire triggers at all, but that seems
>>wrong.  However, I doubt that very many triggers could cope with update
>>events in which the old and new rows have different rowtypes :-(.
>>Any thoughts what to do about that?
> 
> 
> If triggers exist, I think we should just throw a warning that triggers
> will not be fired.

Tom's point about triggers probably not working after the upgrade is a 
good one though. Is it reasonable to just refuse to act on a table until 
  all triggers are dropped? I'd rather be forced to go through and 
drop/restore triggers in a script than be surprised later on.

-- 
   Richard Huxton
   Archonet Ltd

Thu, 06 May 2004 08:51:05 +0100
Richard Huxton <dev@archonet.com>
Re: ALTER TABLE TODO items
On Thu, 6 May 2004, Richard Huxton wrote:

> Bruce Momjian wrote:
> > Tom Lane wrote:
> > 
> >>Richard Huxton <dev@archonet.com> writes:
> >>
> >>>Does that mean I'll want to disable triggers while I do this?
> >>
> >>Hrm.  Right now the code does not fire triggers at all, but that seems
> >>wrong.  However, I doubt that very many triggers could cope with update
> >>events in which the old and new rows have different rowtypes :-(.
> >>Any thoughts what to do about that?
> > 
> > 
> > If triggers exist, I think we should just throw a warning that triggers
> > will not be fired.
> 
> Tom's point about triggers probably not working after the upgrade is a 
> good one though. Is it reasonable to just refuse to act on a table until 
>   all triggers are dropped? I'd rather be forced to go through and 
> drop/restore triggers in a script than be surprised later on.

How about "cascade drop triggers" as an option so you can still do it in 
one line should you want to?


Thu, 6 May 2004 09:47:13 -0600 (MDT)
"scott.marlowe" <scott.marlowe@ihs.com>
Re: ALTER TABLE TODO items
On Thursday 06 May 2004 11:47, scott.marlowe wrote:
> On Thu, 6 May 2004, Richard Huxton wrote:
> > Bruce Momjian wrote:
> > > Tom Lane wrote:
> > >>Richard Huxton <dev@archonet.com> writes:
> > >>>Does that mean I'll want to disable triggers while I do this?
> > >>
> > >>Hrm.  Right now the code does not fire triggers at all, but that seems
> > >>wrong.  However, I doubt that very many triggers could cope with update
> > >>events in which the old and new rows have different rowtypes :-(.
> > >>Any thoughts what to do about that?
> > >
> > > If triggers exist, I think we should just throw a warning that triggers
> > > will not be fired.
> >
> > Tom's point about triggers probably not working after the upgrade is a
> > good one though. Is it reasonable to just refuse to act on a table until
> >   all triggers are dropped? I'd rather be forced to go through and
> > drop/restore triggers in a script than be surprised later on.
>
> How about "cascade drop triggers" as an option so you can still do it in
> one line should you want to?

What about rules/views/functions and who knows what else (domains?)  might be 
dependant on the current type definition?  It seems like a pretty big can of 
worms really. 

Robert Treat
-- 
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

Thu, 6 May 2004 15:02:02 -0400
Robert Treat <xzilla@users.sourceforge.net>
Re: ALTER TABLE TODO items
Robert Treat <xzilla@users.sourceforge.net> writes:
> What about rules/views/functions and who knows what else (domains?)
> might be dependant on the current type definition?

Yeah, I was just thinking about that this morning.  We probably ought to
look for dependencies on the table rowtype as well as the individual
column.

But on the other side of the coin, should we actually reject the ALTER
if we see a function that uses the rowtype as a parameter or result
type?  Without looking inside the function, we can't really tell if the
ALTER will break the function or not.

			regards, tom lane

Thu, 06 May 2004 15:04:32 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: ALTER TABLE TODO items 
Tom Lane wrote:
> Robert Treat <xzilla@users.sourceforge.net> writes:
> 
>>What about rules/views/functions and who knows what else (domains?)
>>might be dependant on the current type definition?
> 
> 
> Yeah, I was just thinking about that this morning.  We probably ought to
> look for dependencies on the table rowtype as well as the individual
> column.
> 
> But on the other side of the coin, should we actually reject the ALTER
> if we see a function that uses the rowtype as a parameter or result
> type?  Without looking inside the function, we can't really tell if the
> ALTER will break the function or not.

With looking, you can't necessarily. What if I'm building a query with 
EXECUTE or for that matter, what if I've written it in C?

-- 
   Richard Huxton
   Archonet Ltd

Fri, 07 May 2004 09:16:22 +0100
Richard Huxton <dev@archonet.com>
Re: ALTER TABLE TODO items