Non-persistent prepared statements not closed in PostgreSQL

[ThomWright]

I have found these related issues/pull requests

I could not find an existing issue.

Description

The docs on Query::persistent() state:

    /// If `false`, the prepared statement will be closed after execution.

So I was expecting sqlx to close the prepared statement after execution.

However, when I set persistent to false while using PgConnection, I see the following behaviour:

1. The statement gets prepared.
2. The statement never enters the client-side statement cache.
3. Unless I am much mistaken, the prepared statement never gets closed.

This can lead to a memory leak in the database, since the same query can get repeatedly prepared and never closed.

Question

Instead of using incrementing IDs, would it help to have a deterministic query text -> ID mapping, and always cache this ID in the LRU statement cache? I believe I have seen this in other connection pools.

In this case, this would result in an error from PostgreSQL when trying to re-prepare the same query with the same ID/name. This is significantly quicker/easier to detect than a memory leak.

Reproduction steps

for _ in 0..10 {
    let q = sqlx::query("SELECT (1)");
    let q = q.persistent(false);
    q.execute(&mut *conn).await.expect("query should succeed");
}

Produces the following logs in the database:

2025-05-01 15:28:31.704 UTC [932] LOG:  execute sqlx_s_1: SELECT (1)
2025-05-01 15:28:31.706 UTC [932] LOG:  execute sqlx_s_2: SELECT (1)
2025-05-01 15:28:31.708 UTC [932] LOG:  execute sqlx_s_3: SELECT (1)
2025-05-01 15:28:31.710 UTC [932] LOG:  execute sqlx_s_4: SELECT (1)
2025-05-01 15:28:31.711 UTC [932] LOG:  execute sqlx_s_5: SELECT (1)
2025-05-01 15:28:31.713 UTC [932] LOG:  execute sqlx_s_6: SELECT (1)
2025-05-01 15:28:31.714 UTC [932] LOG:  execute sqlx_s_7: SELECT (1)
2025-05-01 15:28:31.717 UTC [932] LOG:  execute sqlx_s_8: SELECT (1)
2025-05-01 15:28:31.719 UTC [932] LOG:  execute sqlx_s_9: SELECT (1)
2025-05-01 15:28:31.721 UTC [932] LOG:  execute sqlx_s_10: SELECT (1)

Note how the sqlx_s_<ID> increments every time. Without setting q.persistent(false), you will only see sqlx_s_1;

I've found it difficult to prove that the statement is not being closed/deallocated from looking at the database logs, but I see that Close::Statement(id) is not called anywhere except PgConnection::get_or_prepare(), when a statement is evicted from the LRU statement cache.

SQLx version

0.8.3

Enabled SQLx features

runtime-tokio-native-tls, postgres, derive

Database server and version

PostgreSQL 13

Operating system

MacOS

Rust version

1.86.0

[abonander]

These should probably just use the unnamed statement, honestly. Parse -> Bind -> Execute -> Close in one shot.