PG全局臨時表插件的使用

IT那活兒發布于2023-01-11 13:20 / 3291人閱讀

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外部表臨時表臨時表遞歸

文章版權歸作者所有，未經允許請勿轉載,若此文章存在違規行為，您可以聯系管理員刪除。

轉載請注明本文地址：http://m.specialneedsforspecialkids.com/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