Skip site navigation (1) Skip section navigation (2)

INSERT on MSAccess linked ODBC table : out of memory

From: postgresqlodbc(dot)domain(dot)thewild_codata(at)spamgourmet(dot)com
To: pgsql-odbc(at)postgresql(dot)org
Subject: INSERT on MSAccess linked ODBC table : out of memory
Date: 2009-05-04 10:30:41
Message-ID: 49FEC3D1.6000700@codata.eu (view raw or flat)
Thread:
Lists: pgsql-odbc
Hi all !

I have a MSAccess 2007 frontend with linked tables on a PostgreSQL 8.3.7 
backend.
The tables are linked via ODBC with the PsqlODBC 8.03.0400 driver.
I use the driver's default options.

I am trying to run a query that inserts ~350.000 rows from a local 
Access table to a linked PostgreSQL table. Both tables have the same 
structure, so the statement is basically something like :
INSERT INTO mylinkedtable (field1, field2, etc...) SELECT field1, 
field2, etc... FROM myaccesstable;
The table on the postgresql server is quite simple, no trigger or rule, 
only a primary key and 3 indexes (btree).

This query runs for quite a long time (10 minutes) and fails before the 
end.
Access only shows me an "ODBC failure" message.
On the postgresql server (backend), I have an "ERROR: out of memory 
DETAIL: Failed on request of size 560".

On the postgresql server (running on Windows 2003), I have 1GB of RAM 
and ~3GB of pagefile.
The process running the query eats up all available RAM before swapping 
and dies when its memory usage totals ~2.5GB.

My server settings are :
shared_buffers = 64MB
sort_buffers = 8MB
work_mem = 8MB
maintenance_work_mem = 128MB

work_mem and maintenance_work_mem were higher (32MB and 512MB 
respectively), so I tried to lower them but to no avail.

What should I change for this query to work ? Why does postgresql use so 
much memory (more than 2GB !!) when all my ressource settings are set so 
low ?
How can I make sure that this won't happen again ?

Thanks a lot for your help !


Regards
--
Arnaud

Responses

pgsql-odbc by date

Next:From: Jose J. Ayala PinedaDate: 2009-05-04 22:23:07
Subject: odbc + refcursor + vb6
Previous:From: Adam RuthDate: 2009-05-04 04:04:42
Subject: Unsupported Type UUID

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group