使用Psycopg2调用PostgreSQL存储过程创建表失败问题排查

这问题我碰到过好多次，大概率是事务没有提交或者存储过程的事务上下文逻辑有问题，咱们一步步排查：

1. 最常见的坑：忘记提交事务 #

Psycopg2 默认会自动开启一个事务，所有数据库操作都在这个事务里执行——如果你调用完存储过程后没有显式提交，那么所有操作都会被回滚，哪怕存储过程返回了成功的标识（比如你看到的 [(1,)]），数据库里也不会留下任何修改。

解决办法很简单：在调用存储过程后加上事务提交的代码，或者开启自动提交模式：

方案一：显式提交 #

import psycopg2

# 建立连接
conn = psycopg2.connect("dbname=你的数据库名 user=你的用户名 password=你的密码 host=你的主机")
cur = conn.cursor()

# 调用存储过程
cur.callproc('try_create')
print(cur.fetchall())  # 输出 [(1,)]

# 关键：提交事务！
conn.commit()

# 关闭连接
cur.close()
conn.close()

方案二：开启自动提交 #

如果你希望每次操作都自动提交，可以在建立连接后设置：

conn = psycopg2.connect(...)
conn.autocommit = True  # 开启自动提交

2. 检查存储过程的事务逻辑 #

PostgreSQL 的存储过程默认运行在调用者的事务上下文中，也就是说：

• 如果你的存储过程里没有显式的 COMMIT/ROLLBACK，那么它的所有操作都依赖外层事务的提交
• 如果存储过程里捕获了异常（比如表已存在的情况），但没有处理事务状态，也可能导致操作被回滚

举个反例，假设你的存储过程是这样的：

CREATE OR REPLACE PROCEDURE try_create()
LANGUAGE plpgsql
AS $$
BEGIN
    CREATE TABLE hello (id INT);
EXCEPTION
    WHEN duplicate_table THEN
        -- 返回1表示已存在，但如果外层没提交，新创建的表会被回滚
        RETURN 1;
END;
$$;

这种情况下，哪怕存储过程返回了1，只要外层没提交，新表还是不会出现在数据库里。

3. 确认表是否在正确的Schema下 #

有时候表其实已经创建了，但不在你默认查询的Schema里（比如不是 public）。你可以用以下SQL检查所有Schema下的表：

SELECT table_schema, table_name FROM information_schema.tables WHERE table_name = 'hello';

或者在存储过程里显式指定Schema：

CREATE TABLE public.hello (id INT);

4. 权限排查 #

如果以上都没问题，检查你的数据库用户是否有创建表的权限：

• 如果存储过程是默认的 SECURITY INVOKER 模式，调用存储过程的用户需要有 CREATE TABLE 权限
• 如果是 SECURITY DEFINER 模式，需要存储过程的定义者有创建表的权限

可以用这条SQL检查权限：

SELECT has_table_privilege('你的用户名', 'public', 'CREATE');

内容的提问来源于stack exchange，提问作者wonder