postgresql临时表的线程安全性 [英] postgresql thread safety for temporary tables
问题描述
这是我用来创建临时表的语法：
This the syntax I use for creating a temporary table:
create temp table tmpTable (id bigint not null, primary key (id)) on commit drop;
我知道这意味着在每笔交易结束时，此表格将被删除。
我的问题是，如果同一会话中的两个或多个线程创建并将值插入临时表，它们每个都会获得自己的实例，还是会话中共享的临时实例？如果它是共享的，有没有办法让它每个线程本地？
I know this means that at the end of each transaction, this table will be dropped. My question is, if two or more threads on the same session create and insert values into a temporary table, will they each get their own instance or is the temporary instance shared across the session? If it's shared, is there a way to make it local per thread?
谢谢
Netta
Thanks Netta
推荐答案
临时表对同一会话中的所有操作都是可见的。因此，您无法在删除现有的临时表之前创建一个同名的临时表（在您的情况下提交事务）。
Temporary tables are visible to all operations in the same session. So you cannot create a temporary table of the same name in the same session before you drop the one that exists (commit the transaction in your case).
您可能想要使用：
CREATE TEMP TABLE tmptbl IF NOT EXISTS ...
More about CREATE TABLE in the manual.
要使临时表在每个线程本地（在同一会话中），您需要使用唯一表格名称。一种方法是使用未绑定的 SEQUENCE 和动态SQL - 使用plpgsql或DO语句等过程语言（基本相同而不存储函数。
To make the temp table local per "thread" (in the same session) you need to use unique table names. One way would be to use an unbound SEQUENCE and dynamic SQL - in a procedural language like plpgsql or in a DO statement (which is basically the same without storing a function.
运行一个：
CREATE SEQUENCE myseq;
使用：
DO $$
BEGIN
EXECUTE 'CREATE TABLE tmp' || nextval('myseq') ||'(id int)';
END;
$$
要知道最新的表名：
SELECT 'tmp' || currval('myseq');
或者放把它全部放到plpgsql函数中并返回表或重用表名。
Or put it all into a plpgsql function and return the table or reuse the table name.
所有进一步的SQL命令都必须动态执行，因为普通的SQL语句很难运行因此，将它全部放入plpgsql函数中可能是最好的。
All further SQL commands have to be executed dynamically, though, as plain SQL statements operate with hard coded identifiers. So, it is probably best, to put it all into a plpgsql function.
Anot她可能的解决方案是对同一会话中的所有线程使用相同的临时表并添加一列 thread_id 到桌子。如果大量使用该功能，请务必为该列编制索引。然后在每个线程中使用唯一的 thread_id （在同一个会话中）。
Another possible solution could be to use the same temp table for all threads in the same session and add a column thread_id to the table. Be sure to index the column, if you make heavy use of the feature. Then use a unique thread_id per thread (in the same session).
仅限一次：
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;
这篇关于postgresql临时表的线程安全性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
