PostgreSQL全局临时表插件pgtt的使用

PostgreSQL

前言

PostgreSQL目前到最新12版本只支持本地临时表不支持全局临时表特性 ，会话退出后临时表定义和数据被删除，创建临时表语法如下：

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NO
T 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并没有区别。

1、全局临时表插件pgtt安装

$ wget https://codeload.github.com/darold/pgtt/tar.gz/v2.1
$ tar ‐xvf v2.1
$ cd pgtt‐2.1/
$ which pg_config
/opt/pgsql/bin/pg_config
$ make
$ make install
$ ll /opt/pgsql/share/postgresql/extension/pgtt*
‐rw‐r‐‐r‐‐ 1 postgres postgres 824 Sep 1 09:53 
/opt/pgsql/share/postgresql/ extension/pgtt‐‐2.1.0.sql
‐rw‐r‐‐r‐‐ 1 postgres postgres 177 Sep 1 09:53 
/opt/pgsql/share/postgresql/ extension/pgtt.control
$ ll /opt/pgsql/lib/postgresql/pgtt.so
‐rwxr‐xr‐x 1 postgres postgres 43504 Sep 1 09:53
/opt/pgsql/lib/postgresql/pgtt.so

2、非超级用户使用临时表需做如下设置

export libdir$(pg_config ‐‐pkglibdir)
sudo mkdir $libdir/plugins/ 
cd $libdir/plugins/
sudo ln ‐s ../pgtt.so

3、运行单元测试用例

$ make installcheck
/opt/pg122/lib/postgresql/pgxs/src/makefiles/../../src/test/regress/pg_regr ess ‐‐inputdir=.
/ ‐‐bindir='/opt/pg122/bin' ‐‐inputdir=test ‐‐dbname=cont rib_regression 
00_init 01_oncommitdelete 02_oncommitpreserve 03_createont runcate 04_rename 
05_useindex 06_createas 07_createlike 08_plplgsql 09_tr ansaction 10_foreignkey 11_partition
(using postmaster on Unix socket, port 6000)
============== dropping database "contrib_regression" ============== DROP DATABASE
============== creating database "contrib_regression" ============== CREATE DATABASE
ALTER DATABASE
============== running regression test queries test 00_init ... ok 44 ms
test 01_oncommitdelete test 02_oncommitpreserve test 03_createontruncate
ok 39 ms
ok 35 ms
ok 40 ms
test 04_rename test 05_useindex test 06_createas test 07_createlike test 08_plplgsql
ok 63 ms
ok 52 ms
ok 37 ms
ok 54 ms
ok 40 ms
test 09_transaction test 10_foreignkey test 11_partition
ok 40 ms
ok 15 ms
ok 8 ms
======================
All 12 tests passed.
======================

4、启用开关

1.session级别临时启用或关闭

postgres=# SET pgtt.enabled TO off; SET
postgres=# SET pgtt.enabled TO on;

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  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
test=> load '$libdir/plugins/pgtt'; 
LOAD
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 tempora ry table
CONTEXT: SQL statement "CREATE UNLOGGED TABLE pgtt_schema.t3 ( c1 int,
FOREIGN KEY (c1) REFERENCES tb1 (id)
)"

也不支持分区表
注意：

1. 使用普通用户安装注意第2步
2. 全局临时表不能随便删除，未使用之前可以删除
3. 每次创建全局临时表需要先load
4. 支持约束，但不支持外键引用贺分区表