PG全局臨時(shí)表插件的使用

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

PG全局臨時(shí)表插件的使用

背景

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

全局臨時(shí)表插件的安裝部署

一. 安裝

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

臨時(shí)生效環(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

查看三個(gè)pgtt相關(guān)文件（有證明編譯安裝好了，可以下一步，否則沒(méi)有編譯安裝好，需要重新編譯安裝）

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

二. 配置

非超級(jí)用戶使用臨時(shí)表需做如下設(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/

--進(jìn)不去就用root用戶進(jìn)/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

運(yùn)行單元測(cè)試用例

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

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

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

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

▼▼▼
set pgtt.enabled to on;

創(chuàng)建擴(kuò)展插件

▼▼▼
c sscyy
create extension pgtt;

全局臨時(shí)表的使用

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

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

----加載動(dòng)態(tài)庫(kù)文件，數(shù)據(jù)庫(kù)重啟之后需要重新load
ksl=> load $libdir/plugins/pgtt; 
LOAD

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

-----使用load加載之后自動(dòng)的修改了search_path
-----同時(shí)需要注意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)

再打開(kāi)一個(gè)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)建全局臨時(shí)表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. 刪除全局臨時(shí)表

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

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

同時(shí)需要檢查下pg_global_temp_tables表是否刪除成功
select * from pg_global_temp_tables where relname=test2;

4. 創(chuàng)建索引

需要超級(jí)用戶權(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é)

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

END

更多精彩干貨分享

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

IT那活兒

云服務(wù)器 GPU云服務(wù)器帶臨時(shí)表的存儲(chǔ)過(guò)程 pg外部表臨時(shí)表臨時(shí)表遞歸

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

轉(zhuǎn)載請(qǐng)注明本文地址：http://systransis.cn/yun/129923.html

發(fā)表評(píng)論

登陸后可評(píng)論

0條評(píng)論

IT那活兒

男|高級(jí)講師

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

TA的文章

消息中間件故障分析一例

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

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

閱讀 1215·2023-01-11 13:20
雙節(jié)點(diǎn)RAC實(shí)例2 HANG 故障分析一例

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

閱讀 4165·2023-01-11 13:20
CRS啟動(dòng)報(bào)錯(cuò)CRS-1656處理分享

閱讀 2757·2023-01-11 13:20
oracle 12CR2打補(bǔ)丁報(bào)錯(cuò)處理一例

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

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

成人国产在线小视频_日韩寡妇人妻调教在线播放_色成人www永久在线观看_2018国产精品久久_亚洲欧美高清在线30p_亚洲少妇综合一区_黄色在线播放国产_亚洲另类技巧小说校园_国产主播xx日韩_a级毛片在线免费

資訊專欄INFORMATION COLUMN

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

PG全局臨時(shí)表插件的使用

一. 安裝

二. 配置

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

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

3. 刪除全局臨時(shí)表

4. 創(chuàng)建索引

5. 添加約束

相關(guān)文章

初識(shí)Postgres-XL

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

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

PostgreSQL對(duì)接SequoiaDB

PostgreSQL的實(shí)踐一：初識(shí)

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

發(fā)表評(píng)論

0條評(píng)論

IT那活兒

男|高級(jí)講師

TA的文章

消息中間件故障分析一例

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

ORA-600處理一例

雙節(jié)點(diǎn)RAC實(shí)例2 HANG 故障分析一例

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

CRS啟動(dòng)報(bào)錯(cuò)CRS-1656處理分享

oracle 12CR2打補(bǔ)丁報(bào)錯(cuò)處理一例

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

最新活動(dòng)

資訊專欄INFORMATION COLUMN

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

PG全局臨時(shí)表插件的使用

一. 安裝

二. 配置

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

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

3. 刪除全局臨時(shí)表

4. 創(chuàng)建索引

5. 添加約束

相關(guān)文章

發(fā)表評(píng)論

0條評(píng)論

男|高級(jí)講師

TA的文章

最新活動(dòng)

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