Temporary data and Vertica catalog

Hi,

You can check the LOCK_USAGE system table to find out which statements take a GCL:

For example, creating a GLOBAL TEMP table takes a Global Catalog Lock:

dbadmin=> select object_name, mode, count(*) from lock_usage where session_id = current_session() group by object_name, mode;
 object_name | mode | count
-------------+------+-------
(0 rows)

dbadmin=> create global temp table test (c1 int);
CREATE TABLE

dbadmin=> select object_name, mode, count(*) from lock_usage where session_id = current_session() group by object_name, mode;
   object_name    | mode | count
------------------+------+-------
 Cluster Topology | S    |     1
 Local Catalog    | X    |     1
 Global Catalog   | X    |     1
(3 rows)

Whereas a LOCAL TEMP table does not take a Global Catalog Lock:

dbadmin=> \q

[dbadmin@s18384357 ~]$ vsql
Welcome to vsql, the Vertica Analytic Database interactive terminal.

Type:  \h or \? for help with vsql commands
       \g or terminate with semicolon to execute query
       \q to quit

dbadmin=> select object_name, mode, count(*) from lock_usage where session_id = current_session() group by object_name, mode;
 object_name | mode | count
-------------+------+-------
(0 rows)

dbadmin=> create local temp table test_loc (c1 int);
CREATE TABLE

dbadmin=> select object_name, mode, count(*) from lock_usage where session_id = current_session() group by object_name, mode;
 object_name | mode | count
-------------+------+-------
(0 rows)

We had the regular table version on my last job, and the short answer is that we spent a lot of time chasing GCL timeouts. We would have used either global or local temp, but our ETL tool did not guarantee connection continuity between statements.