PostgreSQL临时表的线程安全性

Question

以下是我创建临时表的语法:

create temp table tmpTable (id bigint not null, primary key (id)) on commit drop;

我知道这意味着在每个事务结束时，该表将被删除。 我的问题是，如果同一会话中的两个或多个线程创建并插入值到临时表中，它们是否会各自获得自己的实例，还是临时实例在会话中共享？如果是共享的，是否有一种方法可以使其对于每个线程都是本地的？ 谢谢 Netta

- netta

---

他们是共享一个数据库连接，还是每个线程都有自己的连接？ - NPE

Answer 1

临时表对同一会话中的所有操作都是可见的。因此，在删除已存在的临时表（在您的情况下提交事务）之前，不能在同一会话中创建同名的临时表。

您可能希望使用以下方法：

CREATE TEMP TABLE tmptbl IF NOT EXISTS ...

唯一的临时表

为了使临时表在同一会话中成为本地表，您需要使用唯一表名。一种方法是使用未绑定的SEQUENCE和动态SQL - 在过程化语言（如plpgsql）或在DO语句中（基本上是相同的，不需要存储函数）。

运行一个：

有关手册中CREATE TABLE的更多信息。

---

CREATE SEQUENCE myseq;

使用：

DO $$
BEGIN
EXECUTE 'CREATE TABLE tmp' || nextval('myseq')  ||'(id int)';
END;
$$

获取最新的表名：

SELECT 'tmp' || currval('myseq');

或者将所有内容放入一个plpgsql函数中，返回表格或重用表名。

然而，所有后续的SQL命令都必须以动态方式执行，因为普通的SQL语句使用硬编码的标识符。所以，最好将所有内容放入一个plpgsql函数中。

---

使用相同的临时表的唯一ID

另一个可能的解决方案是在同一会话中为所有线程使用相同的临时表，并向表中添加一个thread_id列。如果您经常使用该功能，请确保对该列进行索引。然后为每个线程（在同一会话中）使用唯一的thread_id。

仅执行一次：

CREATE SEQUENCE myseq;

每个线程一次：

CREATE TEMP TABLE tmptbl(thread_id int, col1 int) IF NOT EXISTS;
my_id :=  nextval('myseq'); -- in plpgsql
-- else find another way to assign unique id per thread

SQL:

INSERT INTO tmptbl(thread_id, col1) VALUES
(my_id, 2), (my_id, 3), (my_id, 4);

SELECT * FROM tmptbl WHERE thread_id = my_id;

- Erwin Brandstetter

---

整个重点在于我需要每个线程不同的临时表。难道没有创建本地临时表的方法吗？ - netta

@netta：临时表是本地会话的。如果您想使用相同的名称进行操作，请使用单独的会话。否则，您必须使用动态SQL，就像我在我的答案中添加的那样。或者使用每个线程的唯一ID - 我在我的答案中添加了另一个想法。 - Erwin Brandstetter

这是在PostgreSQL中能达到的最优雅的方式。做得好，先生。 - IamIC

如果一个临时表被标记为WITH ON COMMIT DROP，PG将如何处理访问该表的多个线程？任何线程都可以提交，但是由于该表正在使用中，因此无法删除。 - IamIC

@IamIC：在同一会话中只能运行一个事务，因此这种情况是不可能的。理论上来说，这是可以改变的（AFAIC），但目前就是这样实现的。 - Erwin Brandstetter