openGauss数据与PostgreSQL的差异之全局临时表

openGauss的全局临时表

openGauss支持全局临时表，即建立的临时表的定义在当session退出后还可以看到，同时其他用户也可以看到。而目前的PostgreSQL数据库一直没有全局临时表,如果需要可用使用插件：https://github.com/darold/pgtt

演示差异

在openGauss中建一个全局临时表：

postgres=# create global temp table tang01(id int primary key, t text);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "tang01_pkey" for table "tang01"
CREATE TABLE
postgres=# \d
                                       List of relations
 Schema |  Name  | Type  | Owner |                           Storage                            
--------+--------+-------+-------+--------------------------------------------------------------
 public | tang01 | table | gauss | {orientation=row,compression=no,on_commit_delete_rows=false}
 public | test01 | table | gauss | {orientation=row,compression=no}
(2 rows)

postgres=# create local temp table tang02(id int primary key, t text);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "tang02_pkey" for table "tang02"
CREATE TABLE
postgres=# postgres=#

退出psql，再进入，用\d查看表：

postgres-# \d
                                                     List of relations
               Schema               |  Name  | Type  | Owner |                           Storage                            
------------------------------------+--------+-------+-------+--------------------------------------------------------------
 public                             | tang01 | table | gauss | {orientation=row,compression=no,on_commit_delete_rows=false}
 public                             | test01 | table | gauss | {orientation=row,compression=no}
(3 rows)

从上面可以看出，退出session后，仍然可以看到全局的临时表。同时可以看到全局临时表使用固定的模式（上面是public模式）。

我们再建一个局部临时表：

postgres=# create local temp table tang02(id int primary key, t text);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "tang02_pkey" for table "tang02"
CREATE TABLE
postgres=#
postgres-# \d
                                                     List of relations
               Schema               |  Name  | Type  | Owner |                           Storage                            
------------------------------------+--------+-------+-------+--------------------------------------------------------------
 pg_temp_datanod_3_1_47543245477632 | tang02 | table | gauss | {orientation=row,compression=no}
 public                             | tang01 | table | gauss | {orientation=row,compression=no,on_commit_delete_rows=false}
 public                             | test01 | table | gauss | {orientation=row,compression=no}
(3 rows)

上面可以看出局部临时表的模式是一个临时的“pg_temp_datanod_3_1_47543245477632”，我们退出session，再用gsql连接进来，局部临时表就消失了：

postgres-# \q
[gauss@pgtrain master]$ rlwrap gsql postgres
gsql ((openGauss 2.0.1 build d97c0e8a) compiled at 2021-06-02 19:37:17 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
postgres=# \d
                                       List of relations
 Schema |  Name  | Type  | Owner |                           Storage                            
--------+--------+-------+-------+--------------------------------------------------------------
 public | tang01 | table | gauss | {orientation=row,compression=no,on_commit_delete_rows=false}
 public | test01 | table | gauss | {orientation=row,compression=no}
(2 rows)

如果我们不加全局和局部的关键字“gobal”和“local”，建立出来的是局部临时表：

postgres=# create temp table tang03(id int primary key, t text);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "tang03_pkey" for table "tang03"
CREATE TABLE
postgres=# \d
                                                     List of relations
               Schema               |  Name  | Type  | Owner |                           Storage                            
------------------------------------+--------+-------+-------+--------------------------------------------------------------
 pg_temp_datanod_3_2_47543262258944 | tang03 | table | gauss | {orientation=row,compression=no}
 public                             | tang01 | table | gauss | {orientation=row,compression=no,on_commit_delete_rows=false}
 public                             | test01 | table | gauss | {orientation=row,compression=no}
(3 rows)