Thread

1. Re: Changing data types

   Geoffrey J. Gowey <ggowey@rxhope.com> — 2001-09-24T14:53:02Z

   >This is not for -hackers. 
   
   How so?
   
   >And the answer is "no, you can't". Recreate the table with correct types
   >and insert the old values into it.
   
   You're kidding me, right?  *prepares to gargle* MS Sql server can.  Surely
   we can implement this feature or aren't we aiming to go head to head with
   commercial rdbms'?
   
   >>On Mon, 24 Sep 2001, Gowey, Geoffrey wrote:
   
   >> I posted this in my last message, but have not heard anything yet so I'm
   >> wondering if it was overlooked.  I need to know how to change a column
   from
   >> being say a varchar(9) to an integer.  Does anyone know how to change the
   >> data type?
   
   

2. Alter project: client or server side?

   Jean-Michel POURE <jm.poure@freesurf.fr> — 2001-09-24T19:11:04Z

   Hello all,
   
   > >And the answer is "no, you can't". Recreate the table with correct types
   > >and insert the old values into it.
   >
   >You're kidding me, right?  *prepares to gargle* MS Sql server can.  Surely
   >we can implement this feature or aren't we aiming to go head to head with
   >commercial rdbms'?
   
   The other day, I spent 3 hours dropping old_1, old_2 and old_n fields in a DB.
   But what if your table if it has triggers or foreign keys.
   
   There is a very similar problem with DROP FUNCTION / CREATE FUNCTION.
   If function A is based on function B and you drop function B, function A is 
   broken.
   Same as for views: if view A incorporates function A and you drop function 
   A, view A is broken.
   
   OK: what's the point then?
   
   THE POINT IS THAT WHEN YOU HAVE NESTED OBJECTS, YOU NEED TO DROP THEM ALL 
   AND RECREATE THEM ALL.
   SO IF YOU WANT TO MODIFY ONE LINE OF CODE, YOU WILL PROBABLY NEED TO 
   REBUILD ANYTHING.
   NORMAL HUMANS CANNOT DO THIS. MY CODE IS COMPLETE POSTGRESQL SERVER-SIDE.
   IN THESE CONDITIONS, THE CODE CANNOT BE OPTIMIZED ALSO BECAUSE OIDs CHANGE 
   ALL THE TIME.
   
   The way we do it in pgAdmin I 
   http://cvs.social-housing.org/viewcvs.cgi/pgadmin1
   is that we maintain a dependency table based on STRING NAMES and not OIDs.
   When altering an object (view, function, trigger) we rebuild all dependent 
   objects.
   
   Is this the way we should proceed with pgAdmin II?
   Is anyone planning a real dependency table based on object STRING NAMES?
   
   We need some advice:
   1) Client solution: should we add the rebuilding feature to pgAdmin II?
   2) Server solution: should we wait until the ALTER OBJECT project is complete?
   
   Please advice. Help needed.
   Vote for (1) or (2).
   
   Regards,
   Jean-Michel POURE
   pgAdmin Team
   http://pgadmin.postgresql.org
   
   
   
   
   

3. Re: Changing data types

   mlw <markw@mohawksoft.com> — 2001-09-25T01:25:10Z

   "Gowey, Geoffrey" wrote:
   > 
   > >This is not for -hackers.
   > 
   > How so?
   > 
   > >And the answer is "no, you can't". Recreate the table with correct types
   > >and insert the old values into it.
   > 
   > You're kidding me, right?  *prepares to gargle* MS Sql server can.  Surely
   > we can implement this feature or aren't we aiming to go head to head with
   > commercial rdbms'?
   
   To be honest I am very surprised that MS SQL supports that, but then again
   Microsoft is so used to doing everything so utterly wrong, they have to design
   all their products with the ability to support fundamental design error
   corrections on the fly.
   
   I would be surprised if Oracle, DB2, or other "industrial grade" databases
   could do this. Needing to change a column from a varchar to an integer is a
   huge change and a major error in design.
   
   Adding a column, updating a column with a conversion routine, dropping the old
   column, and renaming the new column to the old column name is probably
   supported, but, geez, I have been dealing with SQL for almost 8 years and I
   have never needed to do that.
   
   

4. Re: Changing data types

   Alex Pilosov <alex@pilosoft.com> — 2001-09-25T01:41:44Z

   On Mon, 24 Sep 2001, mlw wrote:
   
   > To be honest I am very surprised that MS SQL supports that, but then
   > again Microsoft is so used to doing everything so utterly wrong, they
   > have to design all their products with the ability to support
   > fundamental design error corrections on the fly.
   > 
   > I would be surprised if Oracle, DB2, or other "industrial grade"
   > databases could do this. Needing to change a column from a varchar to
   > an integer is a huge change and a major error in design.
   Actually they do. Its not a such a great deal, same as adding a column and
   dropping a column. If you can do that, you can do modification of type. 
   
   The sticky thing is dropping a column. There are two options, and
   postgresql developers just can't make up their mind :P)
   
   a) keep old column data in database (wasted space, but fast)
   b) immediately 'compress' table, removing old data (slow, needs a lot of
   space for compression)
   
   Option a) was implemented once, but kludgy, and had a few kinks, and it
   was removed. Option b) plain sucks :P)
   
   -alex
   
   
   
   

5. Re: Changing data types

   Rod Taylor <rod.taylor@inquent.com> — 2001-09-25T02:22:09Z

   > The sticky thing is dropping a column. There are two options, and
   > postgresql developers just can't make up their mind :P)
   >
   > a) keep old column data in database (wasted space, but fast)
   > b) immediately 'compress' table, removing old data (slow, needs a
   lot of
   > space for compression)
   >
   > Option a) was implemented once, but kludgy, and had a few kinks, and
   it
   > was removed. Option b) plain sucks :P)
   
   Out of curiosity how was option a) implemented?  I could envision
   supporting multiple versions of a tuple style to be found within a
   table (each described in pg_attribute).  Gradually these would be
   upgraded through normal use.
   
   I'm personally not fond of the option b) due to the time involved in
   completing the action.  Not only is space an issue, but locking the
   database up for a day while removing a column isn't the nicest thing
   to do -- rename, make nullable, drop all constraints and try to ignore
   it right?
   
   One would expect that keeping multiple versions of a tuple structure
   inside a single table to be slower than normal for selects, but I
   don't think it would require marking the rows themselves -- just base
   it on the max and min transactions in the table at that time.  Vacuum
   would have to push the issue (5k tuples at a time?) of upgrading some
   of the tuples each time it's run in order to enfore that they were all
   gone before XID wrap.  Background vacuum is ideal for that (if
   implemented).  Drop all constraints, indexes and the name (change to
   $1 or something) of the column immediatly.  Vacuum can determine when
   XID Min in a table is > XID Max of another version and drop the
   information from pg_attribute.
   
   Obviously affected:
   - pg_attribute, and anything dealing with it (add XID Max, XID Min
   wraps for known ranges)
   - storage machanism.  On read of a tuple attempt to make it fit latest
   version (XID Max is NULL) by ignoring select fields.
   
   I'll have to leave it up to the pros as to whether it can be done,
   should be done, and what else it'll affect.
   
   I suppose this was option a) that was removed due to it's kludgyness
   :)
   
   
   
   

6. Re: Changing data types

   Alex Pilosov <alex@pilosoft.com> — 2001-09-25T02:31:36Z

   On Mon, 24 Sep 2001, Rod Taylor wrote:
   
   > Out of curiosity how was option a) implemented?  I could envision
   > supporting multiple versions of a tuple style to be found within a
   > table (each described in pg_attribute).  Gradually these would be
   > upgraded through normal use.
   Check the archives (look for "DROP COLUMN" and "Hiroshi Inoue", author of
   original patch).
   
   > One would expect that keeping multiple versions of a tuple structure
   > inside a single table to be slower than normal for selects, but I
   > don't think it would require marking the rows themselves -- just base
   > it on the max and min transactions in the table at that time.  Vacuum
   > would have to push the issue (5k tuples at a time?) of upgrading some
   > of the tuples each time it's run in order to enfore that they were all
   > gone before XID wrap.  Background vacuum is ideal for that (if
   > implemented).  Drop all constraints, indexes and the name (change to
   > $1 or something) of the column immediatly.  Vacuum can determine when
   > XID Min in a table is > XID Max of another version and drop the
   > information from pg_attribute.
   I think it was done by setting attribute_id to negative, essentially
   hiding it from most code, instead of having two tuple versions, but I
   really am not very familiar. Check archives :)
   
   > Obviously affected:
   > - pg_attribute, and anything dealing with it (add XID Max, XID Min
   > wraps for known ranges)
   > - storage machanism.  On read of a tuple attempt to make it fit latest
   > version (XID Max is NULL) by ignoring select fields.
   > 
   > I'll have to leave it up to the pros as to whether it can be done,
   > should be done, and what else it'll affect.
   > 
   > I suppose this was option a) that was removed due to it's kludgyness
   > :)