Database Session

A session is the context a database keeps for one connected client: who is authenticated, which database and schema are current, the settings in force, open transactions, and any session-scoped objects. Statements sent over the same connection share that context, which is why one statement can read a temporary table another statement created.

In conventional connection-bound sessions, several kinds of state end with the session; exact cleanup is product-specific. Temporary tables are discarded, session-level settings such as time zone or search path revert, uncommitted work is rolled back, and session-scoped variables or prepared statements disappear. A new connection to the same database starts a new session: it can see permanent tables and views, and it cannot see the previous session’s local temporary tables. Shared/global temporary objects have different rules. Reconnecting after a dropped network link therefore loses that state while committed durable data remains. Work not committed when the session ends is normally rolled back.

This is where connection pooling surprises people. An application usually borrows a connection per request and returns it, so two requests may land on different sessions, and a later request may inherit a session another request configured. Any workflow that depends on session state — a temporary table built in one call and read in the next, a setting applied once — needs the same connection held for its whole duration, or needs to stop depending on session state. Returning a connection to a pool is not necessarily ending the database session; cleanup and reset policies determine which state the next borrower inherits.

A session is not the same as a transaction. One session usually runs many transactions in sequence, and a transaction ends at commit or rollback while the session continues. Nor is it the same as a connection in every product: some expose session identifiers, multiplex sessions over connections, or keep a session alive briefly after a client disconnects. Check the documentation for the engine and driver before relying on any of it.

References: PostgreSQL client connection defaults, PostgreSQL SET. See it in use in CTEs and Temporary Tables.