I'm looking for some concrete information about how connections and connection pooling in Npgsql translates to PostgreSQL's idea of a 'session'. Specifically:

• Is an NpgsqlConnection.Open synonymous with creating a new session?
• Assuming the above is true, is that also ALWAYS true when connection pooling is enabled and a connection is reused?
• Is there any risk of session-based configurations (set_config / current_setting) 'leaking' between pooled connections?

For context, I want to use session-based settings to enforce multi-tenancy RLS policies, but I want to rule out the possibility of connection pooling causing leaks in corner cases.

Thanks.

CodePudding user response：

See https://stackoverflow.com/a/44272654/640325 for some background.

When Npgsql pooling is enabled (default) NpgsqlConnection.Open is definitely not synonymous with creating a new session (or physical connection to PG) - a connection may be taken out of Npgsql's pool and returned instead, which is much faster. When pooling is off, Open is indeed synonymous with creating a new PG session.

However, even pooling is on, Npgsql takes care to reset the connection state when it is returned to the pool (via DISCARD ALL). The effect is that after NpgsqlConnection.Open, your connection state should be the same as it would have been if a totally new PG session were opened (i.e. if pooling were off). This means that no state leaking should occur, even when pooling is enabled.