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