What effect does destroying database related threads have on system
resources?
lazarus / 16-Databases

----------------------------------------------------------------------
vfclists · 2012-02-25 08:11

An application I have written connects to a remote Postgresql 8.3/8.4
database using Zeoslib components, but it has been timing out too
often as the network gets busy in a bursty mode. When the network
times out, restarting the app retrieves the data quickly, it is always
available in good time. The problem with some of the busier networks
is that timeout dialogs happen too often and it annoys the users who
have to restart the app.

My approach then is to run the database for displaying locally (which
is actually the original design) and fetch the data from the remote
database in background thread or in a background helper utility and
insert it into the local database, where the timeouts do not occur.

This creates 2 problems

1. When the connection times out

2. When the query itself times out.

Since handling connection timeouts and query timeouts can be
fraught,what I want to do is to create the whole process of retrieving
the data in a thread within the main program or in a helper utility,
and destroying the thread if it times out, but I can't be sure of what
happens to the resources created by the thread when it is destroyed,
both in the program code itself, the libpq libraries and on the
server.

This may not be the best place for knowing what happens in the library
or on the server, but in the LCL and FPC will destroying the thread
destroy all the resources it creates?

Any ideas on what is likely to happen?

/vfclists

----------------------------------------------------------------------
ludob · 2012-02-26 06:57

When you are having network and query time outs, the solution is not
to move network/database activity to the background but to solve the
bottleneck. In the environment you describe, a client server
application or any network based application should simply be
avoided.  Upgrade the network, the databases server (if that is
causing the time outs), get network QOS in place or change
architecture. It is surprising your users complain about pop-ups and
not about the dead slow response times.

To reply to your resource question: the fact that the database
routines run in a thread has no impact on the cleaning up of resources
provided you use the normal connection closing and tear down. If you
destroy the thread with brute force then of course your resources will
accumulate.
Don't share any database connections between threads and use, if
possible, different connections for simultaneous but unrelated queries
to limit the impact of disconnections.