Re: alter column to varchar without view drop/re-creation

Hello Adrian,
>>
>>> test=# UPDATE pg_attribute SET atttypmod = -1 WHERE attrelid =
>>> 'base_tbl'::regclass AND attname = 'vc_fld';
>>>
>>> test=# UPDATE pg_attribute SET atttypmod = -1 WHERE attrelid =
>>> 'v_test'::regclass AND attname = 'vc_fld';
>>>
>>
>> *This is exactly what I plan to do*. So, according to the test result,
>> can make conclusion that pg_attribute will auto take care of all
>> dependent views.
>
> No you can not make that conclusion. I had to manually change the
> atttypmod in the view.
You are right.

>
> Well you are using a backdoor hack to directly alter a system table,
> so yes there is a potential for problems.
> I would imagine in this case, same base type just changing the length
> argument
Confirm yes. only varchar(n) to varchar.

> the chances of problems are slight.
So, how about the following steps:

begin;
set pg_attribute for v1; v2,... vN;
set pg_attribute for table;
commit;

What might be the left potential problems?

>>
>> If not, I will adopt this approach since we have many view dependencies
>> and it seems that this was the best way to avoid view drop/re-creation
>> for now. If there are other ways, please do let me know.
>
> The only other way I know to do this is to:
>
> BEGIN;
> DROP VIEW some_view ;
> ALTER TABLE some_table ALTER COLUMN some_col TYPE new_type;
> CREATE OR REPLACE VIEW some_view SELECT * FROM some_table;
> COMMIT;
Comparing with the pg_attribute action, this approach would be the last
one since there are too many view dependencies.

Thanks a lot!
Emi