数据库操作规范

对象：阿里云 AnalyticDB for PostgreSQL（基于 Greenplum，MPP 架构）

用途：写 migration、运维脚本、排查数据库卡死问题时先读这篇。所有条目都来自踩过的坑，不是推测。

1. 致命操作（永远不要做）

操作	后果	替代方案
`ALTER TABLE ... RENAME`	表损坏，需重启实例	`CREATE TABLE AS SELECT` → `DROP TABLE 旧表`
`ALTER TABLE ... DROP COLUMN`	表损坏	同上
事务里执行 DDL	DDL 回滚不完整，部分持久化	`autocommit=True`，每条 DDL 独立
`FOREIGN KEY ... ON DELETE CASCADE`	不支持（底层依赖 trigger）	应用层级联：`knowhub_db/cascade.py`

改表结构的安全模式：CREATE TABLE 新表 → INSERT 数据 → DROP TABLE 旧表。参考 migrate_v3_junction_tables.py。

2. DDL 需谨慎的操作

操作	风险	推荐做法
`ADD COLUMN ... NOT NULL DEFAULT 'X'`	连接可能被服务端杀掉	拆成两步：`ADD COLUMN ... DEFAULT 'X'` → 另起连接 `ALTER COLUMN ... SET NOT NULL`
任何 DDL（ALTER/CREATE INDEX 等）	需要 `AccessExclusiveLock`，任何 `idle in transaction` 会话都会阻塞无限等	跑前先 kill `idle in transaction`；用 `statement_timeout='30s'` 防挂起
批量 DDL 连发	每次开新连接会累积 TCP 会话	一个长连接跑全部 DDL

3. Store / 应用端连接规范

3.1 永远 `autocommit = True`

self.conn = psycopg2.connect(...)
self.conn.autocommit = True   # 必须

原因：autocommit = False 下，执行一次 SELECT 就会开启一个隐式事务，如果不显式 commit/rollback，连接停在 idle in transaction 状态——永久持有 AccessShareLock，阻塞后续所有需要 AccessExclusiveLock 的 DDL。这是最难诊断、最容易卡整个系统的坑。

代价：多语句写（entity INSERT + 若干 junction 写）失去事务原子性。但我们的写模式是 DELETE + INSERT ON CONFLICT DO NOTHING——幂等，失败重跑即可。

详见 decisions.md §17。

3.2 Migration 脚本模板

import os, time, psycopg2
from dotenv import load_dotenv
load_dotenv()

conn = psycopg2.connect(..., connect_timeout=10)
conn.autocommit = True      # 不要开事务
cur = conn.cursor()
cur.execute("SET statement_timeout = '30s'")  # 卡超过 30s 自动失败

# 动手前先清 idle-in-tx，防止 DDL 等锁无限阻塞
cur.execute("""SELECT pid FROM pg_stat_activity WHERE state='idle in transaction' 
               AND pid!=pg_backend_pid() AND datname=current_database()""")
for (pid,) in cur.fetchall():
    cur.execute("SELECT pg_terminate_backend(%s)", (pid,))

# 每条 DDL 前打 flush 的 print（否则卡住时看不到到哪一步）
for t in TARGETS:
    print(f"[{time.strftime('%H:%M:%S')}] ALTER {t}...", flush=True)
    cur.execute(f"ALTER TABLE {t} ...")
    print(f"  ✓ done", flush=True)

关键：

flush=True 永远带上——挂起时最需要看到卡在哪一步
SET statement_timeout——宁可快速失败，不要让 client 无限等
先清 idle-in-tx——否则你做的 DDL 会被别人的长事务卡住
每条 DDL 前后打时间戳

4. 排查手册

症状：连接失败 `remaining connection slots are reserved`

原因：连接池打满。通常因为：

有 Python 脚本被 kill 但 TCP 会话服务端未释放（等 idle timeout，可能十几分钟）
生产服务（knowhub server）连接泄漏

排查：

lsof -i | grep gpdbmaster   # 看本地有没有残留连接

恢复（按成本从低到高）：

等：Alibaba 的 idle session timeout 会自然释放——但"active"或"idle in transaction"不走超时
阿里云控制台：实例管理 → 会话管理 → 手动 terminate。首选
重启实例：所有连接清零，影响 1-2 分钟

症状：DDL 挂起几十秒后 timeout 或被连接断开

原因 99%：有 idle in transaction 会话持有目标表的 AccessShareLock。

查证：

SELECT l.pid, l.mode, l.granted, c.relname, a.state, now()-a.query_start AS dur
FROM pg_locks l
JOIN pg_class c ON l.relation=c.oid
LEFT JOIN pg_stat_activity a ON l.pid=a.pid
WHERE c.relname IN ('your_target_table')
  AND l.pid != pg_backend_pid();

解决：

SELECT pg_terminate_backend(<pid>);

症状：脚本静默挂住，没有任何输出

原因：Python 的 print buffer 没 flush，execute() 已经在 wait 了。

预防：所有 print 带 flush=True；python -u 或设 PYTHONUNBUFFERED=1。

5. 诊断脚本（都在 `knowhub/knowhub_db/scripts/`）

脚本	用途
`kill_db_locks.py`	列出所有非 idle 会话 + 杀 idle-in-tx
`clear_locks.py`	清锁（轻量版）
`check_table_structure.py`	看表结构和行数
`check_extensions.py`	看 PG 扩展（pgvector/fastann 等）

6. 金句（30 秒能记住的）

DDL 前先 autocommit=True；改 store 也必须 autocommit=True
每个 print 带 flush=True
SET statement_timeout='30s'——宁可失败别挂死
跑 DDL 前先 kill idle-in-tx
禁用 RENAME / DROP COLUMN
禁用 FK ON DELETE CASCADE——用 cascade.py
别在事务里跑 DDL
连接满了？去控制台杀会话，本地 kill 进程只关本地 socket

db-operations.md 5.4 KB История Исходник