Global Temporary Table Functions

pg_get_gtt_relstats(relOid)

Description: Displays basic information about a global temporary table specified by the current session. The query result of the distributed global temporary table on a CN is not meaningful, and the query result on a DN is empty. Therefore, it is not recommended in distributed mode.

Parameter: OID of the global temporary table

Return type: record

Example:

gaussdb=# SELECT * FROM pg_get_gtt_relstats(24576);
 relfilenode | relpages | reltuples | relallvisible | relfrozenxid | relminmxid 
-------------+----------+-----------+---------------+--------------+------------
       24576 |        0 |         0 |             0 |        53969 |          0
(1 row)

pg_get_gtt_statistics(relOid, attnum, ''::text)

Description: Displays statistics about a single column in a global temporary table specified by the current session. Before using this function, perform ANALYZE on the table.

Parameter: OID and the attnum attribute of the global temporary table

Return type: record

Example:

gaussdb=# SELECT * FROM  pg_get_gtt_statistics(24576, 1,''::text);
 starelid | starelkind | staattnum | stainherit | stanullfrac | stawidth | stadistinct | stakind1 | stakind2 | stakind3 | stakind4 | stakind5 | staop1 | staop2 | staop3 | staop4 | staop5 | stanumbers1 | stanumbers2 | stanumbers3 | stanumbers
4 | stanumbers5 |                                                                                                                                                                                           stavalues1                           
                                                                                                                                                                 | stavalues2 | stavalues3 | stavalues4 | stavalues5 | stadndistinct | staextinfo

----------+------------+-----------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+-------------+-------------+-------------+-----------
--+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+------------+------------+------------+---------------+-----------
-
    24576 | c          |         1 | f          |           0 |        4 |          -1 |        2 |        3 |        0 |        0 |        0 |     97 |     97 |      0 |      0 |      0 |             | {.495105}   |             |           
  |             | {1,5,10,15,20,25,30,35,40,45,50,55,60,65,70,75,80,85,90,95,100,105,110,115,120,125,130,135,140,145,150,155,160,165,170,175,180,185,190,195,200,205,210,215,220,225,230,235,240,245,250,255,260,265,270,275,280,285,290,295,300,
305,310,315,320,325,330,335,340,345,350,355,360,365,370,375,380,385,390,395,400,405,410,415,420,425,430,435,440,445,450,455,460,465,470,475,480,485,490,495,500} |            |            |            |            |             1 | 
(1 row)

pg_list_gtt_relfrozenxids()

Description: Displays the frozen XID of each session. If the value of pid is 0, the earliest frozen XID of all sessions is displayed. In distributed mode, gs_global_list_gtt_relfrozenxids() is recommended.

Parameter: none

Return type: record

Example:

gaussdb=# SELECT * FROM pg_list_gtt_relfrozenxids();
       pid       | relfrozenxid 
-----------------+--------------
 139988750038784 |        53969
               0 |        53969
(2 rows)

pg_gtt_attached_pid(relOid)

Description: Displays PIDs and session IDs of all threads that are using the specified global temporary table. If the thread pool is enabled and the session that is using the global temporary table is in the detach state, the PID is 0 and sessionid indicates the session ID. In distributed mode, gs_gtt_attached_pid(relOid) is recommended.

Parameter: OID of the global temporary table

Return type: record

Example:

gaussdb=# SELECT * FROM pg_gtt_attached_pid(24576);
 relid |       pid       | sessionid 
-------+-----------------+-----------
 24576 | 139988750038784 |         1
(1 row)

gs_gtt_attached_pid(relOid)

Description: Displays the IDs of all sessions that are using a specified distributed global temporary table. This is executed on DNs, not supported on CNs.

Parameter: OID of the global temporary table

Return type: record

Example:

gaussdb=# SELECT * FROM gs_gtt_attached_pid(16384);
 relid |       pid       |   cn_name    | cn_timeline | cn_sequence | cn_session_id 
-------+-----------------+--------------+-------------+-------------+---------------
 16384 | 140648560719616 | coordinator1 |           2 |           1 |             1
(1 row)

gs_global_list_gtt_relfrozenxids()

Description: Displays XIDs of frozen transactions on each DN. This is executed on CNs. PID is –1 when the current session is not joined with a global temporary table. This cannot be executed on DNs.

Parameter: none

Return type: record

Example:

gaussdb=# SELECT * FROM gs_global_list_gtt_relfrozenxids();
 node_name |       pid       | relfrozenxid 
-----------+-----------------+--------------
 datanode1 | 140648560719616 |        53959
 datanode1 |               0 |        53959
 datanode2 | 140539996403456 |        53959
 datanode2 |               0 |        53959
(4 rows)

gs_local_gtt_clean_session(clean_type, clean_obj, clean_table)

Description: Clears global temporary tables joined with global sessions on CNs. This is executed on DNs, not supported on CNs.

The following lists related parameters.

Return type: void

Example:

Use gs_local_get_cn_session_key on a CN to obtain the latest cn_session_key.

gaussdb=# SELECT * FROM gs_local_get_cn_session_key();
   cn_name    | cn_timeline | cn_sequence | cn_session_id 
--------------+-------------+-------------+---------------
 coordinator1 |           2 |           1 |             1
(1 row)

Use gs_local_dump_gtt_sess_map to obtain the residual cn_session_key on a DN that needs to be manually cleared.

gaussdb=# SELECT * FROM gs_local_dump_gtt_sess_map();
   cn_name    | cn_timeline | cn_sequence | cn_session_id | uniq_sess_id | refcnt | database_id | current_database_id
--------------+-------------+-------------+---------------+--------------+--------+-------------+---------------------
 coordinator1 |           1 |           1 |             1 |            1 |      0 |       13771 |               13771
(1 row)

Clear the residual cn_session_key that meets the following conditions:

• timeline < CN timeline
• refcnt = 0

gaussdb=# SELECT * FROM gs_local_gtt_clean_session(0, 'coordinator1_2', '');
 gs_local_gtt_clean_session 
----------------------------

(1 row)

gs_global_dist_gtt_clean_remain_sessions()

Description: Clears all residual session information in a global temporary table. This is executed on CNs, not supported on DNs.

Parameter: none

Return type: void

Example:

gaussdb=# SELECT * FROM gs_global_dist_gtt_clean_remain_sessions();
 gs_global_dist_gtt_clean_remain_sessions 
------------------------------------------

(1 row)

gs_local_get_cn_session_key()

Description: Displays the session key of the current session. This is executed on CNs. This cannot be executed on DNs.

Parameter: none

Return type: record

Example:

gaussdb=# SELECT * FROM gs_local_get_cn_session_key();
   cn_name    | cn_timeline | cn_sequence | cn_session_id 
--------------+-------------+-------------+---------------
 coordinator1 |           2 |           1 |             1
(1 row)

gs_local_dump_gtt_sess_map()

Description: Displays the unique session ID cn_session_key of a distributed global temporary table stored on a DN. The ID consists of the following columns: cn_name, cn_timeline, cn_sequence, cn_session_id, uniq_sess_id, refcnt, database_id, and current_database_id. This is executed on DNs, not supported on CNs.

Parameter: none

Return type: record

Example:

gaussdb=# SELECT * FROM gs_local_dump_gtt_sess_map();
   cn_name    | cn_timeline | cn_sequence | cn_session_id | uniq_sess_id | refcnt | database_id | current_database_id
--------------+-------------+-------------+---------------+--------------+--------+-------------+---------------------
 coordinator1 |           1 |           1 |             1 |            1 |      0 |       13771 |               13771
(1 row)

gs_global_gtt_clean_data(schema_name, table_name)

Description: Clears the data files of the specified global temporary table in all sessions. This is for internal use only and cannot be executed by users.

The following lists related parameters.

Return type: void

Example:

gaussdb=# SELECT gs_global_gtt_clean_data('public', 'gtt');
ERROR:  The input parameter is invalid.
CONTEXT:  referenced column: gs_local_gtt_clean_session
SQL statement "EXECUTE DIRECT ON DATANODES 'SELECT pg_catalog.gs_local_gtt_clean_session(2, ''public'', ''gtt'')'"
PL/pgSQL function gs_global_gtt_clean_data(text,text) line 6 at EXECUTE statement
referenced column: gs_global_gtt_clean_data

gs_gtt_local_clean_cn_remain_session_keys()

Description: Clears residual data in global temporary tables on other DNs when a DN is faulty. This is executed on CNs, not supported on DNs.

Return type: void

gaussdb=# select * from gs_gtt_local_clean_cn_remain_session_keys();
 gs_gtt_local_clean_cn_remain_session_keys 
-------------------------------------------

(1 row)