Horton

Thread

  1. pgsql-server/ oc/src/sgml/ref/alter_table.sgml ...

    Tom Lane <tgl@svr1.postgresql.org> — 2004-05-05T04:48:48Z

    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.

  2. Re: pgsql-server/ oc/src/sgml/ref/alter_table.sgml ...

    Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2004-05-05T05:27:09Z

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

  3. ALTER TABLE TODO items

    Bruce Momjian <pgman@candle.pha.pa.us> — 2004-05-05T14:36:36Z

    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

  4. Re: ALTER TABLE TODO items

    Stephen Frost <sfrost@snowman.net> — 2004-05-05T15:30:36Z

    * 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

  5. Re: ALTER TABLE TODO items

    Bruce Momjian <pgman@candle.pha.pa.us> — 2004-05-05T15:38:34Z

    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

  6. Re: ALTER TABLE TODO items

    Gaetano Mendola <mendola@bigfoot.com> — 2004-05-05T16:38:31Z

    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

  7. Re: ALTER TABLE TODO items

    Tom Lane <tgl@sss.pgh.pa.us> — 2004-05-05T16:52:15Z

    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

  8. Re: ALTER TABLE TODO items

    Bruce Momjian <pgman@candle.pha.pa.us> — 2004-05-05T16:53:41Z

    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

  9. Re: ALTER TABLE TODO items

    Robert Treat <xzilla@users.sourceforge.net> — 2004-05-05T17:04:31Z

    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

  10. Re: ALTER TABLE TODO items

    Tom Lane <tgl@sss.pgh.pa.us> — 2004-05-05T17:47:21Z

    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

  11. Re: ALTER TABLE TODO items

    Bruce Momjian <pgman@candle.pha.pa.us> — 2004-05-05T17:51:23Z

    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

  12. Re: ALTER TABLE TODO items

    Tom Lane <tgl@sss.pgh.pa.us> — 2004-05-05T17:59:04Z

    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

  13. Re: ALTER TABLE TODO items

    Richard Huxton <dev@archonet.com> — 2004-05-05T19:45:39Z

    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

  14. Re: ALTER TABLE TODO items

    Tom Lane <tgl@sss.pgh.pa.us> — 2004-05-05T19:50:02Z

    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

  15. Re: ALTER TABLE TODO items

    Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2004-05-06T01:45:03Z

    > 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

  16. Re: ALTER TABLE TODO items

    Bruce Momjian <pgman@candle.pha.pa.us> — 2004-05-06T03:00:29Z

    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

  17. Re: ALTER TABLE TODO items

    Richard Huxton <dev@archonet.com> — 2004-05-06T07:51:05Z

    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

  18. Re: ALTER TABLE TODO items

    scott.marlowe <scott.marlowe@ihs.com> — 2004-05-06T15:47:13Z

    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?

  19. Re: ALTER TABLE TODO items

    Robert Treat <xzilla@users.sourceforge.net> — 2004-05-06T19:02:02Z

    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

  20. Re: ALTER TABLE TODO items

    Tom Lane <tgl@sss.pgh.pa.us> — 2004-05-06T19:04:32Z

    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

  21. Re: ALTER TABLE TODO items

    Richard Huxton <dev@archonet.com> — 2004-05-07T08:16:22Z

    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