Managing configuration parameters: VSQL

You can configure Vertica configuration parameters at the following levels, in ascending order precedence:

• Database

• Node

• Session

• User

Most configuration parameters can be set at the database level. You can query system table CONFIGURATION_PARAMETERS to determine at which other levels specific configuration parameters can be set. For example, the following query obtains all partitioning parameters and their allowed levels:

=> SELECT parameter_name, current_value, default_value, allowed_levels FROM configuration_parameters
     WHERE parameter_name ILIKE '%partition%';
           parameter_name           | current_value | default_value | allowed_levels
------------------------------------+---------------+---------------+----------------
 MaxPartitionCount                  | 1024          | 1024          | NODE, DATABASE
 PartitionSortBufferSize            | 67108864      | 67108864      | DATABASE
 DisableAutopartition               | 0             | 0             | SESSION, USER
 PatternMatchingMaxPartitionMatches | 3932160       | 3932160       | NODE, DATABASE
 PatternMatchingMaxPartition        | 20971520      | 20971520      | NODE, DATABASE
 ActivePartitionCount               | 1             | 1             | NODE, DATABASE
(6 rows)

Setting and clearing configuration parameters

You change specific configuration parameters with the appropriate ALTER statements; the same statements also let you reset configuration parameters to their default values:

• ALTER DATABASE
• ALTER NODE
• ALTER SESSION
• ALTER USER

For example, the following ALTER statements change ActivePartitionCount at the database level from 1 to 2 , and DisablePartitionCount at the session level from 0 to 1:

=> ALTER DATABASE DEFAULT SET ActivePartitionCount = 2;
ALTER DATABASE
=> ALTER SESSION SET DisableAutopartition = 1;
ALTER SESSION
=> SELECT parameter_name, current_value, default_value FROM configuration_parameters
      WHERE parameter_name IN ('ActivePartitionCount', 'DisableAutopartition');
    parameter_name    | current_value | default_value
----------------------+---------------+---------------
 ActivePartitionCount | 2             | 1
 DisableAutopartition | 1             | 0
(2 rows)

You can later reset the same configuration parameters to their default values:

=> ALTER DATABASE DEFAULT CLEAR ActivePartitionCount;
ALTER DATABASE
=> ALTER SESSION CLEAR DisableAutopartition;
ALTER DATABASE
=> SELECT parameter_name, current_value, default_value FROM configuration_parameters
      WHERE parameter_name IN ('ActivePartitionCount', 'DisableAutopartition');
    parameter_name    | current_value | default_value
----------------------+---------------+---------------
 DisableAutopartition | 0             | 0
 ActivePartitionCount | 1             | 1
(2 rows)