DISCARD
功能描述
释放与数据库会话相关的内部资源。
DISCARD命令用于重设部分或全部的会话的状态,不同的DISCARD子命令释放不同类型的资源;DISCARD ALL释放所有与当前会话相关的临时资源,并重置到其初始状态。
注意事项
- 执行DISCARD VOLATILE { TEMPORARY | TEMP }操作后,当前session内所有volatile临时表资源都会被清理,不支持清理单个volatile临时表资源。
- 如果某张全局临时表在某个会话占用资源,需要DISCARD依次清理所有会话的资源后,才能执行DDL操作。
- 执行DISCARD ALL成功之后,以pg_temp和pg_toast_temp开头的schema也会被删除。
- DISCARD ALL不允许在事务中执行。
语法格式
1 2 3 |
DISCARD {{ GLOBAL { TEMPORARY | TEMP } [ TABLE table_name ] } | { VOLATILE { TEMPORARY | TEMP } } | { ALL | TEMP | TEMPORARY | PLANS | SEQUENCES }} |
参数说明
参数 |
描述 |
---|---|
VOLATILE { TEMPORARY | TEMP } |
表示释放当前会话中VOLATILE临时表相关资源。 |
GLOBAL { TEMPORARY | TEMP } [ TABLE table_name ] |
|
TEMP | TEMPORARY |
释放当前会话中所有临时表的相关资源,包括VOLATILE临时表和GLOBAL临时表。 |
PLANS |
释放当前会话中所有缓存的查询计划,并在下次使用相关prepare语句时强制进行重新计划。 |
SEQUENCES |
丢弃缓存的所有序列相关的状态,包括currval()/lastval()信息和尚未由nextval()返回的任何预分配的序列值。 |
ALL |
释放所有与当前会话相关的临时资源,并将会话重置为其初始状态,这与执行以下语句序列具有几乎相同的效果: SET SESSION AUTHORIZATION DEFAULT; RESET ALL; DEALLOCATE ALL; CLOSE ALL; UNLISTEN *; SELECT pg_advisory_unlock_all(); DISCARD PLANS; DISCARD SEQUENCES; DISCARD TEMP; |
示例
CREATE GLOBAL TEMP TABLE t_global_temp(a int,b int); NOTICE: The 'DISTRIBUTE BY' clause is not specified. Using round-robin as the distribution mode by default. HINT: Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column. CREATE TABLE INSERT INTO t_global_temp VALUES(1,1),(2,2); INSERT 0 2 DROP TABLE t_global_temp; ERROR: can not DROP TABLE when global temp table "t_global_temp" is in use DISCARD GLOBAL TEMP TABLE t_global_temp; DROP TABLE t_global_temp; DROP TABLE
DISCARD VOLATILE清理当前会话中所有volatile临时表相关资源。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
CREATE VOLATILE TEMP TABLE TX1(A INT) DISTRIBUTE BY HASH(A); CREATE TABLE CREATE VOLATILE TEMP TABLE TX2(A INT) DISTRIBUTE BY HASH(A); CREATE TABLE SELECT * FROM TX1; a --- (0 rows) SELECT * FROM TX2; a --- (0 rows) DISCARD VOLATILE TEMP; SELECT * FROM TX1; ERROR: relation "tx1" does not exist LINE 1: SELECT * FROM TX1; ^ SELECT * FROM TX2; ERROR: relation "tx2" does not exist LINE 1: SELECT * FROM TX2; |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
CREATE GLOBAL TEMP TABLE t_global_temp(a int,b int); NOTICE: The 'DISTRIBUTE BY' clause is not specified. Using round-robin as the distribution mode by default. HINT: Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column. CREATE TABLE INSERT INTO t_global_temp VALUES(1,1),(2,2); INSERT 0 2 CREATE VOLATILE TEMP TABLE t_volatile_temp(a int,b int); CREATE TEMP TABLE t_temp(a int,b int); DISCARD TEMP; SELECT * FROM t_global_temp; a | b ---+--- (0 rows) SELECT * FROM t_volatile_temp; ERROR: relation "t_volatile_temp" does not exist LINE 1: select * from t_volatile_temp; SELECT * FROM t_temp; ERROR: relation "t_temp" does not exist LINE 1: select * from t_temp; |