Database connections

[Egbert Bouwman]

My aim is to develop a policy for database connections and commits,
however I do not fully understand these phenomena, and any comment is welcome.
I use postgres with psycopg.

The essence of my program is to do operations on a specific database,
so I need a connection all the time.
The program may run for hours or even days. Why not.

I can start a connection at the beginning of the program, and close it at
the end.
Or I may do it as a class-level instruction in the class that is
responsible for the database operations. So it is executed only once,
at the first import.

But it gives an insecure feeling. Connections may break down, and intruders
may misuse them.
I can test if the connection still exist before I do a real transaction.
How do I test, if not with some dummy execute ?

The alternative is to open and close a connection immediately before
and after each transaction + commit. Is that a too defensive way of life ?

egbert

 

[Alex Martelli]

I use postgres with psycopg.

The essence of my program is to do operations on a specific database,
so I need a connection all the time.
The program may run for hours or even days. Why not.

Sure, quite reasonable.

I can start a connection at the beginning of the program, and close it at
the end.
Or I may do it as a class-level instruction in the class that is
responsible for the database operations. So it is executed only once,
at the first import.

Either makes sense.

But it gives an insecure feeling. Connections may break down, and intruders
may misuse them.

If anybody can hijack an existing connection to a database, inserting
themselves in the middle of an existing conversation, your whole system
is already in such deep dodo that any attempt at amelioratio is doomed.

Sure, connections do break down -- server might be down or whatever.

I can test if the connection still exist before I do a real transaction.
How do I test, if not with some dummy execute ?

Nah! Just be ready to catch the exception you'll get if the server is
down or whatever, and react to such a hopefully-transient failure like
you would to any other (try reopening once, or try periodically forever,
or whatever else is your policy for "server is down or the like").

The alternative is to open and close a connection immediately before
and after each transaction + commit. Is that a too defensive way of life ?

Yes, it is. I don't really see benefits. You still have to be ready to
catch and deal with exceptions for the unlikely but not impossible case
that the server goes down smack in the middle of a transaction -- until
commit succeeds everything is in the air and you must be ready to retry
or whatever. So reuse that readiness to catch the possibility of the
connection having gone down before your transaction started rather than
crashing smack in the middle of it, it's no harder, really.


Alex