Redshift Session vs Connection

Hello,

A temporary table that is visible only within the current session. The table is automatically dropped at the end of the session in which it is created. The temporary table can have the same name as a permanent table. The temporary table is created in a separate, session-specific schema. (You can't specify a name for this schema.) This temporary schema becomes the first schema in the search path, so the temporary table takes precedence over the permanent table unless you qualify the table name with the schema name to access the permanent table.

The system table STV_SESSIONS can be used to view information about the active user sessions for Amazon Redshift. For more on STV_SESSIONS, please refer to below link[1]

[1] https://docs.aws.amazon.com/redshift/latest/dg/r_STV_SESSIONS.html

The column name 'process' of STV_SESSIONS table contains the Process ID for the session.

The below command [2] the gives the Process ID for the current session:

[2] select pg_backend_pid();

As long as the Process ID for current session is visible through result of STV_SESSIONS, then the temp table will not be deleted as STV_SESSIONS only lists the active user sessions. So, till the session is active the temp table will not be dropped. If the session is closed then the temp table will be dropped and the Process ID will not be listed in the output of STV_SESSIONS anymore.

I also had the same question, but based on my work experiences, this is how I define:

• Connection: A physical, end-to-end communication between a Redshift node to SQL application on your local computer (i.e. DataGrip, DBeaver, etc.). A connection can have one or multiple sessions. For example, multiple sessions can be made from different console windows. Also we can manually restart a session within the SQL application without losing the connection.
• Session: A logical unit of work between a Redshift node and SQL application. Session is measured in a time unit (i.e. millisecond or second), usually between connection and disconnection. A session can have one or multiple transactions.
• Transaction: A block of SQL run from start to end. This block can contain one or multiple SQL statements for either READ and/or WRITE.

Resource: https://dba.stackexchange.com/questions/13698/what-is-the-difference-between-a-connection-and-a-session

Ofc; I can definitely wrong.