change column length, is it that hard?

Hi,

I have a lot of postgresql databases running on remote locations
using identical schemas. They run 24x7.

One of the tables contains a field username character varying(16)
that needs to become varying(40), so just a little longer.

A simple 'alter table alter column ....' does not work so I tried
creating a new column, dropping the old, and renaming:

dbse=# alter table contact add column tmp_user varchar(40);
ALTER TABLE
dbse=# update contact set tmp_user = username;
UPDATE 71
dbse=# alter table contact alter column tmp_user set default '';
ALTER TABLE
dbse=# alter table contact alter column tmp_user set not NULL;
ALTER TABLE
dbse=# alter table contact drop column username
dbse-# ;
NOTICE:  rule _RETURN on view ox_deps depends on table contact column username
NOTICE:  view ox_deps depends on rule _RETURN on view ox_deps
NOTICE:  rule _RETURN on view pptpusers depends on table contact column username
NOTICE:  view pptpusers depends on rule _RETURN on view pptpusers
NOTICE:  rule _RETURN on view team_members depends on table contact column username
NOTICE:  view team_members depends on rule _RETURN on view team_members
ERROR:  cannot drop table contact column username because other objects depend on it
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

Oh man, I cannot use drop column cascade, this is a live database.

Googling led me to believe I should remove dependencies on the column,
then do my thin, and then recreate dependencies.

Can anyone show me an example how to do this? Please note I did not
design this database, and my grasp of views and rules is almost zero.

Thanks,
Ron Arts



--
NeoNova BV, The Netherlands
Professional internet and VoIP solutions

http://www.neonova.nl   Kruislaan 419              1098 VA Amsterdam
info: 020-5628292       servicedesk: 020-5628292   fax: 020-5628291

The following disclamer applies to this email:
http://www.neonova.nl/maildisclaimer