Temporary Table Persists After Connection Closure

[wjkoh]

I've encountered an issue where a temporary table is not deleted when its associated database connection is closed. Furthermore, it appears the closed connection is being reused, allowing subsequent users to see the supposedly deleted temporary table from a new database connection. Here's an example to reproduce the problem.

main.go

package main

import (
	"context"
	"database/sql"
	"log"
	"os"

	_ "github.com/mattn/go-sqlite3"
)

func main() {
	err := createTempTableTwice(context.Background())
	if err != nil {
		log.Fatal(err)
	}
	log.Println("Done.")
}

func createTempTableTwice(ctx context.Context) error {
	os.Remove("./foo.db")
	db, err := sql.Open("sqlite3", "./foo.db")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()
	{
		conn1, err := db.Conn(ctx)
		if err != nil {
			return err
		}
		_, err = conn1.ExecContext(ctx, `CREATE TEMPORARY TABLE temp.documents(document_id TEXT PRIMARY KEY);`)
		if err != nil {
			return err
		}
		err = conn1.Close()
		if err != nil {
			return err
		}
	}
	conn2, err := db.Conn(ctx)
	if err != nil {
		return err
	}
	defer conn2.Close()
	// NOTE: This line causes the following error: `2025/03/24 15:42:40 table documents already exists`
	_, err = conn2.ExecContext(ctx, `CREATE TEMPORARY TABLE temp.documents(document_id TEXT PRIMARY KEY);`)
	return err
}

go.mod:

module example.com/sqlite-conn-temp-table

go 1.24

require github.com/mattn/go-sqlite3 v1.14.24

[rittneje]

This is working as intended. See https://pkg.go.dev/database/sql#Conn.Close

Close returns the connection to the connection pool.

[wjkoh]

The situation is complex. When a connection is returned to a pool, resetSession is called to clear the connection's previous state, ensuring it functions as if it were new. See https://pkg.go.dev/database/sql/driver#SessionResetter. However, this functionality doesn't seem to work for temporary databases with mattn/go-sqlite3, although it does function correctly for transactions and other session-related information.

SessionResetter may be implemented by Conn to allow drivers to reset the session state associated with the connection and to signal a bad connection.

From https://pkg.go.dev/database/sql/driver#SessionResetter

SQLite3 explicitly states that a temporary database is automatically deleted when the connection is closed. For users of database/sql and mattn/go-sqlite3, the only way to achieve this is via conn.Close(). Whether the database utilizes a pool or not is an internal implementation detail, varying based on use case, as users can limit pooling via parameters. Consequently, some users encounter this issue while others do not, leading to inconsistent behavior for conn.Close().

The TEMP database is always automatically deleted when the database connection is closed.

From https://www.sqlite.org/tempfiles.html

Historically, db.Conn() and conn.Close() were absent. These functions were added at the request of a user who needed session-based operations and couldn't perform them without the ability to open and close individual connections. The CL explicitly mentioned that these functions were for handling temporary databases, among other things. See golang/go#18081 and https://go-review.googlesource.com/c/go/+/40694 for more details.

Could @kardianos and @bradfitz please provide guidance on this issue?

[rittneje]

Without a way for this driver to know what you did while using the sql.Conn, I don't think there is anything we can do. Please note the following:

1. Blindly dropping all temporary tables is not a solution, because they may have been added in a ConnectHook, and thus need to persist.
2. Actually closing the connection is not a solution, because clients may have intentionally configured the pool to have a long-lived connection.

[wjkoh]

I believe dropping all temporary tables on connection close is a reasonable action.

SQLite3 explicitly states that temporary tables are dropped when the connection is closed. This isolation (not being shared between connections) and automatic deletion are probably the primary reasons for their existence. The current behavior of go-sqlite3 violates both for no good reason. Therefore, this automatic cleanup should be the default behavior.

While someone might try to persist them using a connection hook or something upon connection closing, this should be considered an edge case and shouldn't take precedence over the common, expected scenario, not the other way around.

[rittneje]

Note that temporary tables are not the only aspect of the connection state that could be changed. For example, one could run PRAGMA busy_timeout=N - shouldn't that also be reverted?

It seems to me the only way to truly reset the connection state is to actually close the connection (by returning ErrBadConn), which would need to be opt in.

[wjkoh]

Regarding temporary tables, I think it would be acceptable if they were specifically dropped only when a user explicitly calls db.Conn() and then conn.Close(). This depends on whether we can detect this sequence, but I don't know much about the implementation details, so it might not be possible. Anyway, I don't care about other corner cases, such as busy timeouts, at all. At a minimum, a temporary table should function as expected when a user explicitly creates and closes a connection. If not, temp tables work just like regular tables and no one would use it for anything.

[rittneje]

The current behavior of go-sqlite3 violates both for no good reason.

As already discussed, the documented behavior of sql.Conn.Close is to return the connection to the pool, not to actually close it. (The name is unfortunate.) Consequently, the current behavior does not violate anything.

This hinges on our ability to detect this specific sequence, which I'm uncertain about due to my limited knowledge of the implementation.

This is not possible. (Actually the connection is not reset when it is returned to the pool, but rather when it is pulled back out, at which point even database/sql doesn't remember what happened.)

[wjkoh]

Let's return to the original question: how can we use a temporary table for anything useful? In the current situation, a temporary table works similarly to regular tables. Actually, it's worse and useless because it may or may not persist depending on the internal behavior of the connection pool. It may unexpectedly take up space and cause data leakage because a user believes that a temporary table will be auto-deleted and won't be shared among connections. I think we should provide users with a useful guide or ban temporary tables from go-sqlite3 if there's no way for users to use them as they're expected to work, as the SQLite3 specification dictates.

[rittneje]

First, what you are encountering is just another example of where database/sql's connection pool concept kind of falls flat, because it doesn't give you the ability to convey your intention when calling Conn.Close.

Also, today it is perfectly legal to do this:

db.Exec("CREATE TEMPORARY TABLE Foo(...)")
defer db.Exec("DROP TEMPORARY TABLE Foo")

db.Exec("INSERT INTO TEMPORARY TABLE Foo ...")

...

But with your proposed change, such code will break. How many clients may be doing such things I could not say, but it's not entirely unreasonable, depending on what the application does.

Lastly, my point about the busy timeout is that there are many things other than temporary tables that are also supposed to be part of the per-connection state, which can also be stumbling blocks. I don't think temporary tables should be treated in a special way here.

[wjkoh]

Note that temporary tables are not the only aspect of the connection state that could be changed. For example, one could run PRAGMA busy_timeout=N - shouldn't that also be reverted?

It seems to me the only way to truly reset the connection state is to actually close the connection (by returning ErrBadConn), which would need to be opt in.

Now that I think about this, even if a busy timeout occurs, isn't it okay to drop all temporary tables? Temporary tables are temporary anyway, and a user should take extra care if they want to use them for an extended period. Currently, a temporary table is completely useless, so at least in this scenario, a temporary table is useful to most users.

[wjkoh]

First, what you are encountering is just another example of where database/sql's connection pool concept kind of falls flat, because it doesn't give you the ability to convey your intention when calling Conn.Close.

Also, today it is perfectly legal to do this:

db.Exec("CREATE TEMPORARY TABLE Foo(...)")
defer db.Exec("DROP TEMPORARY TABLE Foo")

db.Exec("INSERT INTO TEMPORARY TABLE Foo ...")

...
But with your proposed change, such code will break. How many clients may be doing such things I could not say, but it's not entirely unreasonable, depending on what the application does.

Lastly, my point about the busy timeout is that there are many things other than temporary tables that are also supposed to be part of the per-connection state, which can also be stumbling blocks. I don't think temporary tables should be treated in a special way here.

Your code example doesn't seem correct to me because, if I understand correctly, db.Exec selects a connection arbitrarily from the pool, and the Foo table may or may not exist, even with the current implementation, because SQLite exposes the Foo table only to the original connection. The code would behave inconsistently. I wonder if people actually use temporary tables in this way.

[rittneje]

b.Exec selects a connection arbitrarily from the pool, and the Foo table may or may not exist, even with the current implementation

It is possible to force the pool to have a single long-lived connection via SetConnMaxIdleTime, SetMaxIdleConns, and SetMaxOpenConns. Even without that, I believe it would end up picking the same connection each time in this example, unless you have another goroutine doing things in parallel.

I wonder if people actually use temporary tables in this way.

Like I said, I really have no way of knowing. But it certainly wouldn't surprise me.

[wjkoh]

So, you're saying you would prefer doing things the wrong way, at the cost of doing them the right way? This is a good opportunity to do the right thing. We can announce breaking changes, increase the version number, and correct the inconsistent behavior once and for all. Anyway, I'm not a maintainer or a contributor, so it's all up to the team.