为了账号安全,请及时绑定邮箱和手机立即绑定

为PostgreSQL数据库中没有主键的表增加主键

标签:
大数据

PostgreSQL数据库测试环境中有多张表没有添加主键约束,只有一个serial的自增字段。现在需要把那些没有主键的表都加上,serial类型的字段为id 。

首先是怎么找到PostgreSQL数据库中哪些表没有主键?我们看下pg_class这个表,里面有个relhaspkey字段,如果为t说明有主键,f即没有主键。例如下面这个sql 。

SELECT n.nspname AS "Schema",c.relname AS "Table Name",c.relhaspkey AS "Has PK" FROM
 pg_catalog.pg_class c
JOIN
 pg_namespace n
ON (
 c.relnamespace = n.oid
 AND n.nspname NOT IN ('information_schema', 'pg_catalog')
 AND c.relkind='r')WHERE c.relhaspkey = 'f'ORDER BY c.relhaspkey, c.relname;

然后就是对这些表增加主键约束。删除和添加主键的sql如下所示:

alter table server drop constraint server_pkey ;alter table server add primary key (id) ;

主键添加完成之后可以通过\d查看。

zhangnq=# \d server Table "public.server"
 Column | Type | Modifiers --------+---------------+------------------------------------------------------
 id | integer | not null default nextval('server_int_seq'::regclass)
 ip | character(50) | Indexes:
 "server_pkey" PRIMARY KEY, btree (id)

最后就是把这个思路写到脚本里面,运行脚本批量添加。脚本里面把执行失败的表都放在error.log文件中。

脚本:

#!/bin/bashexport PATH=/opt/PostgreSQL/93/bin:$PATHexport PGDATA=/data/pgsqlexport PGHOME=/opt/PostgreSQL/93export PGPORT=5432dbname=$1if [ ! $dbname ];then
 echo "Please enter the database name."
 exit 1fipsql -c "\dt" -d $dbname >/dev/nullif [ $? -ne 0 ];then
 exit 1fierror_log="error.log"echo "">$error_log
sql=`cat << EOF
SELECT n.nspname AS "Schema",c.relname AS "Table Name"
FROM
 pg_catalog.pg_class c
JOIN
 pg_namespace n
ON (
 c.relnamespace = n.oid
 AND n.nspname NOT IN ('information_schema', 'pg_catalog')
 AND c.relkind='r'
)
WHERE c.relhaspkey = 'f'
ORDER BY c.relhaspkey, c.relname
;
EOF`schemas=`psql -t -A -c "$sql" -d $dbname |cut -d "|" -f 1`tables=`psql -t -A -c "$sql" -d $dbname |cut -d "|" -f 1`for res in `psql -t -A -c "$sql" -d $dbname`do
 schema=`echo $res|cut -d "|" -f 1`
 table=`echo $res|cut -d "|" -f 2`
 tablename=`echo "$schema.$table"`
 psql -e -c "alter table $tablename add primary key (id) " -d $dbname if [ $? -ne 0 ];then
 echo "$dbname : Add primary key to $tablename error." >>$error_log fidone

说下碰到的的问题,在测试的时候发现如果把主键drop掉之后pg_class.relhaspkey值还是为t,但是用\d查看确实没有主键了。解决的办法是手动vacuum这个表,即vacuum server 。

zhangnq=# select relname,relhaspkey from pg_class where relname='server' ;
 relname | relhaspkey 
---------+------------
 server | t(1 row)zhangnq=# alter table server drop constraint server_pkey ;ALTER TABLE
zhangnq=# select relname,relhaspkey from pg_class where relname='server' ;
 relname | relhaspkey 
---------+------------
 server | t(1 row)zhangnq=# vacuum server ;VACUUM
zhangnq=# select relname,relhaspkey from pg_class where relname='server' ;
 relname | relhaspkey 
---------+------------
 server | f(1 row)zhangnq=# alter table server add primary key (id) ;ALTER TABLE
zhangnq=# select relname,relhaspkey from pg_class where relname='server' ;
 relname | relhaspkey 
---------+------------
 server | t(1 row)

查看pg_class的说明后发现原来pg_class只有在状态由false变成ture的时候会自动修改。这么设计可以提高并发性。

Several of the Boolean flags in pg_class are maintained lazily: they are guaranteed to be true if that's the correct state, but may not be reset to false immediately when the condition is no longer true. For example, relhasindex is set by CREATE INDEX, but it is never cleared by DROP INDEX. Instead, VACUUM clears relhasindex if it finds the table has no indexes. This arrangement avoids race conditions and improves concurrency.

参考链接:

http://www.postgresql.org/message-id/1395116664140-5796526.post@n5.nabble.com

原文链接:http://outofmemory.cn/postgresql/add-primary-key-for-table-without-primary-key

点击查看更多内容
TA 点赞

若觉得本文不错,就分享一下吧!

评论

作者其他优质文章

正在加载中
  • 推荐
  • 评论
  • 收藏
  • 共同学习,写下你的评论
感谢您的支持,我会继续努力的~
扫码打赏,你说多少就多少
赞赏金额会直接到老师账户
支付方式
打开微信扫一扫,即可进行扫码打赏哦
今天注册有机会得

100积分直接送

付费专栏免费学

大额优惠券免费领

立即参与 放弃机会
意见反馈 帮助中心 APP下载
官方微信

举报

0/150
提交
取消