Error when prepared Statement in transaction mode
Opened this issue · 0 comments
Describe the bug
Preparing statement on a client connected to PgBouncer that runs in transaction mode does not have any issues. However, after switching to pgCat with the configs below:
[general]
host = "0.0.0.0"
port = 6432
admin_username = "pgcat"
admin_password = "pgcat"
prepared_statements = true
prepared_statements_cache_size = 50000
[pools.solana]
[pools.solana.users.0]
username = "rpc-server"
password = "pass"
pool_size = 400
min_pool_size = 10
pool_mode = "transaction"
[pools.solana.shards.0]
servers = [
["127.0.0.1", 5432, "primary"]
]
database = "slna"
Caused the application to error out while preparing some of the statements. Here Are application log which shows which statement is failed to be prepared at:
[2024-05-04T03:15:33.019846000Z INFO rpc_server::postgres_client] Statement prepared successfully
[2024-05-04T03:15:33.065166000Z INFO rpc_server::postgres_client] Statement prepared successfully
[2024-05-04T03:15:33.163112000Z INFO rpc_server::postgres_client] Statement prepared successfully
[2024-05-04T03:15:33.208093000Z INFO rpc_server::postgres_client] Statement prepared successfully
[2024-05-04T03:15:33.253698000Z INFO rpc_server::postgres_client] Statement prepared successfully
[2024-05-04T03:15:33.298925000Z INFO rpc_server::postgres_client] Statement prepared successfully
[2024-05-04T03:15:33.345800000Z INFO rpc_server::postgres_client] Statement prepared successfully
[2024-05-04T03:15:33.394634000Z INFO rpc_server::postgres_client] Statement prepared successfully
[2024-05-04T03:15:33.496065000Z INFO rpc_server::postgres_client] Statement prepared successfully
[2024-05-04T03:15:33.541682000Z INFO rpc_server::postgres_client] Statement prepared successfully
Could not connect to the Postgres server. Please review the configuration information. Error details: (Error in preparing the statement for PostgreSQL database, statment: SELECT t.*, COALESCE(
(SELECT block_time FROM block_confirmed_pool WHERE slot = t.slot),
(SELECT block_time FROM block_rooted WHERE slot = t.slot)
) AS block_time
FROM transaction t
WHERE t.slot <= (SELECT slot FROM slot WHERE status = $1)
AND t.signature = $2; err: db error: ERROR: prepared statement "s21" does not exist)
To Reproduce
Not sure how without sharing the source code. But here are some snippets:
pub async fn new(conn_str: String) -> Result<Self> {
let (client, conn) = tokio_postgres::connect(
conn_str.as_str(), NoTls,
).await.map_err(|err| {
anyhow!("Error in connecting to PostgreSQL database, conn_str: {}, error: {}", conn_str, err)
})?;
// Spawn a background task to handle the connection
tokio::spawn(async move {
if let Err(e) = conn.await {
eprintln!("Error in connections: {}", e);
}
});
let get_transaction_stmt = prepare_statement(
"SELECT t.*, COALESCE(
(SELECT block_time FROM block_confirmed_pool WHERE slot = t.slot),
(SELECT block_time FROM block_rooted WHERE slot = t.slot)
) AS block_time
FROM transaction t
WHERE t.slot <= (SELECT slot FROM slot WHERE status = $1)
AND t.signature = $2;",
&client,
).await?;
}
async fn prepare_statement(
stmt: &str,
client: &Client,
) -> Result<Statement> {
let result = client.prepare(stmt).await;
match result {
Err(err) => {
Err(anyhow!("Error in preparing the statement for PostgreSQL database, statment: {} err: {}", stmt, err))
}
Ok(statement) => {
info!("Statement prepared successfully");
Ok(statement)
}
}
}
Expected behavior
I expect all of the prepare statements operations to be successful when done on a client that is connected to pgCat that runs in transaction pool mode since it does that without errors with pgBouncer
Version: pgcat 1.1.2-dev4
OS: Ubuntu 22.04.4 LTS
postgres: psql (PostgreSQL) 16.2 (Ubuntu 16.2-1.pgdg22.04+1)
Pg Client lib: Rust, tokio_postgres 0.7