[BUG] Spark connector leaves multiple sessions open

[jeremyprime]

Environment

• Vertica Spark Connector version: 3.0.1

---

Problem Description

When performing a read or write operation using the Spark Connector, it creates one or more sessions in Vertica. These sessions are not cleaned up until the connection is closed and the client application exits.

For example, the pyspark example creates 3 sessions, 1 for write and 2 for read. These remain until the python script completes.

We likely need to close these sessions when an operation completes since we do not appear to reuse a given session id. That way, even if multiple operations are performed in a single script/client, the sessions should be closed without having to close the client application.

To get the session information, or close all open sessions, run the following:

docker exec -it docker_vertica_1 vsql

-- Get all sessions
SELECT session_id, node_name, user_name, client_hostname, login_timestamp, statement_start, current_statement, last_statement, client_type, client_os FROM v_monitor.sessions ORDER BY user_name;

-- Close all sessions
SELECT close_all_sessions();

---

Spark Connector Logs

[jeremyprime]

I have run a number of example applications in both Python and Scala. This was done using sbt run, submitting in Spark, and using Jupyter Notebook (also Spark).

For each operation (read or write) one or more sessions will be created in Vertica. These sessions are then closed when the application itself exits. The only time when sessions persist is when I use an application that is long-running, such as Jupyter Notebook, where the kernel continues to run even after execution of the code has completed. When the Jupyter Notebook kernel is shutdown, the Vertica sessions are closed.

Although, since we close the connection after each operation, we might expect the session to also be closed even before the application/client exits. According to this page, a session may persist for a few different reasons, such as if a transaction is not committed. Will need to investigate if we are not correctly committing transactions or closing connections in some cases.

[jeremyprime]

May be related to #171 (planInputPartitions being called twice).

[jeremyprime]

Note that there was an internal ticket, VER-76615, that tried to address this in the past. The test code on that ticket (simply performed a write in a loop, 100 times) still exhausts the number of available sessions when run against the main branch:

java.sql.SQLNonTransientConnectionException: [Vertica][VJDBC](4060) FATAL: New session rejected due to limit, already 105 sessions active

The SQL queries in SchemaTools.getColumnInfo and TableUtils.tableExists leave open sessions when called. There may be other queries as well, these are just the ones that are seen with the current test code. These sessions keep building up and are not closed until the application exits.

[jeremyprime]

The SQL connection to Vertica is the source of the dangling sessions. The connection is created under VerticaJdbcLayer.VerticaJdbcLayer(). This JDBC layer is currently created once for reads, VerticaPipeFctory.getReadPipe(), and writes, VerticaPipeFctory.getWritePipe(). However, there are multiple places where the read and write pipes are created, and thus multiple places where connections are created.

The creation of the connection and/or the creation of the JDBC layer needs to be a singleton. However, this also complicates closing of the connection as other parts of the code will try to reuse the closed connection.

Perhaps connection pooling would help to better manage minimal connections across reads and writes, and all of the underlying queries they perform. It would be ideal to create and reuse a single connection, provided that it does not hang around when the operations are complete (such as when performing long-lived operations in Jupyter Notebook).

[jeremyprime]

Recreating the JDBC layer if the child connection is closed seems to address the issue of multiple and dangling sessions. Although there is still some connection closing and reopening performed by the connector, which is not ideal.

#324 has been created to address this issue.