When to open/close connection to database?

Question

I am using PostgreSQL data server through VB.NET/ODBC on windows what seem's pretty reliable and highly usable solution for storing/manipulating data.
As a beginner here I have some doubts on when to open/close connection to database.

For example, I can open connection when program starts and keep it open during program execution what can be duration of few days, even months. In that case I transfer connection reference for use in all read/write functions in program.

Or, in other case I can open/close connection for every function call or operation on database which not take a long time but brings many open/close situations.

Now I would like to get recommendation from experienced users which way is better.

1) To keep opened connection all the time of program execution.
2) To connect/close every time when data is needed to read/save.

Second but related question is when I opened connection to database like this:

    Dim conn As New OdbcConnection
    conn.ConnectionString = "Dsn=" + dbDsn + _
                            ";database=" + mydatabase + _
                            ";server=" + dbServer + _
                            ";port=" + dbPort + _
                            ";uid=" + dbUser + _
                            ";pwd=" + dbPass
    conn.Open()
    ... etc...

Is here a way to find later in program which exact database is opened using connection referenced by "conn"?

Answer 1

Keep connections for a long time, but keep transactions as short as possible.

How to find the database

SELECT current_database()

See postgresql info functions

Long connections with fault tolerance

Don't open and close connections too often. Hang onto them, but be prepared for them to vanish out from under you, for example:

• if the database or the server it's on is restarted
• if there's a stateful firewall/router between you and the server that might forget about idle connection tracking associations after a while
• if the database administrator intentionally closes your connection for any reason

Your app must gracefully cope with the connection becoming unusable and be prepared to re-try an operation after making a new connection. Make sure to limit the retries so you don't loop forever, hammering the database server with the same failure over and over.

Because a connection can become invalid at any time, you must always check the error codes from absolutely every database operation unless your language can throw exceptions when database operations fail. If you can use exceptions, you only have to wrap the whole sequence of database operations in a single try/catch.

If your app is really simple and doesn't have to be user friendly it's ok to just tell the user you lost the connection and ask them to restart the application. Personally I think you will regret it down the track if you choose this approach, but it does make error handling lots easier.

If your app needs more than a few connections at once, use a connection pool or redesign to queue operations on a single connection.

Short transactions with fault tolerance and retry

While you can hang on to connections for ages if you like, you should keep transactions as short as possible. Long-running transactions cause problems for some database systems, including PostgreSQL - they can reduce efficiency, increase risk of locking problems, etc. Do not keep a transaction open over user "think time" - ie pauses for user interaction - if at all possible. If you must keep transactions open, try to have a timeout in place where you bounce the user back to the start with a session expiry if they decide to go on holiday half-way through filling out that form. If you think I'm joking, you haven't spent enough time maintaining production apps yet.

Your app should be prepared for any database operation to fail and be able to re-try it. Don't throw away your app's knowledge about a task after a database statement runs successfully; retain it until the whole transaction commits successfully. That way you can re-issue the whole transaction if you hit a serialisation failure, your tx is cancelled by the server, etc.

Imagine you wrote some typical linear database access code (pseudocode, I don't speak Visual Basic) like this:

BEGIN;
value = get_value1_from_user();
UPDATE something SET field = :value;
value = get_value2_from_user();
UPDATE somethingelse SET otherfield = :value;
COMMIT;

Now imagine what happens if the second UPDATE fails. The COMMIT won't happen - if you try to run it, it'll do a ROLLBACK instead - and you don't know what the user entered for get_value1_from_user() so you can't retry.

It is usually wiser to write something like:

value1 = get_value1_from_user();
value2 = get_value2_from_user();
committed = false;
retries = 3;
do:
    try:
        BEGIN;
        UPDATE something SET field = :value1;
        UPDATE somethingelse SET otherfield = :value2;
        COMMIT;
        committed = true;
    catch ...:
        retries = retries - 1;
        log "Failed to commit (sometask) because of (error message from database), "+retries+" left"
while not committed and retries > 0;
if not committed:
    print "Tell the user I couldn't save their work"
end if;

Of course, you want to be smart about retrying. After a failure, check to make sure the connection still exists and re-establish it before retrying if it doesn't. Check why your statement failed, too - there's no point re-trying a statement with a syntax error. That is what SQLSTATE is for, to tell the difference between different kinds of errors without examining the message text.

Never examine the error message text as a way to make a decision, because messages get translated and can change from version to version.

There is no such thing as a "verified" connection

Developers who're novices at database development get the idea that they can save lots of hassle by verifying their database connections before they proceed with an operation. They issue a SELECT 1 or something and conclude based on that that the connection is usable and therefore won't fail when the next operation is attempted.

This is bogus. There's an inherent race condition between the "verification" and the next operation. Not only that, but just because your simple verification operation succeeds doesn't mean the next non-trivial statement won't fail. Your app should keep track of all the changes it's made during a transaction until that commits, and be able to retry if the transaction fails.

The alternative is to tell the user "oops, that failed. Re-enter all your changes and try again". Sometimes that's actually the right thing to do, such as when optimistic locking strategies are in use, two in-flight changes conflict, and you can't safely merge them programmatically. It's usually better to just retry behind the scenes, though.

Answer 2

Use a connection pool, for example pgbouncer or pgpool. We did some tests earlier this month:

Using pgpool it takes about 9ms to execute the query, without pgpool it takes about 700ms to execute the same query.

The differences are in the caching of queryplans.