PsqlODBC / MSAccess : Out of Memory on backend
Posted on 2009-05-04
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 drivers 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;
This query runs for quite a long time (10 minutes) and fails before the end with an ODBC failure.
On the postgresql server (backend), I have an "out of memory" error for this specific query.
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.
The table is quite simple, no trigger or rule, only a primary key and 3 indexes (btree).
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 !