DISCARD

Function

Releases internal resources related to database sessions. The DISCARD command is used to reset the status of some or all sessions. Different DISCARD clauses release different types of resources. The DISCARD ALL command releases all temporary resources related to the current session and resets them to the initial state.

Precautions

None

Syntax

DISCARD
        { VOLATILE { TEMPORARY | TEMP } }  |
        { ALL | TEMP | TEMPORARY | PLANS | SEQUENCES } }

Parameter Description

• VOLATILE { TEMPORARY | TEMP }

  Releases resources related to the VOLATILE temporary table in the current session.

  Caution

  After the DISCARD VOLATILE { TEMPORARY | TEMP } statement is executed, all volatile temporary table resources in the current session will be cleared. However, the statement cannot clear a single volatile temporary table resource.

• GLOBAL { TEMPORARY | TEMP } [ TABLE table_name ]

  • Run the DISCARD GLOBAL TEMP command to release resources related to the global temporary table in the current session.

  • DISCARD GLOBAL TEMP TABLE table_name releases resources of a specified global temporary table in the current session.

  Caution

  If a global temporary table occupies resources in a session, you need to run the DISCARD command to clear the resources of all sessions before performing DDL operations.

• TEMP | TEMPORARY

  Releases resources related to all temporary tables in the current session, including volatile and global temporary tables.

• PLANS

  Releases all cached query plans in the current session and forces them to be replanned when related PREPARE statements are used next time.

• SEQUENCES

  Discards all cached sequence-related states, including currval()/lastval() information and any pre-allocated sequence values that have not been returned through nextval().

• ALL

  Releases all temporary resources related to the current session and resets them to their initial state. This has almost the same effect as executing the following statement sequence:

  SET SESSION AUTHORIZATION DEFAULT;
  RESET ALL;
  DEALLOCATE ALL;
  CLOSE ALL;
  UNLISTEN *;
  SELECT pg_advisory_unlock_all();
  DISCARD PLANS; DISCARD SEQUENCES;
  DISCARD TEMP;
  

  Caution

  • After DISCARD ALL is executed successfully, schemas starting with pg_temp and pg_toast_temp are also deleted.

  • DISCARD ALL cannot be executed in a transaction.

Examples

• DISCARD global temporary tables

  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 temporary tables

  After the DISCARD operation is performed, all resources related to volatile temporary tables in the current session are cleared.

  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;
  

• DISCARD TEMP

  After DISCARD TEMP is run, all temporary table resources in the current session are cleared.

  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;
  

last updated: 2025-09-11 15:37 UTC - commit: 0936b7736854a5d1860e596c7749139402140c8a