Postgresql COPY命令 - 如何删除临时文件？

Question

我有一个程序，它运行得很好。

CREATE OR REPLACE FUNCTION f_dynamic_copy(_tbl     text = 'tmp1')
RETURNS void AS
$func$
DECLARE
   _filename VARCHAR;
BEGIN
  _filename := '/tmp/' || random() || '.csv';
  EXECUTE format($$COPY (select id, 1, 1, 1 from my_first_table) TO %L$$, _filename);
  EXECUTE format($$COPY my_second_table FROM %L$$, _filename);
END
$func$  LANGUAGE plpgsql;

但是我想删除在此过程中创建的临时文件。如何做到？

- Nick

---

你不能通过plpgsql或SQL访问文件系统，除了COPY FROM和COPY TO之外的任何内容。你可以使用plperlu函数或cron作业清理旧文件。 - Ezequiel Tolnay

Answer 1

我找到了PostgreSQL 9.3+的解决方案：

-- Delete temporary file
EXECUTE format(
  $$COPY (SELECT 1) TO PROGRAM 'rm %I'$$,
  _filename
);

所以，我的函数是

CREATE OR REPLACE FUNCTION f_dynamic_copy(_tbl     text = 'tmp1')
RETURNS void AS
$func$
DECLARE
   _filename VARCHAR;
BEGIN
  _filename := '/tmp/' || random() || '.csv';
  EXECUTE format($$COPY (select id, 1, 1, 1 from my_first_table) TO %L$$, _filename);
  EXECUTE format($$COPY my_second_table FROM %L$$, _filename);

  -- Delete temporary file
  EXECUTE format(
    $$COPY (SELECT 1) TO PROGRAM 'rm %I'$$,
    _filename
  );

END
$func$  LANGUAGE plpgsql;

- Nick

---

是的，这是一个技巧，黑客手段。在我的情况下，文件名具有完全可控结构，因此我确信这是一个安全的解决方案。如果攻击者覆盖random()或format()函数，我可能会遇到问题。但这种情况非常糟糕，不应该发生。 - Nick

Answer 2

plpgsql没有删除文件的函数。您需要在perl或python中创建存储函数才能删除临时文件。但是，您真的需要一个临时文件吗？使用INSERT SELECT不需要存储函数，同样可以完成工作。

INSERT INTO my_second_table SELECT id, 1, 1, 1 from my_first_table

- e4c5

---

这里的性能有显著的差异吗？如果COPY是唯一的选项，我想它将不得不是plpython或类似的东西。 - e4c5

请注意，使用COPY加载大量行通常比使用INSERT更快，即使使用PREPARE并将多个插入批处理到单个事务中也是如此。 在我的情况下，COPY比INSERT快5倍。 - Nick