I've run into these quirks before using ODBC.
If a table is huge, the ODBC driver will croak if you try to grab the
entire table all at once. I end up needing to use LIMIT xxxx OFFSET yyyy
to get chunks of tables and then piece the table together on the client.
For a transaction encompassing a lot of inserts/updates, I have to break
it up into smaller batches to avoid the the ODBC driver going haywire.
The connections thing is a mystery. Killing the client apps on my end
always disconnects the postgres connection as soon as the last sql
command finishes. Perhaps the MS Access "engine" continues to maintain
the connections behind the scenes?
Együd Csaba wrote:
> Hi,
> I need some information regarding pgsqlODBC driver on Windows2000. We use an
> application which inserts records pallely into an SQL database via ODBC.
> It estableshes 16 pallel connections and continously inserts data. The
> software works well on a test environment with MS Access and MS ODBC driver.
>
>
> Using Postgres the application stopps inserting rows after 2-3 hours. Is
> there anybody who uses the postgres ODBC driver 24x7? Is that possible that
> the odbc dll contains a memory leak or a bug?
>
> At the time losing the connection the following entry appears in the
> postgres log file:
> -------------------------------------------------------------------
> 2005-01-12 00:01:31 LOG: unexpected EOF on client connection
> 2005-01-12 00:01:31 LOG: could not receive data from client: No
> connection could be made because the target machine actively refused it.
> -------------------------------------------------------------------
>
> Other question: After numerous restart of the above mentioned software there
> are 50-70 connections in postgres (PGAdminIII server status window). How
> could I set a timeout for these connections to be destroyed quicker.