Isn't a connection the same as a session?

Question

I'm trying to figure when temporary tables are created and deleted.

I thought that a temporary table is dropped at the end of a session, but this test makes me unsure of that.

The connection string has Pooling=true;Maximum Pool Size=50 so there is pooling, with a max of 50 connections.

I run the following piece of code concurrently, 30 jobs at a time. I can see that there are 30 distinct process id values used (which makes me assume that a total of 30 connections are opened and reused) no matter how many times i run the code. So the pooling seems to work.

using (var connection = new NpgsqlConnection(connectionString))
{
    var insertIds = GetInsertIds(1000).ToList();

    await connection.OpenAsync();

    var transaction = connection.BeginTransaction();

    await connection.ExecuteAsync(@"
        CREATE TEMP TABLE tmp_data
        (
            insertid INTEGER NOT NULL
        );", transaction: transaction);

    const string copyItemSql = @"
        COPY tmp_data(
            insertid
        ) FROM STDIN (FORMAT BINARY)
    ";

    using (var writer = connection.BeginBinaryImport(copyItemSql))
    {
        foreach (var insertId in insertIds)
        {
            writer.StartRow();
            writer.Write(insertId, NpgsqlDbType.Integer);
        }
    }

    await connection.ExecuteAsync(@"
        INSERT INTO data (insertid)
            SELECT tmpd.insertid
            FROM tmp_data tmpd;
    ", transaction: transaction);

    transaction.Commit();
}

My assumptions where that:

1. I would get an exception when a connection is taken from the pool and reused, because tmp_data already exists. This does not happen, the code runs just fine.
2. I could work around this by doing CREATE TEMP TABLE IF NOT EXISTS tmp_data .... But then I would have the problem that since tmp_data already exists, there would still be rows from the previous insert. And I would be able to work around that by doing ON COMMIT DELETE ROWS.

So am I wrong about this? Isn't a connection in the pool the same thing as a session? Why doesn't the temp table exist the second time a connection is reused?

Answer 1

I looked at Npgsql's implementation, and this is what runs when a connection is returned to the pool:

void GenerateResetMessage()
{
    var sb = new StringBuilder("SET SESSION AUTHORIZATION DEFAULT;RESET ALL;");
    var responseMessages = 2;
    if (DatabaseInfo.SupportsCloseAll)
    {
        sb.Append("CLOSE ALL;");
        responseMessages++;
    }
    if (DatabaseInfo.SupportsUnlisten)
    {
        sb.Append("UNLISTEN *;");
        responseMessages++;
    }
    if (DatabaseInfo.SupportsAdvisoryLocks)
    {
        sb.Append("SELECT pg_advisory_unlock_all();");
        responseMessages += 2;
    }
    if (DatabaseInfo.SupportsDiscardSequences)
    {
        sb.Append("DISCARD SEQUENCES;");
        responseMessages++;
    }
    if (DatabaseInfo.SupportsDiscardTemp)
    {
        sb.Append("DISCARD TEMP");
        responseMessages++;
    }

    responseMessages++;  // One ReadyForQuery at the end

    _resetWithoutDeallocateMessage = PregeneratedMessage.Generate(WriteBuffer, QueryMessage, sb.ToString(), responseMessages);
}

NpgsqlDatabaseInfo.SupportsDiscardTemp is set like this:

public virtual bool SupportsDiscardTemp => Version >= new Version(8, 3, 0);

So you get that behavior whenever you use a PostgreSQL version of at least 8.3.

You can avoid this reset using the connection string parameter No Reset On Close=true, as commented by Shay Rojansky below, but then you must yourself take care that you leave the session “clean”.