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