PostgreSQL的临时表和全局临时表
PostgreSQL的临时表和全局临时表
目录
前言
在PostgreSQL中，虽然没有像其他数据库（如Oracle）那样的全局临时表（Global Temporary Table）的概念，但是PostgreSQL提供了临时表（Temporary Table），并且可以通过一些方式模拟全局临时表的行为。
PostgreSQL临时表
PostgreSQL的临时表具有以下特性：
- 临时表在会话结束时自动删除，或者在事务结束时删除（如果使用ON COMMIT DROP选项）。
- 临时表只在创建它的会话中可见，不同会话可以创建同名的临时表而不会冲突。
- 临时表可以创建索引、约束等。
PostgreSQL 支持三种类型的临时表：
-- 创建一个临时表，事务结束后删除
CREATE TEMP TABLE temp_table_name (
id INT,
name VARCHAR(100)
) ON COMMIT DROP;
-- 创建一个临时表，会话结束时删除（默认行为）
CREATE TEMP TABLE temp_table_name (
id INT,
name VARCHAR(100)
);
-- 创建一个临时表，事务结束后保留数据，但会话结束时删除
CREATE TEMP TABLE temp_table_name (
id INT,
name VARCHAR(100)
) ON COMMIT PRESERVE ROWS;
PostgreSQL全局临时表插件pgtt的使用
PostgreSQL 官方目前没有内置的“全局临时表”功能，但你可以通过 pgtt 插件 来实现类似 Oracle 或 DB2 中全局临时表的行为。这个插件主要为那些在迁移数据库时，希望在不重写应用代码的情况下保留原有功能而设计。
简单来说，使用 pgtt 创建的全局临时表，其表定义（结构）是持久且所有会话共享的，但表中的数据是会话隔离且临时的（会话结束后数据自动清理）。
以下是它的核心特性、安装和使用方法。
pgtt 与 PostgreSQL 原生临时表对比
| 特性 | PostgreSQL 原生临时表 (CREATE TEMP TABLE) | pgtt 全局临时表 |
|---|---|---|
| 表定义可见性 | 仅限创建它的会话，其他会话不可见。 | 全局可见，所有会话都可以使用相同的表结构。 |
| 数据作用域 | 数据仅限于创建它的会话或事务（取决于 ON COMMIT 子句）。 | 数据仅限于插入数据的会话，不同会话的数据互相隔离。 |
| 数据持久性 | 会话或事务结束时，数据和表定义自动删除。 | 表定义持久保留，会话结束时仅自动删除该会话的数据。 |
| 适用场景 | 单个会话内部的复杂计算、中间结果暂存。 | 应用需要跨多个会话使用相同的临时表结构，特别是从 Oracle/DB2 迁移时。 |
安装与启用 pgtt
PostgreSQL目前到最新12版本只支持本地临时表不支持全局临时表特性 ，会话退出后临时表定义和数据被删除，创建临时表语法如下：
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [
{ column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
| table_constraint
| LIKE source_table [ like_option ... ] }
[, ... ]
] )
[ INHERITS ( parent_table [, ... ] ) ]
[ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]
[ USING method ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]
虽然语法上支持GLOBAL，但GLOBAL与LOCAL并没有区别。
全局临时表插件pgtt安装
git clone https://github.com/darold/pgtt.git
cd pgtt
make
make install
psql:
CREATE EXTENSION pgtt;
使用 pgtt
创建全局临时表：在需要使用全局临时表的会话中，使用 CREATE GLOBAL TEMPORARY TABLE 语法。表结构会被存储在 pgtt_schema 中，供其他会话复用，但每个会话的数据是独立的。
启动开关：
1.session级别临时启用或关闭
postgres=# SET pgtt.enabled TO off;
SET
postgres=# SET pgtt.enabled TO on;
SET
2.单个数据库永久启用或关闭
alter database postgres set pgtt.enabled to on;
5.普通用户使用用例
创建普通用户
postgres=# create user test;
CREATE ROLE
postgres=# create database test owner test;
CREATE DATABASE
postgres=# \c test postgres
You are now connected to database "test" as user "postgres".
创建扩展，在每一个需要使用全局临时表的数据库上使用超级权限用户创建pgtt扩展。
test=# create extension pgtt;
CREATE EXTENSION
使用普通用户连接测试
postgres=# \c test test
test=> show search_path;
search_path
-----------------
"$user", public
(1 row)
加载动态库文件，数据库重启之后需要重新load
load '/data/postgresql/pg16/lib/pgtt'
test=> show search_path;
search_path
--------------------
public,pgtt_schema
(1 row)
使用load加载之后自动的修改了search_path
同时需要注意pgtt_schema要放在最后。
创建全局临时表ON COMMIT PRESERVE
CREATE /*GLOBAL*/ TEMPORARY TABLE test_gtt_table (
id integer,
lbl text
) ON COMMIT PRESERVE ROWS;
test=> insert into test_gtt_table values(1,'data1');
INSERT 0 1
test=> select * from test_gtt_table ;
id | lbl
----+-------
1 | data1
(1 row)
再打开一个session连接查看
$ psql test test
postgres=# set search_path to public,pgtt_schema;
SET
test=> select * from test_gtt_table;
id | lbl
----+-----
(0 rows)
可以看到表结构是存在的，数据为空
创建全局临时表ON COMMIT DELETE
test=> load '$libdir/plugins/pgtt';
LOAD
CREATE /*GLOBAL*/ TEMPORARY TABLE test6_gtt_table (
id integer,
lbl text
) ON COMMIT DELETE ROWS;
test=> begin;
BEGIN
test=> insert into test2_gtt_table values(2,'data2');
INSERT 0 1
test=> select * from test2_gtt_table ;
id | lbl
----+-------
2 | data2
(1 row)
test=> commit;
COMMIT
test=> select * from test2_gtt_table ;
id | lbl
----+-----
(0 rows)
6.删除全局临时表
与删除普通表没有任何区别，需要超级用户权限
test=# load '$libdir/plugins/pgtt';
LOAD
test=# drop table test2_gtt_table ;
DROP TABLE
同时需要检查下pg_global_temp_tables表是否删除成功
select * from pg_global_temp_tables where relname='test2_gtt_table ';
7.创建索引
需要超级用户权限
test=# CREATE INDEX ON test_gtt_table (id);
CREATE INDEX
test=# \d test_gtt_table
Unlogged table "pgtt_schema.test_gtt_table"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | |
lbl | text | | |
Indexes:
"test_gtt_table_id_idx" btree (id)
8.添加约束
需要超级用户权限
test=> load '$libdir/plugins/pgtt';
LOAD
CREATE /*GLOBAL*/ TEMPORARY TABLE t2 (
c1 serial PRIMARY KEY,
c2 VARCHAR (50) UNIQUE NOT NULL,
c3 boolean DEFAULT false
);
但不支持外键
CREATE /*GLOBAL*/ TEMPORARY TABLE t3 (
c1 int,
FOREIGN KEY (c1) REFERENCES tb1 (id)
);
ERROR: attempt to create referential integrity constraint on global temporary table
CONTEXT: SQL statement "CREATE UNLOGGED TABLE pgtt_schema.t3 (
c1 int,
FOREIGN KEY (c1) REFERENCES tb1 (id)
)"
也不支持分区表
使用注意
- 1.使用普通用户安装注意第2步
- 2.全局临时表不能随便删除，未使用之前可以删除
- 3.每次创建全局临时表需要先load
- 4.目前不支持外键和分区表
浙公网安备 33010602011771号