Re: alter column to varchar without view drop/re-creation - Mailing list pgsql-general

On 08/29/2014 02:29 PM, Emi Lu wrote:
> Hello,
>
> On 08/29/2014 03:16 PM, Adrian Klaver wrote:
>>>> May I know is there a way to "alter column type to varchar" (previous
>>>> is varchar(***)) without view drop/re-creation?
>>>>
>>>> Basically, looking for a way to change column without have to
>>>> drop/re-create dependent views.
>>>>
>>>> varchar(***) to varchar and no date/numeric changes.
>>>>
>>> I saw docs mention about: update pg_attribute. May I know:
>>>
>>> . will dependent views updated automatically or there might be potential
>>> problems?
>>> . If it's fine, will the following SQL enough to change column from
>>> varchar(***) to varchar?
>>>
>>>    update pg_attribute set atttypmod =-1
>>>    where  attrelid = 'oid' ;
>>
>> Here is what I did. I would definitely test first and run in a
>> transaction:
>
>
>>
>> test=# SELECT version();
>>                                                          version
>>
--------------------------------------------------------------------------------------------------------------------------
>>
>>  PostgreSQL 8.3.23 on i686-pc-linux-gnu, compiled by GCC gcc (SUSE
>> Linux) 4.7.2 20130108 [gcc-4_7-branch revision 195012]
>>
>>
>> test=# create TABLE base_tbl (id integer, vc_fld varchar(10));
>> CREATE TABLE
>> test=# CREATE view v_test as SELECT * from base_tbl ;
>> CREATE VIEW
>> test=# insert INTO base_tbl VALUES(1, 'one');
>> INSERT 0 1
>> test=# insert INTO base_tbl VALUES(2, 'two');
>> INSERT 0 1
>> test=# \d base_tbl
>>           Table "public.base_tbl"
>>  Column |         Type          | Modifiers
>> --------+-----------------------+-----------
>>  id     | integer               |
>>  vc_fld | character varying(10) |
>>
>> test=# \d v_test
>>             View "public.v_test"
>>  Column |         Type          | Modifiers
>> --------+-----------------------+-----------
>>  id     | integer               |
>>  vc_fld | character varying(10) |
>> View definition:
>>  SELECT base_tbl.id, base_tbl.vc_fld
>>    FROM base_tbl;
>>
>> test=# UPDATE pg_attribute SET atttypmod = -1 WHERE attrelid =
>> 'base_tbl'::regclass AND attname = 'vc_fld';
>> UPDATE 1
>> test=# UPDATE pg_attribute SET atttypmod = -1 WHERE attrelid =
>> 'v_test'::regclass AND attname = 'vc_fld';
>> UPDATE 1
>> test=# \d base_tbl
>>         Table "public.base_tbl"
>>  Column |       Type        | Modifiers
>> --------+-------------------+-----------
>>  id     | integer           |
>>  vc_fld | character varying |
>>
>> test=# \d v_test
>>           View "public.v_test"
>>  Column |       Type        | Modifiers
>> --------+-------------------+-----------
>>  id     | integer           |
>>  vc_fld | character varying |
>> View definition:
>>  SELECT base_tbl.id, base_tbl.vc_fld
>>    FROM base_tbl;
>>
>> test=# insert INTO base_tbl VALUES(3, '123456789012345678901234567890');
>> INSERT 0 1
>> test=# SELECT * from base_tbl ;
>>  id |             vc_fld
>> ----+--------------------------------
>>   1 | one
>>   2 | two
>>   3 | 123456789012345678901234567890
>> (3 rows)
>>
>> test=# SELECT * from v_test ;
>>  id |             vc_fld
>> ----+--------------------------------
>>   1 | one
>>   2 | two
>>   3 | 123456789012345678901234567890
>> (3 rows)
>
> *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.

>
>  >> Here is what I did. I would definitely test first and run in a
> transaction:
>
> It seems that there is no transaction block needed? The one line command
> is:
> UPDATE pg_attribute SET atttypmod = -1 WHERE attrelid =
> 'table_name'::regclass AND attname = 'col1';
> Isn't it?

There is more than one line. One for the base table and one for each
view that uses the base table.

>
> As for the "definitely test", you mean check view after the change?
> Would there be any other potential problems for this approach?

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, the chances of
problems are slight. Still I would run some test queries against both
the base table and view(s) just to be sure.

>
> 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;

Then everything is wrapped in a transaction and 'hidden' from other
sessions until complete.

>
> Thanks a lot!
> Emi


--
Adrian Klaver
adrian.klaver@aklaver.com