注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

PostgreSQL 中文网

 
 
 

日志

 
 

PG: 扩 varchar 字段长度的奇怪现象  

2011-02-15 20:06:54|  分类: Postgres基础 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

       今天测试了下,有张大表字段需要扩充长度,即将 character varying(128)
扩大到 character varying(256),因为以前有Oracle 经验,类似的操作在Oracle
库里瞬间就能完成。因为只涉及到更改数据字典,不更改物理数据。下面来看下
PG里的情况。

--查看表大小
wapreader_log=> select pg_size_pretty(pg_relation_size('log_foot_mark'));
 pg_size_pretty
----------------
 5441 MB
(1 row)


--查看表结构
wapreader_log=> \d log_foot_mark
          Table "wapreader_log.log_foot_mark"
   Column    |            Type             | Modifiers
-------------+-----------------------------+-----------
 id          | integer                     | not null
 create_time | timestamp without time zone |
 sky_id      | integer                     |
 url         | character varying(1000)     |
 refer_url   | character varying(1000)     |
 source      | character varying(64)       |
 users       | character varying(64)       |
 userm       | character varying(64)       |
 usert       | character varying(64)       |
 ip          | character varying(32)       |
 module      | character varying(64)       |
 resource_id | character varying(100)      |
 user_agent  | character varying(128)      |
Indexes:
    "pk_log_footmark" PRIMARY KEY, btree (id)

--扩字段长度
wapreader_log=> \timing
Timing is on.

wapreader_log=>  ALTER TABLE wapreader_log.log_foot_mark ALTER column user_agent TYPE character varying(256);
ALTER TABLE
Time: 603504.835 ms   

   通常加字段的DDL是很快的,瞬间就能完成,为什么这次经历了十分钟,觉得很奇怪。
那为什么PG里扩字段长度会花很长时间呢?难道在更改物理数据?带着这个疑问,做了
以下测试,等下看结果。  

--测试,创建一张表
wapreader_log=> create table test_1 (id integer ,remark varchar(32));
CREATE TABLE

--创建插入数据 function
CREATE OR REPLACE FUNCTION wapreader_log.fun_ins_test_1()
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
    DECLARE

      i        INTEGER ;
    BEGIN
      for i in 1..100 loop
      insert into test_1 values (1,'a');
      end loop;
      return 1;
    END;
  $function$

--插入 100 条数据
wapreader_log=> select wapreader_log.fun_ins_test_1();
 fun_ins_test_1
----------------
              1
(1 row)

--查看数据,注意 ctid
wapreader_log=> select ctid,* from test_1 ;
  ctid   | id | remark
---------+----+--------
 (0,1)   |  1 | a
 (0,2)   |  1 | a
 (0,3)   |  1 | a
 (0,4)   |  1 | a
 (0,5)   |  1 | a
 (0,6)   |  1 | a
 (0,7)   |  1 | a
 (0,8)   |  1 | a
 (0,9)   |  1 | a
 (0,10)  |  1 | a
 (0,11)  |  1 | a
 (0,12)  |  1 | a
 (0,13)  |  1 | a
 (0,14)  |  1 | a
 (0,15)  |  1 | a
 (0,16)  |  1 | a
 (0,17)  |  1 | a
 (0,18)  |  1 | a
 (0,19)  |  1 | a
 (0,20)  |  1 | a
 (0,21)  |  1 | a
 (0,22)  |  1 | a
 (0,23)  |  1 | a
 (0,24)  |  1 | a
 (0,25)  |  1 | a
 (0,26)  |  1 | a
 (0,27)  |  1 | a
 (0,28)  |  1 | a
 (0,29)  |  1 | a
 (0,30)  |  1 | a
 (0,31)  |  1 | a
 (0,32)  |  1 | a
 (0,33)  |  1 | a
 (0,34)  |  1 | a
 (0,35)  |  1 | a
 (0,36)  |  1 | a
 (0,37)  |  1 | a
 (0,38)  |  1 | a
 (0,39)  |  1 | a
 (0,40)  |  1 | a
 (0,41)  |  1 | a
 (0,42)  |  1 | a
 (0,43)  |  1 | a
 (0,44)  |  1 | a
 (0,45)  |  1 | a
 (0,46)  |  1 | a
 (0,47)  |  1 | a
 (0,48)  |  1 | a
 (0,49)  |  1 | a
 (0,50)  |  1 | a
 (0,51)  |  1 | a
 (0,52)  |  1 | a
 (0,53)  |  1 | a
 (0,54)  |  1 | a
 (0,55)  |  1 | a
 (0,56)  |  1 | a
 (0,57)  |  1 | a
 (0,58)  |  1 | a
 (0,59)  |  1 | a
 (0,60)  |  1 | a
 (0,61)  |  1 | a
 (0,62)  |  1 | a
 (0,63)  |  1 | a
 (0,64)  |  1 | a
 (0,65)  |  1 | a
 (0,66)  |  1 | a
 (0,67)  |  1 | a
 (0,68)  |  1 | a
 (0,69)  |  1 | a
 (0,70)  |  1 | a
 (0,71)  |  1 | a
 (0,72)  |  1 | a
 (0,73)  |  1 | a
 (0,74)  |  1 | a
 (0,75)  |  1 | a
 (0,76)  |  1 | a
 (0,77)  |  1 | a
 (0,78)  |  1 | a
 (0,79)  |  1 | a
 (0,80)  |  1 | a
 (0,81)  |  1 | a
 (0,82)  |  1 | a
 (0,83)  |  1 | a
 (0,84)  |  1 | a
 (0,85)  |  1 | a
 (0,86)  |  1 | a
 (0,87)  |  1 | a
 (0,88)  |  1 | a
 (0,89)  |  1 | a
 (0,90)  |  1 | a
 (0,91)  |  1 | a
 (0,92)  |  1 | a
 (0,93)  |  1 | a
 (0,94)  |  1 | a
 (0,95)  |  1 | a
 (0,96)  |  1 | a
 (0,97)  |  1 | a
 (0,98)  |  1 | a
 (0,99)  |  1 | a
 (0,100) |  1 | a
(100 rows)

--查看表大小
wapreader_log=> select pg_size_pretty(pg_relation_size('test_1'));
 pg_size_pretty
----------------
 8192 bytes
(1 row)


--更改字段长度
wapreader_log=> \d test_1
        Table "wapreader_log.test_1"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 id     | integer               |
 remark | character varying(32) |


wapreader_log=> alter table test_1 alter column remark type character varying(256);
ALTER TABLE

--再次查看表的 ctid 和表大小
wapreader_log=> select ctid,* from test_1;
  ctid   | id | remark
---------+----+--------
 (0,1)   |  1 | a
 (0,2)   |  1 | a
 (0,3)   |  1 | a
 (0,4)   |  1 | a
 (0,5)   |  1 | a
 (0,6)   |  1 | a
 (0,7)   |  1 | a
 (0,8)   |  1 | a
 (0,9)   |  1 | a
 (0,10)  |  1 | a
 (0,11)  |  1 | a
 (0,12)  |  1 | a
 (0,13)  |  1 | a
 (0,14)  |  1 | a
 (0,15)  |  1 | a
 (0,16)  |  1 | a
 (0,17)  |  1 | a
 (0,18)  |  1 | a
 (0,19)  |  1 | a
 (0,20)  |  1 | a
 (0,21)  |  1 | a
 (0,22)  |  1 | a
 (0,23)  |  1 | a
 (0,24)  |  1 | a
 (0,25)  |  1 | a
 (0,26)  |  1 | a
 (0,27)  |  1 | a
 (0,28)  |  1 | a
 (0,29)  |  1 | a
 (0,30)  |  1 | a
 (0,31)  |  1 | a
 (0,32)  |  1 | a
 (0,33)  |  1 | a
 (0,34)  |  1 | a
 (0,35)  |  1 | a
 (0,36)  |  1 | a
 (0,37)  |  1 | a
 (0,38)  |  1 | a
 (0,39)  |  1 | a
 (0,40)  |  1 | a
 (0,41)  |  1 | a
 (0,42)  |  1 | a
 (0,43)  |  1 | a
 (0,44)  |  1 | a
 (0,45)  |  1 | a
 (0,46)  |  1 | a
 (0,47)  |  1 | a
 (0,48)  |  1 | a
 (0,49)  |  1 | a
 (0,50)  |  1 | a
 (0,51)  |  1 | a
 (0,52)  |  1 | a
 (0,53)  |  1 | a
 (0,54)  |  1 | a
 (0,55)  |  1 | a
 (0,56)  |  1 | a
 (0,57)  |  1 | a
 (0,58)  |  1 | a
 (0,59)  |  1 | a
 (0,60)  |  1 | a
 (0,61)  |  1 | a
 (0,62)  |  1 | a
 (0,63)  |  1 | a
 (0,64)  |  1 | a
 (0,65)  |  1 | a
 (0,66)  |  1 | a
 (0,67)  |  1 | a
 (0,68)  |  1 | a
 (0,69)  |  1 | a
 (0,70)  |  1 | a
 (0,71)  |  1 | a
 (0,72)  |  1 | a
 (0,73)  |  1 | a
 (0,74)  |  1 | a
 (0,75)  |  1 | a
 (0,76)  |  1 | a
 (0,77)  |  1 | a
 (0,78)  |  1 | a
 (0,79)  |  1 | a
 (0,80)  |  1 | a
 (0,81)  |  1 | a
 (0,82)  |  1 | a
 (0,83)  |  1 | a
 (0,84)  |  1 | a
 (0,85)  |  1 | a
 (0,86)  |  1 | a
 (0,87)  |  1 | a
 (0,88)  |  1 | a
 (0,89)  |  1 | a
 (0,90)  |  1 | a
 (0,91)  |  1 | a
 (0,92)  |  1 | a
 (0,93)  |  1 | a
 (0,94)  |  1 | a
 (0,95)  |  1 | a
 (0,96)  |  1 | a
 (0,97)  |  1 | a
 (0,98)  |  1 | a
 (0,99)  |  1 | a
 (0,100) |  1 | a
(100 rows)

wapreader_log=> select pg_size_pretty(pg_relation_size('wapreader_log.test_1'));
 pg_size_pretty
----------------
 8192 bytes
(1 row)

  发现表大小,ctid 均无变化,说明扩字段长度操作没有更改物理数据, 上述猜想是不成立的。

 后来我把这个问题发到一国外论坛上,上面有个回复我觉得解释得比较好:原文如下,就不翻译了

     When you alter a table, PostgreSQL has to make sure the old version doesn't go away in some cases,
to allow rolling back the change if the server crashes before it's committed and/or written to disk.
For those reasons, what it actually does here even on what seems to be a trivial change is write
out a whole new copy of the table somewhere else first. When that's finished, it then swaps over to
the new one. Note that when this happens, you'll need enough disk space to hold both copies as well.

    There are some types of DDL changes that can be made without making a second copy of the table, but
this is not one of them. For example, you can add a new column that defaults to NULL quickly. But adding
a new column with a non-NULL default requires making a new copy instead.

 针对PG在扩字段长度时会扫描全表且时间较长的问题,总结以下经验

1 尽可能熟悉应用,对于不确定长度的 Character Types ,建议不指定长度。

2  对于Character较长的字段可以采用 text类型。
3  Oracle 在给 varchar2 类型字段加长时,瞬间就能完成,在这点上,PG有着与Oracle 不同的方式;

  评论这张
 
阅读(26982)| 评论(3)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2016