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/bash
export PATH=/opt/PostgreSQL/93/bin:$PATH
export PGDATA=/data/pgsql
export PGHOME=/opt/PostgreSQL/93
export PGPORT=5432
dbname=$1
if [ ! $dbname ];then
 echo "Please enter the database name."
 exit 1
fi
psql -c "dt" -d $dbname >/dev/null
if [ $? -ne 0 ];then
 exit 1
fi
error_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
 fi
done

说下碰到的的问题，在测试的时候发现如果把主键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.