# 数据库操作规范

**对象**：阿里云 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`

```python
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 脚本模板

```python
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）连接泄漏

**排查**：
```bash
lsof -i | grep gpdbmaster   # 看本地有没有残留连接
```

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

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

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

**查证**：
```sql
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();
```

**解决**：
```sql
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