LightDB支持全局临时表

追魂曲

已于 2023-03-10 16:05:00 修改

阅读量209

点赞数

分类专栏： postgresql LightDB 文章标签：数据库 mysql

于 2023-03-10 15:55:38 首次发布

本文链接：https://blog.csdn.net/qq_22066003/article/details/129443163

版权

LightDB 同时被 2 个专栏收录

34 篇文章 1 订阅

订阅专栏

postgresql

32 篇文章 0 订阅

订阅专栏

1. 什么是全局临时表

简单的讲，全局临时表(GTT)对所有用户均可见，用户对GTT进行操作时，用户之间数据不可见。LightDB原生的临时表，当用户断开连接时，表就被清掉了，GTT则不会，表结构一直被保留。

2. GTT有哪些分类

GTT分为会话级GTT和事务级GTT。

会话级GTT，当会话断开时，GTT内容会被自动清掉。

事务级GTT，当事务提交时，GTT内容会被自动清掉。

3. 如何创建GTT

语法参考：CREATE TABLE

直接举例：

会话级GTT：

create global temporary table gtt_session(id number,ename varchar(15))on commit preserve rows;

事务级GTT:

create global temporary table gtt_transaction(id number,ename varchar(15))on commit delete rows;

会发现LightDB实现的GTT内部为unlogged表， unlogged表的好处就是：

1、unlogged table不记录wal日志，写入速度快，备库无数据，只有结构；
2、当数据库crash后，数据库重启时自动清空unlogged table的数据；
3、使用lt_basebackup备份时，不会备份unlogged table；

怎么看该表是否为GTT类型：

通过查询系统表lt_global_temp_table_ext

lightdb@postgres=# select * from lt_global_temp_table_ext;
 relid | nspname |     relname     | preserved | code 
-------+---------+-----------------+-----------+------
 43898 | public  | gtt_session     | t         | 
 43904 | public  | gtt_transaction | f         | 
(2 rows)

lightdb@postgres=#

4. 使用GTT注意的点

1）不支持分区表

lightdb@postgres=# CREATE global temporary TABLE gtt_test 
lightdb@postgres-# (
lightdb@postgres(#     a int,
lightdb@postgres(# b float,
lightdb@postgres(# c date,
lightdb@postgres(# d timestamp,
lightdb@postgres(# e varchar
lightdb@postgres(# ) PARTITION BY LIST(e)
lightdb@postgres-# (
lightdb@postgres(#     PARTITION p1 VALUES ('0001', '0002', '0003', '0004', '0005'),
lightdb@postgres(#     PARTITION p2 VALUES ('0006', '0007', '0008', '0009'),
lightdb@postgres(#     PARTITION p3 VALUES ('0010', '0011')
lightdb@postgres(# )on commit preserve rows;
ERROR:  Global Temporary Table do not support partitioning.
lightdb@postgres=#

2）当对GTT进行过DML操作后，再进行DDL操作将不被允许。

lightdb@postgres=# select * from gtt_session;
 id | ename 
----+-------
(0 rows)

lightdb@postgres=# drop table gtt_session;
ERROR:  can not drop a gtt that is in use.
lightdb@postgres=#

3）GTT支持分布式

GTT支持分布式，当对GTT建立分布式列后，用户断开连接，分布式信息也被清掉。

搭建一个简单的分布式环境，1CN和1DN

GTT信息如下：

lightdb@postgres=# select * from lt_global_temp_table_ext;
 relid | nspname |     relname     | preserved | code 
-------+---------+-----------------+-----------+------
 43898 | public  | gtt_session     | t         | 
 43904 | public  | gtt_transaction | f         | 
(2 rows)

现在对gtt_session创建分布式列

lightdb@postgres=# select create_distributed_table('gtt_session','id');
 create_distributed_table 
--------------------------
 
(1 row)

lightdb@postgres=#

分布式节点信息如下：

lightdb@postgres=# SELECT * FROM canopy_shards;
             table_name             | shardid |                shard_name                 | canopy_table_type | colocation_id |    nodename     
| nodeport | shard_size 
------------------------------------+---------+-------------------------------------------+-------------------+---------------+-----------------
+----------+------------
 lt_gtt_schema_103385_7.gtt_session |  102463 | lt_gtt_schema_103385_7.gtt_session_102463 | distributed       |            15 | 192.168.237.142 
|     5434 |          0
 lt_gtt_schema_103385_7.gtt_session |  102464 | lt_gtt_schema_103385_7.gtt_session_102464 | distributed       |            15 | 192.168.237.142 
|     5434 |          0
 lt_gtt_schema_103385_7.gtt_session |  102465 | lt_gtt_schema_103385_7.gtt_session_102465 | distributed       |            15 | 192.168.237.142 
|     5434 |          0
 lt_gtt_schema_103385_7.gtt_session |  102466 | lt_gtt_schema_103385_7.gtt_session_102466 | distributed       |            15 | 192.168.237.142 
|     5434 |          0
 lt_gtt_schema_103385_7.gtt_session |  102467 | lt_gtt_schema_103385_7.gtt_session_102467 | distributed       |            15 | 192.168.237.142 
|     5434 |          0
 lt_gtt_schema_103385_7.gtt_session |  102468 | lt_gtt_schema_103385_7.gtt_session_102468 | distributed       |            15 | 192.168.237.142 
|     5434 |          0
 lt_gtt_schema_103385_7.gtt_session |  102469 | lt_gtt_schema_103385_7.gtt_session_102469 | distributed       |            15 | 192.168.237.142 
|     5434 |          0
 lt_gtt_schema_103385_7.gtt_session |  102470 | lt_gtt_schema_103385_7.gtt_session_102470 | distributed       |            15 | 192.168.237.142 
|     5434 |          0
 lt_gtt_schema_103385_7.gtt_session |  102471 | lt_gtt_schema_103385_7.gtt_session_102471 | distributed       |            15 | 192.168.237.142 
|     5434 |          0
 lt_gtt_schema_103385_7.gtt_session |  102472 | lt_gtt_schema_103385_7.gtt_session_102472 | distributed       |            15 | 192.168.237.142 
|     5434 |          0
 lt_gtt_schema_103385_7.gtt_session |  102473 | lt_gtt_schema_103385_7.gtt_session_102473 | distributed       |            15 | 192.168.237.142 
|     5434 |          0
 lt_gtt_schema_103385_7.gtt_session |  102474 | lt_gtt_schema_103385_7.gtt_session_102474 | distributed       |            15 | 192.168.237.142 
|     5434 |          0
 lt_gtt_schema_103385_7.gtt_session |  102475 | lt_gtt_schema_103385_7.gtt_session_102475 | distributed       |            15 | 192.168.237.142 
|     5434 |          0
 lt_gtt_schema_103385_7.gtt_session |  102476 | lt_gtt_schema_103385_7.gtt_session_102476 | distributed       |            15 | 192.168.237.142 
|     5434 |          0
 lt_gtt_schema_103385_7.gtt_session |  102477 | lt_gtt_schema_103385_7.gtt_session_102477 | distributed       |            15 | 192.168.237.142 
|     5434 |          0
 lt_gtt_schema_103385_7.gtt_session |  102478 | lt_gtt_schema_103385_7.gtt_session_102478 | distributed       |            15 | 192.168.237.142 
|     5434 |          0
 lt_gtt_schema_103385_7.gtt_session |  102479 | lt_gtt_schema_103385_7.gtt_session_102479 | distributed       |            15 | 192.168.237.142 
|     5434 |          0
 lt_gtt_schema_103385_7.gtt_session |  102480 | lt_gtt_schema_103385_7.gtt_session_102480 | distributed       |            15 | 192.168.237.142 
|     5434 |          0
 lt_gtt_schema_103385_7.gtt_session |  102481 | lt_gtt_schema_103385_7.gtt_session_102481 | distributed       |            15 | 192.168.237.142 
|     5434 |          0
 lt_gtt_schema_103385_7.gtt_session |  102482 | lt_gtt_schema_103385_7.gtt_session_102482 | distributed       |            15 | 192.168.237.142 
|     5434 |          0
 lt_gtt_schema_103385_7.gtt_session |  102483 | lt_gtt_schema_103385_7.gtt_session_102483 | distributed       |            15 | 192.168.237.142 
|     5434 |          0
 lt_gtt_schema_103385_7.gtt_session |  102484 | lt_gtt_schema_103385_7.gtt_session_102484 | distributed       |            15 | 192.168.237.142 
|     5434 |          0
 lt_gtt_schema_103385_7.gtt_session |  102485 | lt_gtt_schema_103385_7.gtt_session_102485 | distributed       |            15 | 192.168.237.142 
|     5434 |          0
 lt_gtt_schema_103385_7.gtt_session |  102486 | lt_gtt_schema_103385_7.gtt_session_102486 | distributed       |            15 | 192.168.237.142 
|     5434 |          0
 lt_gtt_schema_103385_7.gtt_session |  102487 | lt_gtt_schema_103385_7.gtt_session_102487 | distributed       |            15 | 192.168.237.142 
|     5434 |          0
 lt_gtt_schema_103385_7.gtt_session |  102488 | lt_gtt_schema_103385_7.gtt_session_102488 | distributed       |            15 | 192.168.237.142 
|     5434 |          0
 lt_gtt_schema_103385_7.gtt_session |  102489 | lt_gtt_schema_103385_7.gtt_session_102489 | distributed       |            15 | 192.168.237.142 
|     5434 |          0
 lt_gtt_schema_103385_7.gtt_session |  102490 | lt_gtt_schema_103385_7.gtt_session_102490 | distributed       |            15 | 192.168.237.142 
|     5434 |          0
 lt_gtt_schema_103385_7.gtt_session |  102491 | lt_gtt_schema_103385_7.gtt_session_102491 | distributed       |            15 | 192.168.237.142 
|     5434 |          0
 lt_gtt_schema_103385_7.gtt_session |  102492 | lt_gtt_schema_103385_7.gtt_session_102492 | distributed       |            15 | 192.168.237.142 
|     5434 |          0
 lt_gtt_schema_103385_7.gtt_session |  102493 | lt_gtt_schema_103385_7.gtt_session_102493 | distributed       |            15 | 192.168.237.142 
|     5434 |          0
 lt_gtt_schema_103385_7.gtt_session |  102494 | lt_gtt_schema_103385_7.gtt_session_102494 | distributed       |            15 | 192.168.237.142 
|     5434 |          0
(32 rows)

lightdb@postgres=#

当断开用户连接时，分布式信息也被清掉：

[lightdb@localhost test]$ ltsql 
ltsql (13.8-23.1)
Type "help" for help.

lightdb@postgres=# SELECT * FROM canopy_shards;
 table_name | shardid | shard_name | canopy_table_type | colocation_id | nodename | nodeport | shard_size 
------------+---------+------------+-------------------+---------------+----------+----------+------------
(0 rows)

lightdb@postgres=#

想要了解更多GTT相关内容，请持续关注LightDB。

详细语法可参考LightDB官网查看https://www.hs.net/lightdb
更多请登录LightDB官网进行查看https://www.hs.net/lightdb