postgresql數(shù)據(jù)庫測試環(huán)境中有多張表沒有添加主鍵約束，只有一個serial的自增字段。現(xiàn)在需要把那些沒有主鍵的表都加上，serial類型的字段為id 。
首先是怎么找到postgresql數(shù)據(jù)庫中哪些表沒有主鍵？我們看下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 cjoin pg_namespace non ( 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)最后就是把這個思路寫到腳本里面，運行腳本批量添加。腳本里面把執(zhí)行失敗的表都放在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_logsql=`cat << eofselect n.nspname as \schema\,c.relname as \table name\from pg_catalog.pg_class cjoin pg_namespace non ( 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說下碰到的的問題，在測試的時候發(fā)現(xiàn)如果把主鍵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 tablezhangnq=# select relname,relhaspkey from pg_class where relname=\'server\' ; relname | relhaspkey --------- ------------ server | t(1 row)zhangnq=# vacuum server ;vacuumzhangnq=# select relname,relhaspkey from pg_class where relname=\'server\' ; relname | relhaspkey --------- ------------ server | f(1 row)zhangnq=# alter table server add primary key (id) ;alter tablezhangnq=# select relname,relhaspkey from pg_class where relname=\'server\' ; relname | relhaspkey --------- ------------ server | t(1 row)查看pg_class的說明后發(fā)現(xiàn)原來pg_class只有在狀態(tài)由false變成ture的時候會自動修改。這么設(shè)計可以提高并發(fā)性。
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



三星折疊機陣亡，華為折疊的機會來了？
小聚教你過期域名搶注
數(shù)字域名八位數(shù)交易成功！
怎么代理服務(wù)器
百度推出高管退休計劃：張亞勤今年十月退休
外匯掛ea的vps云服務(wù)器掛機
云服務(wù)的服務(wù)器價格
上半年云業(yè)務(wù)逆勢增長 賦能數(shù)字經(jīng)濟 全國各地上云忙