ClassDB Home | Table of Contents

---

Managing User Connections

Author: Steven Rollo

While working with ClassDB, you may encounter situations where students are unable to log in because they have reached their connection limit. This document explains how to find and, if necessary, terminate stray connections.

Listing User Connections

ClassDB provides the ClassDB.listUserConnections(VARCHAR(63)) function to list user connections. This function takes the name of a user as an input parameter, and returns a table with one row for each active connection by that user. The return table is populated with information about each connection from the system view pg_stat_activity. Below is a description of the return table’s schema, followed by a short description of each column:

TABLE
(
   userName VARCHAR(63),
   pid INT,
   applicationName VARCHAR(63),
   clientAddress INET,
   connectionStartTime TIMESTAMPTZ,
   lastQueryStartTime TIMESTAMPTZ
)

Column	pg_stat_activity Equivalent	Description
UserName VARCHAR(63)	usename name	User Name of user that established the connection
PID INT	pid INTEGER	Process ID of the connection Process
ApplicationName VARCHAR(63)	application_name TEXT	A short identifier string sent by the client
ClientAddress INET	client_addr INET	IP address of the client
ConnectionStartTime TIMESTAMPTZ	backend_start TIMESTAMPTZ	Timestamp marking when the connection was established
LastQueryStartTime TIMESTAMPTZ	query_start TIMESTAMPTZ	Timestamp marking when the connection last executed a query

More information about pg_stat_activity can be found in table 28-3 on the Postgres statistics collector documentation page.

Killing User Connections

ClassDB provides two functions that can kill connections to the database. ClassDB.killUserConnections(VARCHAR(63)) terminates all active connections established by a given user. ClassDB.killConnection(INT) terminates a single connection with the given pid. Both functions return BOOLEAN values to signal if the termination was successful. killConnection returns a single value, while killUserConnections returns one for each connection to be terminated. Both functions use pg_terminate_backend(INT4) to kill connections. More information about this function can be found on the Postgres Administration Function Documentation page.

Finding Stray Connections

One common connection problem observed is students being unable to open new connections because they have reached their connection limit. This often occurs when a client fails to close its connection(s) after the student has logged out. Since ClassDB sets a limit of 5 connection per student by default, students can quickly reach their connection limit when their client misbehaves. If you frequently encounter problems with students hitting their connection limit, you may want to consider increasing the connection limit.

Isolating the Problem

A good first step is to confirm the connection problem is due to the connection limit. The item to check is the error message reported by the student. Most clients display an error message explicitly stating that the current user has reached their connection limit. Other error messages likely mean that the student has entered their connection information incorrectly, or some other network issue is interfering with the connection. Additionally, it is useful to examine the client the student is using. Many students are unaware that clients often open multiple connections to the database, even when only one instance of the client is running.

Identifying Possible Stray Connections

Once the connection limit has been identified as the likely culprit, the data returned by ClassDB.listUserConnections(VARCHAR(63)) can be used to find how many connections have been established by a user, when they were established, and what client established them. All three of these metrics are useful for identifying stray connections. The following query displays the number of connections that user currently has open. If this number is equal to the connection limit for Students, then the student has reached their connection limit.

SELECT userName, COUNT(*) connections
FROM ClassDB.listUserConnections('<username>')
GROUP BY userName;

Next, identify which of these connections should be terminated. Running the following query will lists information about all open connections:

SELECT *
FROM ClassDB.listUserConnections('<username>');

When choosing connections to terminate, it is a good rule of thumb to look for connections that have an older lastQueryStartTime timestamp, which shows they have been idle for a longer time. Additionally, you can use this timestamp to separate connections a student may be actively using from ones that are idle.

Killing Stray Connections

If you wish to kill all of a student’s connections, you can simply execute ClassDB.killUserConnections(VARCHAR(63)). However, if you have identified specific connections to kill, you can use ClassDB.listUserConnections(VARCHAR(63)) to get their pids, then use ClassDB.killConnection(INT) to terminate them individually.

---