Reset Your Session: Quick Tip

Jim Knicely authored this tip.

The SHOW command “shows” run-time parameters for the current session. dbadmin=> SHOW all; name | setting -----------------------------+--------------------------------------------------- locale | en_US@collation=binary (LEN_KBINARY) autocommit | off standard_conforming_strings | on escape_string_warning | on multipleactiveresultsets | off datestyle | ISO, MDY intervalstyle | plain timezone | US/Eastern search_path | "$user", public, v_catalog, v_monitor, v_internal transaction_isolation | READ COMMITTED transaction_read_only | false resource_pool | general memorycap | UNLIMITED tempspacecap | UNLIMITED runtimecap | UNLIMITED idlesessiontimeout | UNLIMITED graceperiod | UNLIMITED enabled roles | dbduser*, dbadmin*, pseudosuperuser* available roles | dbduser*, dbadmin*, pseudosuperuser* (19 rows) You can modify one or more of these parameters with the SET command. dbadmin=> SET LOCALE TO en_GB; INFO 2567: Canonical locale: 'en_GB' Standard collation: 'LEN' English (United Kingdom) SET dbadmin=> SET RUNTIMECAP = '1M'; SET dbadmin=> SHOW locale; name | setting --------+------------- locale | en_GB (LEN) (1 row) dbadmin=> SHOW runtimecap; name | setting ------------+--------- runtimecap | 00:01 (1 row) To quickly reset all of the run-time parameters back to their default values, execute the RESET_SESSION function! dbadmin=> SELECT reset_session(); reset_session ---------------------- Reset session: done. (1 row) dbadmin=> SHOW locale; name | setting --------+-------------------------------------- locale | en_US@collation=binary (LEN_KBINARY) (1 row) dbadmin=> SHOW runtimecap; name | setting ------------+----------- runtimecap | UNLIMITED (1 row) Helpful Links:

https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/SQLReferenceManual/Statements/SHOW.htm

https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/SQLReferenceManual/Statements/SET/SETSESSIONRUNTIMECAP.htm

https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/SQLReferenceManual/Functions/VerticaFunctions/RESET_SESSION.htm

Have fun!