我目前正在为一个项目构建一些功能,客户可以通过 UI 更改他们的数据库架构,并且我们处理运行更新数据库 (PostgreSQL) 所需语句的所有细节。
我对一些陷阱可能是什么进行了一些研究,我们遇到的陷阱之一是索引名称,尤其是创建UNIQUE
约束时自动生成的索引名称。
我们允许用户声明他们想要对哪一列建立索引,但我们没有意识到,现在每当他们indexed
在列上设置为 true时,我们就会创建两个索引UNIQUE
。
现在这个问题已经解决了,但是这提出了一个问题:重命名列时会发生什么。就我们的测试而言,重命名列不会更改索引名称,然后当使用先前占用的名称创建新列时,会创建一个附加数字的索引:
CREATE TABLE users (
user_id SERIAL PRIMARY KEY,
email VARCHAR(255) UNIQUE NOT NULL
);
ALTER TABLE "users" RENAME COLUMN "email" TO "email_one";
ALTER TABLE "users" ADD COLUMN "email" INTEGER UNIQUE;
/*
postgres@localhost:postgres> \d users
+-----------+------------------------+----------------------------------------------------------+
| Column | Type | Modifiers |
|-----------+------------------------+----------------------------------------------------------|
| user_id | integer | not null default nextval('users_user_id_seq'::regclass) |
| email_one | character varying(255) | not null |
| email | integer | |
+-----------+------------------------+----------------------------------------------------------+
Indexes:
"users_pkey" PRIMARY KEY, btree (user_id)
"users_email_key" UNIQUE CONSTRAINT, btree (email_one)
"users_email_key1" UNIQUE CONSTRAINT, btree (email)
*/
由于我不是数据库专家,也没有维护过大型数据库,所以我不确定这是否会导致问题。我问了我的一个朋友,他给了我以下功能,我可以使用它来代替RENAME TO
:
CREATE OR REPLACE FUNCTION rename_column_and_index(
tbl_name TEXT,
old_col_name TEXT,
new_col_name TEXT
) RETURNS void AS $$
DECLARE
index_record RECORD;
old_index_suffix TEXT;
new_index_name TEXT;
BEGIN
-- Find all indexes that contain the old column name
FOR index_record IN
SELECT indexname
FROM pg_indexes
WHERE tablename = tbl_name
AND indexdef LIKE old_col_name || '%'
LOOP
-- Start a savepoint
EXECUTE 'SAVEPOINT before_rename';
BEGIN
-- Extract the suffix from the old index name
old_index_suffix := substring(index_record.indexname from length(quote_ident(old_col_name)) + 1);
-- Create the new index name
new_index_name := quote_ident(new_col_name) || old_index_suffix;
-- Rename the column
EXECUTE 'ALTER TABLE ' || quote_ident(tbl_name) ||
' RENAME COLUMN ' || quote_ident(old_col_name) ||
' TO ' || quote_ident(new_col_name);
-- Rename the index
EXECUTE 'ALTER INDEX ' || quote_ident(index_record.indexname) ||
' RENAME TO ' || new_index_name;
EXCEPTION
WHEN OTHERS THEN
-- Rollback to the savepoint in case of error
EXECUTE 'ROLLBACK TO SAVEPOINT before_rename';
RAISE;
END;
-- Release the savepoint
EXECUTE 'RELEASE SAVEPOINT before_rename';
END LOOP;
END;
$$ LANGUAGE plpgsql;
-- SELECT rename_column_and_btree_index('users', 'email', 'email_one');
现在我的问题是:我是否太害怕这个问题了?这个功能有什么好处/让以后的维护更容易吗?或者我可以完全不用担心自动索引吗?
这很大程度上取决于个人品味。在我看来,约束及其实现索引很好地解释了自己,即使我不知道名称。尽管如此,为对象采用一致的命名约定还是很有用的:例如,如果您检查
pg_stat_user_indexes
未使用的索引,如果索引名称已经告诉您索引属于主键并且不应删除,那就太好了。保持数据库干净,这对您有利。在 PostgreSQL 中重命名对象既便宜又简单。