PG全局临时表插件的使用

IT那活儿发布于2023-01-11 13:20 / 3276人阅读

PG全局临时表插件的使用

背景

PostgreSQL目前12版本只支持本地临时表不支持全局临时表特性，会话退出后临时表定义和数据被删除，在我们PG迁移之后业务需要用到全局临时表，为了完成这个需求，我们是采用PG的一个全局临时表插件pgtt-2.1来实现的。

全局临时表插件的安装部署

一. 安装

▼▼▼
[root@test-telepg-01 ~]# su - telepg
[telepg@test-telepg-01 ~]$ cd /tmp
[telepg@test-telepg-01 tmp]$ wget https://codeload.github.com/darold/pgtt/tar.gz/v2.1
[telepg@test-telepg-01 tmp]$ tar -zxvf pgtt-2.1.tar.gz
[telepg@test-telepg-01 tmp]$ cd pgtt-2.1

临时生效环境变量

▼▼▼
export POSTGRES_HOME=/app/pg/pg_2_18803/postgresql
export ORACLE_HOME=/opt/oracle/product/18c/dbhome_1
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$POSTGRES_HOME/lib:$POSTGRES_HOME/lib/postgresql:$POSTGRES_HOME/plugin:$ORACLE_HOME/lib
export PROJ_LIB="$POSTGRES_HOME/share/proj"
export PATH=$PATH:$POSTGRES_HOME/bin

编译安装

▼▼▼
[telepg@test-telepg-01 pgtt-2.1]$ make
[telepg@test-telepg-01 pgtt-2.1]$ make install

查看三个pgtt相关文件（有证明编译安装好了，可以下一步，否则没有编译安装好，需要重新编译安装）

▼▼▼
[telepg@test-telepg-01 pgtt-2.1]$ ll /app/pg/pg_2_18803/postgresql/share/postgresql/extension/pgtt*
-rw-r--r-- 1 telepg telepg 824 Apr 2 14:47 /app/pg/pg_2_18803/postgresql/share/postgresql/extension/pgtt--2.1.0.sql
-rw-r--r-- 1 telepg telepg 177 Apr 2 14:47 /app/pg/pg_2_18803/postgresql/share/postgresql/extension/pgtt.control

[telepg@test-telepg-01 pgtt-2.1]$ ll /app/pg/pg_2_18803/postgresql/lib/postgresql/pgtt.so
-rwxr-xr-x 1 telepg telepg 43520 Apr 2 14:47 /app/pg/pg_2_18803/postgresql/lib/postgresql/pgtt.so

二. 配置

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

▼▼▼
[telepg@test-telepg-01 pgtt-2.1]$ export libdir=$(pg_config --pkglibdir)
[telepg@test-telepg-01 pgtt-2.1]$ sudo mkdir $libdir/plugins/
[telepg@test-telepg-01 pgtt-2.1]$ cd $libdir/plugins/

--进不去就用root用户进/app/pg/pg_2_18803/postgresql/lib/postgresql/plugins/

▼▼▼
[telepg@test-telepg-01 pgtt-2.1]$ sudo ln -s ../pgtt.so/
[root@test-telepg-01 plugins]# ll pgtt.so 
lrwxrwxrwx 1 root root 10 Apr 2 14:58 pgtt.so -> ../pgtt.so

运行单元测试用例

▼▼▼
$ 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.
======================

单个数据库永久启用(off为关闭)

▼▼▼
su - telepg
cd /app/telepg
. p3_18803env
c sscyy
alter database sscyy set pgtt.enabled to on;

Session级别的启用（off为关闭）

▼▼▼
set pgtt.enabled to on;

创建扩展插件

▼▼▼
c sscyy
create extension pgtt;

全局临时表的使用

1. 创建全局临时表ON COMMIT PRESERVE

▼▼▼
postgres=# c ksl ksl
ksl=> show search_path;
search_path
‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
"$user", public
(1 row)

----加载动态库文件，数据库重启之后需要重新load
ksl=> load $libdir/plugins/pgtt; 
LOAD

ksl=> show search_path;
search_path
‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
public,pgtt_schema
(1 row)

-----使用load加载之后自动的修改了search_path
-----同时需要注意pgtt_schema要放在最后。


CREATE /*GLOBAL*/ TEMPORARY TABLE test(
id integer, 
lbl text
) ON COMMIT PRESERVE ROWS;

ksl=> insert into test values(1,data1);
INSERT 0 1

ksl=> select * from test; 
id | lbl
‐‐‐‐+‐‐‐‐‐‐‐
1 | data1 
(1 row)

再打开一个session连接查看

▼▼▼
postgres=# set search_path to public,pgtt_schema;
SET
postgres=# c ksl ksl

ksl=> select * from test;
id | lbl
‐‐‐‐+‐‐‐‐‐
(0 rows)

可以看到表结构是存在的，数据为空

2. 创建全局临时表ON COMMIT DELETE

▼▼▼
ksl=> load$libdir/plugins/pgtt
LOAD

CREATE /*GLOBAL*/ TEMPORARY TABLE test2 (
id integer, lbl text
) ON COMMIT DELETE ROWS;

ksl=> begin;
BEGIN
ksl=> insert into test2 values(2,data2);
INSERT 0 1
ksl=> select * from test2; 
id | lbl
‐‐‐‐+‐‐‐‐‐‐‐
2 | data2
(1 row)

ksl=> commit;
COMMIT
ksl=> select * from test2; 
id | lbl
‐‐‐‐+‐‐‐‐‐
(0 rows)

3. 删除全局临时表

与删除普通表没有任何区别，需要超级用户权限

▼▼▼
ksl# load ’$libdir/plugins/pgtt
LOAD
ksl=# drop table test2 ; 
DROP TABLE

同时需要检查下pg_global_temp_tables表是否删除成功
select * from pg_global_temp_tables where relname=test2;

4. 创建索引

需要超级用户权限

▼▼▼
ksl=# CREATE INDEX ON test (id);
CREATE INDEX

ksl=# d test
Unlogged table "pgtt_schema.test" 
Column | Type | Collation | Nullable | Default
‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐
id | integer | | | 
lbl | text | | | 
Indexes:
”test_id_idx”btree (id)

5. 添加约束

▼▼▼
ksl=> load$libdir/plugins/pgtt’
LOAD

CREATE /*GLOBAL*/ TEMPORARY TABLE test3 (
c1 serial PRIMARY KEY,
c2 VARCHAR (50) UNIQUE NOT NULL,
c3 boolean DEFAULT false
);

但不支持外键(也不支持分区表)

▼▼▼
CREATE /*GLOBAL*/ TEMPORARY TABLE test4 (
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.test4 ( c1 int,
FOREIGN KEY (c1) REFERENCES tb1 (id)
)"

总结

普通用户使用安装的时候需要创建软连接（见配置）。
每次创建全局临时表需要先load。
该插件支持约束，不支持外键和分区表。
全局临时表建议不要随便drop，当然没有用过的全局临时表随便drop。

END

更多精彩干货分享

点击下方名片关注

IT那活儿

GPU云服务器云服务器带临时表的存储过程 pg外部表临时表临时表递归

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/129923.html

初识Postgres-XL

摘要：所有节点中分为三种角色全局事务管理器协调器和数据节点。一旦故障，整个集群立刻无法访问，此时可以切换到节点上。第一节简介Postgres-XL是一款开源的PG集群软件，XL代表eXtensible Lattice，即可扩展的PG格子之意，以下简称PGXL。官方称其既适合写操作压力较大的OLTP应用，又适合读操作为主的大数据应用。它的前身是Postgres-XC（简称PGXC），...

JasinYip 2019-07-25 12:33 评论0 收藏0
PostgreSQL 自动分区分表维护管理插件 pathman 基础使用

摘要：使用数据库会自动的根据从某几个片中读取数据。更加详细的请参考德哥文章官方地址：https://github.com/postgrespr...关于pathman的原理和优化问题，请移步至https://yq.aliyun.com/article... 检查环境变量如果直接执行psql命令提示command not found则执行下面的命令设置环境变量 root@host# PA...

MASAILA 2019-07-25 12:37 评论0 收藏0
【PHP7源码分析】PHP中$_POST揭秘

摘要：和进程的启动过程类似，启动过程有种进程角色启动进程进程和进程。直到请求到来，将连接赋值给对象的字段。注当进程执行完后会再次调用函数，准备监听新的请求。当读取到的时，会调用函数对进行解析，将中的以及存储到结构体中。运营研发团队季伟滨一、前言前几天的工作中，需要通过curl做一次接口测试。让我意外的是，通过$_POST竟然无法获取到Content-Type是application...

sf190404 2019-07-01 10:52 评论0 收藏0
PostgreSQL对接SequoiaDB

摘要：是一款开源的数据库，支持标准，用户可以通过驱动连接进行应用程序开发。本文就针对如何扩展功能，实现对接进行介绍。直接在中修改配置文件，只能在当前中生效，重新登录需要重新设置。 PostgreSQL是一款开源的SQL数据库，支持标准SQL，用户可以通过JDBC驱动连接PostgreSQL进行应用程序开发。用户通过扩展PostgreSQL功能，让开发者可以使用SQL语句访问SequoiaDB...

TZLLOG 2019-07-25 12:30 评论0 收藏0
PostgreSQL的实践一：初识

摘要：每个服务由多个进程组成，为首的进程名为。服务使用字节长的内部事务标识符，即时发生重叠后仍然继续使用，这会导致问题，所以需要定期进行操作。操作被认为是紧跟操作后的操作。在涉及高比例插入删除的表中，会造成索引膨胀，这时候可以重建索引。简介和认知发音 post-gres-q-l 服务（server）一个操作系统中可以启动多个postgres服务。每个服务由多个进程组成，为首的进程名为p...

yibinnn 2019-07-25 12:38 评论0 收藏0
Docker 部署 Kong 网关

摘要：自定义配置文件镜像的配置文件路径为如需自定义配置文件，自行挂载即可。配置项手册管理网关的的使用教程这里就不写了，自行觅食吧简单的看看下面这篇可以的集成插件服务网关 Kong 镜像: https://hub.docker.com/_/kong 官网给定的用户安装手册上并没有设置 PG 的密码，导致如下问题无法启动 nginx: [error] init_by_lua error: /us...

zhouzhou 2019-06-28 17:58 评论0 收藏0