User-defined session parameters

User-defined session parameters allow you to write more generalized parameters than what Vertica provides. You can configure user-defined session parameters in these ways:

• From the client—for example, with ALTER SESSION

• Through the UDx itself

A user-defined session parameter can be passed into any type of UDx supported by Vertica. You can also set parameters for your UDx at the session level. By specifying a user-defined session parameter, you can have the state of a parameter saved continuously. Vertica saves the state of the parameter even when the UDx is invoked multiple times during a single session.

The RowCount example uses a user-defined session parameter. This parameter counts the total number of rows processed by the UDx each time it runs. RowCount then displays the aggregate number of rows processed for all executions. See C++ example: using session parameters and Java example: using session parameters for implementations.

Viewing the user-defined session parameter

Enter the following command to see the value of all session parameters:

=> SHOW SESSION UDPARAMETER all;
schema | library | key | value
--------+---------+-----+-------
(0 rows)

No value has been set, so the table is empty. Now, execute the UDx:

=> SELECT RowCount(5,5);
RowCount
----------
10
(1 row)

Again, enter the command to see the value of the session parameter:

=> SHOW SESSION UDPARAMETER all;
schema |  library  |   key    | value
--------+-----------+----------+-------
public | UDSession | rowcount | 1
(1 row)

The library column shows the name of the library containing the UDx. This is the name set with CREATE LIBRARY. Because the UDx has processed one row, the value of the rowcount session parameter is now 1. Running the UDx two more times should increment the value twice.

=> SELECT RowCount(10,10);
RowCount
----------
20
(1 row)
=> SELECT RowCount(15,15);
RowCount
----------
30
(1 row)

You have now executed the UDx three times, obtaining the sum of 5 + 5, 10 + 10, and 15 + 15. Now, check the value of rowcount.

=> SHOW SESSION UDPARAMETER all;
schema |  library  |   key    | value
--------+-----------+----------+-------
public | UDSession | rowcount | 3
(1 row)

Altering the user-defined session parameter

You can also manually alter the value of rowcount. To do so, enter the following command:

=> ALTER SESSION SET UDPARAMETER FOR UDSession rowcount = 25;
ALTER SESSION

Check the value of RowCount:

=> SHOW SESSION UDPARAMETER all;
schema |  library  |   key    | value
--------+-----------+----------+-------
public | UDSession | rowcount | 25
(1 row)

Clearing the user-defined session parameter

From the client:

To clear the current value of rowcount, enter the following command:

=> ALTER SESSION CLEAR UDPARAMETER FOR UDSession rowcount;
ALTER SESSION

Verify that rowcount has been cleared:

=> SHOW SESSION UDPARAMETER all;
schema | library | key | value
--------+---------+-----+-------
(0 rows)

Through the UDx in C++:

You can set the session parameter to clear through the UDx itself. For example, to clear rowcount when its value reaches 10 or greater, do the following:

1. Remove the following line from the destroy() method in the RowCount class:

   udParams.getUDSessionParamWriter("library").getStringRef("rowCount").copy(i_as_string);
   

2. Replace the removed line from the destroy() method with the following code:

   
   if (rowCount < 10)
   {
   udParams.getUDSessionParamWriter("library").getStringRef("rowCount").copy(i_as_string);
   }
   else
   {
   udParams.getUDSessionParamWriter("library").clearParameter("rowCount");
   }
   

3. To see the UDx clear the session parameter, set rowcount to a value of 9:

   => ALTER SESSION SET UDPARAMETER FOR UDSession rowcount = 9;
   ALTER SESSION
   

4. Check the value of rowcount:

   => SHOW SESSION UDPARAMETER all;
    schema |  library  |   key    | value
   --------+-----------+----------+-------
    public | UDSession | rowcount | 9
    (1 row)
   

5. Invoke RowCount so that its value becomes 10:

   => SELECT RowCount(15,15);
   RowCount
   ----------
         30
    (1 row)
   

6. Check the value of rowcount again. Because the value has reached 10, the threshold specified in the UDx, expect that rowcount is cleared:

   => SHOW SESSION UDPARAMETER all;
    schema | library | key | value
   --------+---------+-----+-------
    (0 rows)
   

   As expected, RowCount is cleared.

Through the UDx in Java:

1. Remove the following lines from the destroy() method in the RowCount class:

   udParams.getUDSessionParamWriter("library").setString("rowCount", Integer.toString(rowCount));
   srvInterface.log("RowNumber processed %d records", count);
   

2. Replace the removed lines from the destroy() method with the following code:

   
   if (rowCount < 10)
   {
   udParams.getUDSessionParamWriter("library").setString("rowCount", Integer.toString(rowCount));
   srvInterface.log("RowNumber processed %d records", count);
   }
   else
   {
   udParams.getUDSessionParamWriter("library").clearParameter("rowCount");
   }
   

3. To see the UDx clear the session parameter, set rowcount to a value of 9:

   => ALTER SESSION SET UDPARAMETER FOR UDSession rowcount = 9;
   ALTER SESSION
   

4. Check the value of rowcount:

   => SHOW SESSION UDPARAMETER all;
    schema |  library  |   key    | value
   --------+-----------+----------+-------
    public | UDSession | rowcount | 9
    (1 row)
   

5. Invoke RowCount so that its value becomes 10:

   => SELECT RowCount(15,15);
   RowCount
   ----------
          30
    (1 row)
   

6. Check the value of rowcount. Since the value has reached 10, the threshold specified in the UDx, expect that rowcount is cleared:

   => SHOW SESSION UDPARAMETER all;
    schema | library | key | value
   --------+---------+-----+-------
    (0 rows)
   

As expected, rowcount is cleared.

Read-only and hidden session parameters

If you don't want a parameter to be set anywhere except in the UDx, you can make it read-only. If, additionally, you don't want a parameter to be visible in the client, you can make it hidden.

To make a parameter read-only, meaning that it cannot be set in the client, but can be viewed, add a single underscore before the parameter's name. For example, to make rowCount read-only, change all instances in the UDx of "rowCount" to "_rowCount".

To make a parameter hidden, meaning that it cannot be viewed in the client nor set, add two underscores before the parameter's name. For example, to make rowCount hidden, change all instances in the UDx of "rowCount" to "__rowCount".

See also