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

原创多米爸比 2020-09-02

3514

参考 https://github.com/darold/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并没有区别。

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_regress --inputdir=./ --bindir='/opt/pg122/bin'    --inputdir=test --dbname=contrib_regression 00_init 01_oncommitdelete 02_oncommitpreserve 03_createontruncate 04_rename 05_useindex 06_createas 07_createlike 08_plplgsql 09_transaction 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            ... ok           39 ms
test 02_oncommitpreserve          ... ok           35 ms
test 03_createontruncate          ... ok           40 ms
test 04_rename                    ... ok           63 ms
test 05_useindex                  ... ok           52 ms
test 06_createas                  ... ok           37 ms
test 07_createlike                ... ok           54 ms
test 08_plplgsql                  ... ok           40 ms
test 09_transaction               ... ok           40 ms
test 10_foreignkey                ... ok           15 ms
test 11_partition                 ... ok            8 ms

======================
 All 12 tests passed. 
======================

4.启用开关

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

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 temporary table
CONTEXT:  SQL statement "CREATE UNLOGGED TABLE pgtt_schema.t3 (
c1 int,
FOREIGN KEY (c1) REFERENCES tb1 (id)
)"

也不支持分区表