PG全局臨時表插件的使用

IT那活兒發(fā)布于2023-01-11 13:20 / 3373人閱讀

PG全局臨時表插件的使用

背景

PostgreSQL目前12版本只支持本地臨時表不支持全局臨時表特性，會話退出后臨時表定義和數(shù)據(jù)被刪除，在我們PG遷移之后業(yè)務(wù)需要用到全局臨時表，為了完成這個需求，我們是采用PG的一個全局臨時表插件pgtt-2.1來實現(xiàn)的。

全局臨時表插件的安裝部署

一. 安裝

▼▼▼
[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

臨時生效環(huán)境變量

▼▼▼
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相關(guān)文件（有證明編譯安裝好了，可以下一步，否則沒有編譯安裝好，需要重新編譯安裝）

▼▼▼
[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

二. 配置

非超級用戶使用臨時表需做如下設(shè)置

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

單個數(shù)據(jù)庫永久啟用(off為關(guān)閉)

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

Session級別的啟用（off為關(guān)閉）

▼▼▼
set pgtt.enabled to on;

創(chuàng)建擴展插件

▼▼▼
c sscyy
create extension pgtt;

全局臨時表的使用

1. 創(chuàng)建全局臨時表ON COMMIT PRESERVE

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

----加載動態(tài)庫文件，數(shù)據(jù)庫重啟之后需要重新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)

可以看到表結(jié)構(gòu)是存在的，數(shù)據(jù)為空

2. 創(chuàng)建全局臨時表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. 刪除全局臨時表

與刪除普通表沒有任何區(qū)別，需要超級用戶權(quán)限

▼▼▼
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. 創(chuàng)建索引

需要超級用戶權(quán)限

▼▼▼
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
);

但不支持外鍵(也不支持分區(qū)表)

▼▼▼
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)
)"

總結(jié)

普通用戶使用安裝的時候需要創(chuàng)建軟連接（見配置）。
每次創(chuàng)建全局臨時表需要先load。
該插件支持約束，不支持外鍵和分區(qū)表。
全局臨時表建議不要隨便drop，當(dāng)然沒有用過的全局臨時表隨便drop。

END

更多精彩干貨分享

點擊下方名片關(guān)注

IT那活兒

云服務(wù)器 GPU云服務(wù)器帶臨時表的存儲過程 pg外部表臨時表臨時表遞歸

文章版權(quán)歸作者所有，未經(jīng)允許請勿轉(zhuǎn)載,若此文章存在違規(guī)行為，您可以聯(lián)系管理員刪除。

轉(zhuǎn)載請注明本文地址：http://m.hztianpu.com/yun/129923.html

發(fā)表評論

登陸后可評論

0條評論

IT那活兒

男|高級講師

我要關(guān)注我要私信

TA的文章

消息中間件故障分析一例

閱讀 1459·2023-01-11 13:20
RAC雙節(jié)點crash回復(fù)一例

閱讀 1813·2023-01-11 13:20
ORA-600處理一例

閱讀 1263·2023-01-11 13:20
雙節(jié)點RAC實例2 HANG 故障分析一例

閱讀 2006·2023-01-11 13:20
RAC集群節(jié)點1重啟分析一例

閱讀 4226·2023-01-11 13:20
CRS啟動報錯CRS-1656處理分享

閱讀 2879·2023-01-11 13:20
oracle 12CR2打補丁報錯處理一例

閱讀 1488·2023-01-11 13:20
分布式緩存組件故障分析及監(jiān)控優(yōu)化

閱讀 3807·2023-01-11 13:20

成人无码视频,亚洲精品久久久久av无码,午夜精品久久久久久毛片,亚洲中文字幕日韩无码

資訊專欄INFORMATION COLUMN

上云采購季！| 2核2G4M爆款云服務(wù)器低至59元/年，更有多臺、長期優(yōu)惠，快來選購！

PG全局臨時表插件的使用

一. 安裝

二. 配置

1. 創(chuàng)建全局臨時表ON COMMIT PRESERVE

2. 創(chuàng)建全局臨時表ON COMMIT DELETE

3. 刪除全局臨時表

4. 創(chuàng)建索引

5. 添加約束

相關(guān)文章

初識Postgres-XL

PostgreSQL 自動分區(qū)分表維護管理插件 pathman 基礎(chǔ)使用

【PHP7源碼分析】PHP中$_POST揭秘

PostgreSQL對接SequoiaDB

PostgreSQL的實踐一：初識

Docker 部署 Kong 網(wǎng)關(guān)

發(fā)表評論

0條評論

IT那活兒

男|高級講師

TA的文章

消息中間件故障分析一例

RAC雙節(jié)點crash回復(fù)一例

ORA-600處理一例

雙節(jié)點RAC實例2 HANG 故障分析一例

RAC集群節(jié)點1重啟分析一例

CRS啟動報錯CRS-1656處理分享

oracle 12CR2打補丁報錯處理一例

分布式緩存組件故障分析及監(jiān)控優(yōu)化

最新活動

資訊專欄INFORMATION COLUMN

上云采購季！| 2核2G4M爆款云服務(wù)器低至59元/年，更有多臺、長期優(yōu)惠，快來選購！

PG全局臨時表插件的使用

一. 安裝

二. 配置

1. 創(chuàng)建全局臨時表ON COMMIT PRESERVE

2. 創(chuàng)建全局臨時表ON COMMIT DELETE

3. 刪除全局臨時表

4. 創(chuàng)建索引

5. 添加約束

相關(guān)文章

發(fā)表評論

0條評論

男|高級講師

TA的文章

最新活動

上云采購季！| 2核2G4M爆款云服務(wù)器低至59元/年，更有多臺、長期優(yōu)惠，快來選購！