Обсуждение: 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