postgresql 创建一个临时表（如果不存在）以用于自定义过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22929365/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Create a temp table (if not exists) for use into a custom procedure
提问by アレックス
I'm trying to get the hang of using temp tables:
我正在尝试掌握使用临时表的窍门：
CREATE OR REPLACE FUNCTION test1(user_id BIGINT) RETURNS BIGINT AS
$BODY$
BEGIN
create temp table temp_table1
ON COMMIT DELETE ROWS
as SELECT table1.column1, table1.column2
FROM table1
INNER JOIN -- ............
if exists (select * from temp_table1) then
-- work with the result
return 777;
else
return 0;
end if;
END;
$BODY$
LANGUAGE plpgsql;
I want the row temp_table1to be deleted immediately or as soon as possible, that's why I added ON COMMIT DELETE ROWS. Obviously, I got the error:
我希望temp_table1立即或尽快删除该行，这就是我添加ON COMMIT DELETE ROWS. 显然，我得到了错误：
ERROR: relation "temp_table1" already exists
I tried to add IF NOT EXISTSbut I couldn't, I simply couldn't find working example of it that would be the I'm looking for.
我试图添加IF NOT EXISTS但我不能，我只是找不到它的工作示例，这将是我正在寻找的。
Your suggestions?
你的建议？
回答by Ilesh Patel
DROP Table each time before creating TEMP table as below:
每次创建 TEMP 表之前的 DROP 表如下：
BEGIN
DROP TABLE IF EXISTS temp_table1;
create temp table temp_table1
-- Your rest Code comes here
回答by klin
You want to DROP term table after commit (not DELETE ROWS), so:
您想在提交后删除术语表（而不是 DELETE ROWS），因此：
begin
create temp table temp_table1
on commit drop
...
回答by Ivan Kolyhalov
The problem of temp tables is that dropping and recreating temp table bloats pg_attribute heavily and therefore one sunny morning you will find db performance dead, and pg_attribute 200+ gb while your db would be like 10gb.
临时表的问题在于，删除和重新创建临时表会使 pg_attribute 大量膨胀，因此在一个阳光明媚的早晨，您会发现 db 性能下降，而 pg_attribute 为 200+ gb，而您的 db 则为 10gb。
So we're very heavy on temp tables having >500 rps and async i\o via nodejs and thus experienced a very heavy bloating of pg_attribute because of that. All you are left with is a very aggressive vacuuming which halts performance. All answers given here do not solve this, because they all bloat pg_attribute heavily.
因此，我们非常重视通过 nodejs 具有 >500 rps 和异步 i\o 的临时表，因此经历了非常严重的 pg_attribute 膨胀。剩下的只是一个非常激进的吸尘，它会停止性能。这里给出的所有答案都不能解决这个问题，因为它们都严重膨胀 pg_attribute。
So the solution is elegantly this
所以解决方案很优雅
create temp table if not exists my_temp_table (description) on commit delete rows;
So you go on playing with temp tables and save your pg_attribute.
所以你继续玩临时表并保存你的 pg_attribute。
