openGauss的全局临时表

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

演示差异

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

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

退出psql,再进入,用\d查看表:

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

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

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

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

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

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

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

  1. postgres=# create temp table tang03(id int primary key, t text);
  2. NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "tang03_pkey" for table "tang03"
  3. CREATE TABLE
  4. postgres=# \d
  5. List of relations
  6. Schema | Name | Type | Owner | Storage
  7. ------------------------------------+--------+-------+-------+--------------------------------------------------------------
  8. pg_temp_datanod_3_2_47543262258944 | tang03 | table | gauss | {orientation=row,compression=no}
  9. public | tang01 | table | gauss | {orientation=row,compression=no,on_commit_delete_rows=false}
  10. public | test01 | table | gauss | {orientation=row,compression=no}
  11. (3 rows)
本站文章,未经作者同意,请勿转载,如需转载,请邮件customer@csudata.com.
0 评论  
添加一条新评论