postgresql - 当应用程序可能尝试为独立但同时的进程创建临时表时，使用临时表是否安全？

Question

我希望我能有效地阐明这一点，所以这里是:

我正在创建一个模型，该模型将由用户在平台上运行，可能同时运行，但每个模型运行都由一个唯一的整数标识符标记。该模型将执行一系列 PostgreSQL 查询，并最终将结果写入其他地方。

现在，由于模型运行需要并行化，我必须确保进程不会发生冲突，尽管它们在同一个数据库中运行。我现在必须存储记录列表，按分数变量排序，然后对它们进行操作。这是查询的开头:

DO
$$
DECLARE row RECORD;
BEGIN

DROP TABLE IF EXISTS ranked_clusters;
CREATE TEMP TABLE ranked_clusters AS (
    SELECT
        pl.cluster_id AS c_id,
        SUM(pl.total_area) AS cluster_score
    FROM
        emob.parking_lots AS pl
    WHERE
        pl.cluster_id IS NOT NULL
        AND
        run_id = 2005149
    GROUP BY
        pl.cluster_id
    ORDER BY
        cluster_score DESC
);

FOR row IN SELECT c_id FROM ranked_clusters LOOP
    RAISE NOTICE 'Cluster %', row.c_id;
END LOOP;

END;
$$ LANGUAGE plpgsql;

所以我创建了一个名为 ranked_clusters 的临时表，然后遍历它，目前只记录每条记录的标识符。

我一直小心翼翼地只从 run_id 值等于特定数字的记录构建此列表，因此来自相同来源但数字不同的数据将被忽略。

然而，我担心的是，同时进行的进程也会创建自己的 ranked_clusters 临时表，这将与第一个发生冲突，从而使结果无效。

所以我的问题本质上是这样的:临时表是否只对创建它们的 session 可见(或者对来自 Python 的游标对象)？以这种方式使用临时表是否安全？

我问的主要原因是因为我看到这些所谓的“临时”表似乎在我在 PgAdmin III 中执行查询后仍然存在，并且在下一次执行时查询失败，因为该表已经存在。这让我很困扰，因为这些表似乎在其生命周期内实际上是全局可访问的，因此会在同时运行时引入冲突的可能性。

感谢@a_horse_with_no_name 的解释，但我还不确定它是否安全，因为我已经能够执行以下代码:

import psycopg2 as pg2

conn = pg2.connect(dbname=CONFIG["GEODB_NAME"],
                       user=CONFIG["GEODB_USER"],
                       password=CONFIG["GEODB_PASS"],
                       host=CONFIG["GEODB_HOST"],
                       port=CONFIG["GEODB_PORT"])
conn.autocommit = True
cur = conn.cursor()

conn2 = pg2.connect(dbname=CONFIG["GEODB_NAME"],
                    user=CONFIG["GEODB_USER"],
                    password=CONFIG["GEODB_PASS"],
                    host=CONFIG["GEODB_HOST"],
                    port=CONFIG["GEODB_PORT"])
conn2.autocommit = True
cur2 = conn.cursor()

    cur.execute("CREATE TEMPORARY TABLE temptable (tempcol INTEGER); INSERT INTO temptable VALUES (0);")
    cur2.execute("SELECT tempcol FROM temptable;")
    print(cur2.fetchall())

我收到 temptable 中的值，尽管它是在与之后查询它的连接完全不同的连接中创建为临时表的。我在这里错过了什么吗？因为看起来临时表在连接之间确实是可以访问的。

上面有一个拼写错误，两个游标实际上都是从 conn 中生成的，而不是一个从 conn 中生成，另一个从 conn2 中生成。 psycopg2 中的各个连接无法访问彼此的临时表，但从同一连接生成的游标可以。

Answer 1

临时表仅对创建它们的 session (=连接)可见。即使两个session创建同一张表，也不会互相干扰。

当 session 断开时，临时表会自动删除。

如果您想在事务结束时自动删除它们，请在创建表时使用 ON COMMIT DROP 选项。

所以答案是:是的，这是安全的。

---

无关，但是:您不能“以排序方式”存储行。表中的行没有隐式排序顺序。获得有保证的排序顺序的唯一方法是在选择 行时使用ORDER BY。 CREATE TABLE AS 语句中的 order by 几乎没有用。

如果您必须依赖行的排序顺序，唯一安全的方法是在 SELECT 语句中:

FOR row IN SELECT c_id FROM ranked_clusters <b>ORDER BY cluster_score </b>
LOOP
    RAISE NOTICE 'Cluster %', row.c_id;
END LOOP;