Re: [GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] alter column to varchar without view drop/re-creation
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.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] alter column to varchar without view drop/re-creation
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
Re: [GENERAL] alter column to varchar without view drop/re-creation
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.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] alter column to varchar without view drop/re-creation
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
