Postgres Connection Error

[dbettin]

Our production service will occasionally (maybe a handful of times a week) receive the following error:

postgres connection error: connection closed

What is the best way to check the connection via the pool? Is there a better way to handle these errors then just doing a check and trying the get another connection from the pool again?

[sfackler]

Have you disabled test_on_checkout? https://docs.rs/r2d2/0.8.8/r2d2/struct.Builder.html#method.test_on_check_out

If not, the pool is already making an empty query before giving you the connection.

[dbettin]

I am not disabling testing the connection. So I assume based on this, the connection becomes invalid between the time I get it from the pool and the time it is used?

[sfackler]

That's what should be happening. You might want to try turning on logging for the postgres and tokio_postgres crates to see if there's anything interesting there.

[dbettin]

I am not seeing any anything interesting in the additional logs. One thing to note, the connection error does occur inside a transaction.

It creates the transaction fine. Then during a query execution in the transaction, we receive the connection error.

[sfackler]

Starting a transaction is a query in Postgres, so it seems like the server's just deciding to disconnect in the middle of your logic for whatever reason.

[dbettin]

Thanks. Do you have any suggestions to help mitigate this issue? Should I check the connection again before I issue another transaction query?

[sfackler]

I think the thing you'll want to do is to figure out why the server is disconnecting you. There may also be logs on the Postgres server side to look at. If you're not seeing this error log message, then the server is presumably deciding to kill the connection in a "normal" fashion: https://github.com/sfackler/rust-postgres/blob/master/postgres/src/config.rs#L330.

The server might be sending a notice message before it kills the connection, but right now that gets ignored. I'll tweak things such that it'll be logged.

[dbettin]

We are seeing that error. The extra logging would be great.