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.