Thread: INSERT on MSAccess linked ODBC table : out of memory
INSERT on MSAccess linked ODBC table : out of memory
From
postgresqlodbc.domain.thewild_codata@spamgourmet.com
Date:
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
postgresqlodbc.domain.thewild_codata@spamgourmet.com wrote: > 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". Please set the *Level of rollback on errors* Datasource option to *Transaction* if you are setting the option to *Statement*. You also have to relink the link table after changing the Datasource setting. regards, Hiroshi Inoue
Re: INSERT on MSAccess linked ODBC table : out of memory
From
postgresqlodbc.domain.thewild_codata@spamgourmet.com
Date:
Hiroshi Inoue a écrit : > postgresqlodbc.domain.thewild_codata@spamgourmet.com wrote: >> 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". > > Please set the *Level of rollback on errors* Datasource option to > *Transaction* if you are setting the option to *Statement*. > You also have to relink the link table after changing the Datasource > setting. Is this done by setting "protocol" to 7.4-1 ? And a value of 7.4-2 is for "statement" ? I could not find this in the documentation, but this is a guess from an automatically generated file-DSN. Thanks a lot for your answer Hiroshi ! Regards Arnaud
postgresqlodbc.domain.thewild_codata@spamgourmet.com wrote: > Hiroshi Inoue a écrit : >> postgresqlodbc.domain.thewild_codata@spamgourmet.com wrote: >>> 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". >> >> Please set the *Level of rollback on errors* Datasource option to >> *Transaction* if you are setting the option to *Statement*. >> You also have to relink the link table after changing the Datasource >> setting. > > Is this done by setting "protocol" to 7.4-1 ? And a value of 7.4-2 is > for "statement" ? Yes. This problem seems closely related to the topic http://archives.postgresql.org/pgsql-general/2009-04/msg00728.php . regards, Hiroshi Inoue
Re: INSERT on MSAccess linked ODBC table : out of memory
From
postgresqlodbc.domain.thewild_codata@spamgourmet.com
Date:
Hiroshi Inoue a écrit : > postgresqlodbc.domain.thewild_codata@spamgourmet.com wrote: >> Hiroshi Inoue a écrit : >>> postgresqlodbc.domain.thewild_codata@spamgourmet.com wrote: >>>> I am trying to run a query that inserts ~350.000 rows from a local >>>> Access table to a linked PostgreSQL table. >>>> 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". >>> >>> Please set the *Level of rollback on errors* Datasource option to >>> *Transaction* if you are setting the option to *Statement*. >>> You also have to relink the link table after changing the Datasource >>> setting. >> >> Is this done by setting "protocol" to 7.4-1 ? And a value of 7.4-2 is >> for "statement" ? > > This problem seems closely related to the topic > http://archives.postgresql.org/pgsql-general/2009-04/msg00728.php Yes, that's exactly what I am seeing here. Strange that this is not considered as a bug, but my understanding of postgresql's internals is very limited. Anyway, protocol=7.4-1 fixed this ! Thanks for your help ! Regards -- Arnaud