Обсуждение: alter column to varchar without view drop/re-creation
Hello list, <br /><br /> May I know is there a way to "alter column type to varchar" (previous is varchar(***)) withoutview drop/re-creation?<br /><br /> Basically, looking for a way to change column without have to drop/re-create dependentviews. <br /><br /> varchar(***) to varchar and no date/numeric changes. <br /><br /> Thanks a lot!<br /> Emi<br/> ---<br /><b>PostgreSQL 8.3.18 on x86_64</b><br />
<meta content="text/html; charset=ISO-8859-1" http-equiv="Content-Type"> <meta http-equiv="content-type" content="text/html; charset=ISO-8859-1"> Hello list, 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' ; Thanks a lot! --- PostgreSQL 8.3.18 on x86_64
On 08/29/2014 12:09 PM, Emi Lu wrote: > >> Hello list, >> >> 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) > > Thanks a lot! > >> --- >> *PostgreSQL 8.3.18 on x86_64* > > -- Adrian Klaver adrian.klaver@aklaver.com
<meta content="text/html; charset=ISO-8859-1" http-equiv="Content-Type"> 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. >> 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? As for the "definitely test", you mean check view after the change? Would there be any other potential problems for this approach? 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. Thanks a lot! Emi
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
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