Session Management

Vertica now provides powerful methods for database administrators to view and control sessions. The methods vary according to the type of session:

• External (user) sessions are initiated by vsql or programmatic (ODBC or JDBC) connections and have associated client state.
• Internal (system) sessions are initiated by the Vertica database process and have no client state.

You can view a list of currently active sessions (including internal sessions) and can interrupt or close external sessions when necessary.

Viewing Sessions

There are two virtual tables that you can use to view the session status of your database: VT_SESSION and VS_SESSION. Both tables have the same set of columns, as described in the SQL Reference Manual. However:

• VT_SESSION contains information about external sessions and returns one row per session.
• VS_SESSION contains information about both internal and external sessions and returns one row per session per node. In other words, it returns exactly what was reported by each node in the cluster.

These virtual tables have the same query limitations as all other virtual tables:

• Only the FROM Clause is allowed in the SELECT statement.
• Historical queries are not allowed, including AT EPOCH LATEST.

  Vertica can execute a query from a snapshot of the database taken at a specific date and time. The syntax is:

  AT TIME 'timestamp' SELECT...

  The command queries all data in the database up to and including the epoch representing the specified date and time without holding a lock or blocking write operations.

  An epoch represents committed changes to the data stored in a database between two specific points in time. In other words, an epoch contains all COPY, INSERT, UPDATE, and DELETE operations that have been executed and committed since the end of the previous epoch.

  Reviewers:

  Re: "The VT_SESSION table typically reports the state returned by the initiator node of each session, though if a bug or race condition caused a session to be closed on the initiator while it was still open on an executor, it would show in the VT_SESSION with the executor node listed in the node column."

  We don't talk about intiator vs. executor nodes in the doc. Is this information important enough to warrant doing so?

Interrupting and Closing Sessions

• Interrupting a session returns it to an idle state, meaning no statements or transactions are running, no locks are held, and the database is doing no work on behalf of the session.
• Closing a session interrupts the session and disposes of all state related to the session, including client socket connections for external sessions.

These actions are provided in the form of SQL functions:

• INTERRUPT_STATEMENT
• CLOSE_SESSION
• CLOSE_ALL_SESSIONS

These functions are described in detail in the SQL Reference Manual.

Is this true?

SELECT statements that call these functions return when the interrupt or close message has been delivered to all nodes, not after the interrupt or close has completed.