临时表一直存在,并被应用程序开发人员广泛使用。然而,临时表的功能远不止表面看上去的那么简单。PostgreSQL 允许您以一种很好的方式配置临时表的生命周期,并有助于避免一些常见的陷阱。
创建临时表
默认情况下,临时表的存在时间与数据库连接的时间一样长。一旦断开连接,它就会被删除。在许多情况下,这是人们想要的行为:
tmp=# CREATE TEMPORARY TABLE x (id int);
CREATE TABLE
tmp=# \d
List of relations
Schema | Name | Type | Owner
-----------+------+-------+-------
pg_temp_3 | x | table | hs
(1 row)
tmp=# \q
iMac:~ hs$ psql tmp
psql (12.3)
Type "help" for help.
tmp=# \d
Did not find any relations.
一旦我们重新连接,表就永远消失了。另外,请记住,临时表仅在您的会话中可见。其他连接不会看到该表(当然,这是期望的行为)。这也意味着许多会话可以创建具有相同名称的临时表。
然而,临时表可以做更多的事情。最重要的是能够控制提交时发生的情况:
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
3 个可用选项
如您所见,有三个选项。PRESERVE ROWS
是您刚刚目睹的行为。有时你不希望那样。因此,也可以在提交时清空临时表:
tmp=# BEGIN;
BEGIN
tmp=# CREATE TEMP TABLE x ON COMMIT DELETE ROWS AS
SELECT * FROM generate_series(1, 5) AS y;
SELECT 5
tmp=# SELECT * FROM x;
y
---
1
2
3
4
5
(5 rows)
tmp=# COMMIT;
COMMIT
tmp=# SELECT * FROM x;
y
---
(0 rows)
在这种情况下,一旦事务结束,PostgreSQL 就会给我们留下一个空表。表本身仍然存在并且可以使用。
让我们暂时删除该表:
tmp=# DROP TABLE x;
DROP TABLE
有时您希望整个表在事务结束时消失:可以使用ON COMMIT DROP
来实现这一点:
tmp=# BEGIN;
BEGIN
tmp=# CREATE TEMP TABLE x ON COMMIT DROP AS
SELECT * FROM generate_series(1, 5) AS y;
SELECT 5
tmp=# COMMIT;
COMMIT
tmp=# SELECT * FROM x;
ERROR: relation "x" does not exist
LINE 1: SELECT * FROM x;
PostgreSQL 会抛出错误,因为表已经消失了。这里值得注意的是,您仍然可以使用WITH HOLD
游标,如下一个示例所示:
tmp=# BEGIN;
BEGIN
tmp=# CREATE TEMP TABLE x ON COMMIT DROP AS SELECT * FROM generate_series(1, 5) AS y;
SELECT 5
tmp=# DECLARE mycur CURSOR WITH HOLD FOR SELECT * FROM x;
DECLARE CURSOR
tmp=# COMMIT;
COMMIT
tmp=# FETCH ALL FROM mycur;
y
---
1
2
3
4
5
(5 rows)
表本身仍然不存在,但是 WITH HOLD
游标将确保游标的“内容”在事务结束后仍然存在。许多人并没有预料到这种行为,但它是有道理的,并且可以派上用场。
控制内存使用
如果您使用临时表,则将它们保持相对较小是有意义的。然而,在某些情况下,临时表可能由于某种原因而变得相当大。为了确保性能保持良好,您可以告诉 PostgreSQL 在 RAM 中保留更多临时表。temp_buffers
是 postgresql.conf 中的参数,在这种情况下您应该查看:
tmp=# SHOW temp_buffers;
temp_buffers
--------------
8MB
(1 row)
默认值为 8 MB。如果您的临时表很大,那么增加这个值当然是有意义的。