Changing data types

This is not for -hackers.

And the answer is "no, you can't". Recreate the table with correct types
and insert the old values into it.

On Mon, 24 Sep 2001, 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'?

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

One thought did just occur to me. It is at least theoretically possible to
simplisticly migrate on column type to another by reading in the data and
oid of the row into a struct, drop the column, create a new column with the
correct data type, and populate. This is ugly, but it is better than saying
"no, you can't".

Geoff

-----Original Message-----
From: Gowey, Geoffrey
Sent: Monday, September 24, 2001 10:53 AM
To: 'Alex Pilosov'; Gowey, Geoffrey
Cc: pgsql-hackers@postgresql.org
Subject: RE: [HACKERS] Changing data types

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

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

create temptable
as select col_a, col_b, varchar9col_c::int, col_d from originaltable
;

drop table originaltable;

alter table temptable rename to originaltable;

and then create all indexes and constraints.

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

"Gowey, Geoffrey" wrote:

One thought did just occur to me. It is at least theoretically possible to
simplisticly migrate on column type to another by reading in the data and
oid of the row into a struct, drop the column, create a new column with the
correct data type, and populate. This is ugly, but it is better than saying
"no, you can't".

The DROP COLUMN part is the one that is what's really hard.

It is not currently supported in postgreSQL

Supporting it comes up now and then, but as the solution (changing
system tables
and then rewriting the whole table) is considered ugly in current
implementation
it has always windled down to not doing it.

The way to manually change column type is something like:

alter table mytable add column newcolumn int;
update table set newcolumn = oldcolumn;
alter table rename oldcolumn to __del__001;
alter table rename newcolumn to oldcolumn;

but you can't DROP COLUMN without recreating the TABLE

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

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

-----Original Message-----
From: Jean-Michel POURE [mailto:jm.poure@freesurf.fr]
Sent: 24 September 2001 20:11
To: pgadmin-hackers@postgresql.org
Subject: [pgadmin-hackers] Alter project: client or server side?

< snipped long discussion about object dependencies and gotchas with regard
to dropping/recreating (== editting) of objects such as functions - e.g.
view frog uses function tadpole. Editting tadpole will break frog so we need
to rebuild frog as well >

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?

I've CC'd this to pgsql-hackers in hope of some guidence from the developers
there.

My current view is that we need to implement these facilities (object
dependency tracking/rebuilding) client side. I believe we are just coming up
to the 7.2 beta and the required features do not exist to my knowledge,
therefore we either wait and hope they get written for 7.3 (or 8.0) or do it
ourselves client side.

Regards, Dave.

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

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

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

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

Unfortunately, some of the head aches I have been encountering require me to
be able to do such oddities (example: my money column type not working with
the pgsql odbc driver). It's not just limited to a varchar to int
conversion that was just an example. There's a bunch of things that I need
to be able to do (and I would gladly help with the coding if I knew where to
start).

Geoff

-----Original Message-----
From: mlw [mailto:markw@mohawksoft.com]
Sent: Monday, September 24, 2001 9:25 PM
To: Gowey, Geoffrey
Cc: 'Alex Pilosov'; pgsql-hackers@postgresql.org
Subject: Re: Changing data types

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